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_S
OR_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

[ Post a follow-up to this message ]

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
>
>

[ Post a follow-up to this message ]

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

[ Post a follow-up to this message ]