Re: Call Oracle DECODE in BizTalk 2006 XML message

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

On Feb 1, 8:26 am, Dipti <D...@xxxxxxxxxxxxxxxxxxxxxxxxx> 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...@xxxxxxxxxxx" 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- Hide quoted text -

- 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

.

Relevant Pages

  • Re: ODBC cannot access Oracle tables under different schemas in one session
    ... >Access / Oracle problem, ... >referring to the proper server / SID of every branch. ... >table, when i try to query the second table under another schema, ODBC ... >shift to another schema and query their tables, ...
    (comp.databases.oracle.misc)
  • Re: UTL_FILE Permissions
    ... The schema APP has already been granted READ and WRITE to the APP_DIR, ... I used the usermod command so that oracle user has a secondary group ... it should be possible for your upload ...
    (comp.databases.oracle.server)
  • Re: UTL_FILE Permissions
    ... The schema APP has already been granted READ and WRITE to the APP_DIR, ... I used the usermod command so that oracle user has a secondary group ...
    (comp.databases.oracle.server)
  • Re: ODBC cannot access Oracle tables under different schemas in one session
    ... >>Access / Oracle problem, ... >>referring to the proper server / SID of every branch. ... >>table, when i try to query the second table under another schema, ODBC ... >>shift to another schema and query their tables, ...
    (comp.databases.oracle.misc)
  • Re: Synchronization Wizard weirdness
    ... Using the Oracle Enterprise Manager and Oracle Change Manager packages, ... :> I have a Test Schema in the same database as the Main schema and need to ... Wizard between the Main schema and Test schema -- obviously returned the full ...
    (comp.databases.oracle.tools)