|
Home > Archive > BizTalk Server > February 2007 > Call Oracle DECODE in BizTalk 2006 XML message
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Call Oracle DECODE in BizTalk 2006 XML message
|
|
| simonvinyl@hotmail.com 2007-01-18, 1:15 pm |
| Hi
I have an Oracle DB with a function that can be used inside an insert
statement
Such that
insert into CDI_SOR_DEFS (EX_SOR_NUM, SOR_REF, SOURCE_CODE,
CATALOGUE_SOURCE)
values(DECODE(customer_card_loc(31,null,
null),null,31 ,null),
'TEST','TEST','TEST')
(customer_card_loc() - is the function)
This works fine in SQLPlus however when i try and call this function
through Biztalk and an XML message
such that
<ns0:Insert
xmlns:ns0="http://schemas.microsoft.com/[OracleDb://LFC/CDI/Tables/CDI_SOR_DEFS]"
xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
<ns0:Rows>
<ns0:InsertRecord>
<ns0:EX_SOR_NUM>DECODE(customer_card_loc(31,null,null ),null,31
,null)</ns0:EX_SOR_NUM>
<ns0:SOR_REF>506003</ns0:SOR_REF>
<ns0:SOURCE_CODE>W</ns0:SOURCE_CODE>
<ns0:CATALOGUE_SOURCE>CATALOGUE</ns0:CATALOGUE_SOURCE>
</ns0:InsertRecord>
</ns0:Rows>
</ns0:Insert>
It fails
Any pointers would be great
Thanks
Simon
| |
|
| Hey,
Were you able to resolve this issue? am facing the same problem, would be
great if you can provide soem pointers.
Thanks and Regards
Dipti
"simonvinyl@hotmail.com" wrote:
> Hi
>
> I have an Oracle DB with a function that can be used inside an insert
> statement
>
> Such that
>
> insert into CDI_SOR_DEFS (EX_SOR_NUM, SOR_REF, SOURCE_CODE,
> CATALOGUE_SOURCE)
>
> values(DECODE(customer_card_loc(31,null,
null),null,31 ,null),
> 'TEST','TEST','TEST')
>
> (customer_card_loc() - is the function)
>
> This works fine in SQLPlus however when i try and call this function
> through Biztalk and an XML message
>
> such that
>
> <ns0:Insert
> xmlns:ns0="http://schemas.microsoft.com/[OracleDb://LFC/CDI/Tables/CDI_SOR_DEFS]"
> xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance">
> <ns0:Rows>
> <ns0:InsertRecord>
> <ns0:EX_SOR_NUM>DECODE(customer_card_loc(31,null,null ),null,31
> ,null)</ns0:EX_SOR_NUM>
> <ns0:SOR_REF>506003</ns0:SOR_REF>
> <ns0:SOURCE_CODE>W</ns0:SOURCE_CODE>
> <ns0:CATALOGUE_SOURCE>CATALOGUE</ns0:CATALOGUE_SOURCE>
> </ns0:InsertRecord>
> </ns0:Rows>
> </ns0:Insert>
>
> It fails
>
> Any pointers would be great
>
> Thanks
>
> Simon
>
>
| |
| simonvinyl@hotmail.com 2007-02-07, 1:15 pm |
| On Feb 1, 8:26 am, Dipti <D...@discussions.microsoft.com> wrote:
> Hey,
>
> Were you able to resolve this issue? am facing the same problem, would be
> great if you can provide soem pointers.
>
> Thanks and Regards
> Dipti
>
>
>
> "simonvi...@hotmail.com" wrote:
>
>
>
>
>
>
>
>
>
>
>
>
>
> - Show quoted text -
I ended up using "Add generated items" against the function / Stored
Proc (customer_card_loc()) on the Oracle DB - this gave me the xsd
schema.
i then (in my Orcastration) use this schema to query the Oracle DB to
get the result of the function - assign the result to a variable (in
the Orc) then pass it and the rest of my Message to a custom adapter
that builds the correct xml for the insert- i then pass this back to
Oracle
Not the most effciant way - but it worked
Simon
|
|
|
|
|