Originally posted by: VincentHodicq

Hi,

I need to invoke a PL/SQL Procedure on an Oracle Database. This procedure has only one argument but it is a type manually created that contains an array of some other objects.

Here is the Type declaration on Oracle

 type customObject is record (

       text1        VARCHAR2 (20),
       text2     VARCHAR2 (11),
       size               NUMBER (12,3)
  );
  type customObjectList is table of customObject;
 

The procedure signature is 

procedure myProcedure(v_List IN customObjectList);

In my map, I created the rule (per WTX documentation found here http://www.ibm.com/support/knowledgecenter/SSVSD8_8.4.1/com.ibm.websphere.dtx.did.doc/references/r_dbidref_Stored_Procedures_with_Object_Type_Parameters.htm)

=VALID(
    DBLOOKUP(
        "CALL ?=  myProcedure([[ABC|DEF|123][GHI|JKL|456]])",
        "-T+ -DBTYPE ORACLE -CONNECT MYDB -USER MYUSER -PASSWORD MYPWD"),
    FAIL(LASTERRORCODE(  )+LASTERRORMSG(  )))

Map is failing with error -1030Failed to get data from the database

In the dbl, I get

Data being retrieved for DBLOOKUP function.
<624-9580-03/16/16 14:30:11>:    Database adapter: Oracle11g Version 8.4.1.3(14)
<624-9580-03/16/16 14:30:11>:    Starting a database unload...
<624-9580-03/16/16 14:30:11>:    Query      : CALL ?=  myProcedure([[ABC|DEF|123][GHI|JKL|456]])
<624-9580-03/16/16 14:30:11>:    DBLOOKUP Function, Transaction scope: Map
<624-9580-03/16/16 14:30:11>:    TRACE command specified, append mode, file: C:\workspace\WTX\MyDbl.dbl
<624-9580-03/16/16 14:30:11>:    Success returned by OCIDescribeAny, errcode=0

The function completed successfully
<624-9580-03/16/16 14:30:11>:    Returned status: (-1030) Failed_to_get_data_from_the_database
<624-9580-03/16/16 14:30:11>:    Cleaning up and rolling back the transaction...
<624-9580-03/16/16 14:30:11>:    Transaction rollback was successful.
<624-9580-03/16/16 14:30:11>:    Returned status: (0) Success

Do anyone has tried to invoke PL/SQL procedure with custom object types or array? What did I miss?

Thanks in advance for the help

Originally posted by: JayCh

HI,

try using this CALL myProcedure("+TEXT([[ABC|DEF|123][GHI|JKL|456]])+")"

Originally posted by: VincentHodicq

Hi Jay,

Thanks for your reply.

I put CALL myProcedure("+TEXT("[[ABC|DEF|123][GHI|JKL|456]]")+")" and I got the same result as before: Map is failing with error -1030Failed to get data from the database .

I added " in TEXT function from your reply otherwise rule was not valid.