JDBC Adapter call Oracle Procedure with REF CURSOR Type Input

[Former Member]

Hi All,

I have scenario to calling oracle storeprocedure with ref cursor input type. This is the sample oracle procedure code :

CREATE OR REPLACE PACKAGE DB2_PAYSLIP IS

CURSOR c_details IS

SELECT '' in_nogaji

, '' in_jenis

, '' in_no

, '' in_deskripsi

, '' in_jumlah

FROM DUAL;

TYPE ct_details IS REF CURSOR RETURN c_details%ROWTYPE;

END;

CREATE OR REPLACE PROCEDURE proc_ins_payslip(in_cur IN OUT db2_payslip.ct_details) IS

v_in db2_payslip.c_details%ROWTYPE;

BEGIN

LOOP

FETCH in_cur INTO v_in;

INSERT INTO PAYSLIP_DETAIL VALUES(v_in.in_nogaji,v_in.in_jenis,v_in.in_no,v_in.in_deskripsi,v_in.in_jumlah);

EXIT WHEN in_cur%NOTFOUND;

END LOOP;

CLOSE in_cur;

COMMIT;

END;

How to design my data type in order to support calling this oracle store procedure.

Please help me.

Regards

Fernand

[justin_santhanam · Accepted solution]

Fernand,

You might have seen the link before http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

In that the u have to design as described in the statement6

Best regards,

raj.

[Former Member]

Fernand,

I am not a XI guy, i will try to answer to the best way i can.

What I understand from the PL/SQL procedure, the cursor "c_details " definition is global for that package, means the cursor is fired when the package is called, which is when the procedure is called. This cursor gets all the results, i mean records/rows, and these results are subsequently used for inserting into the table "PAYSLIP_DETAIL".

Before i answer your question, let me explain you little more on this procedure.

First of all this procedure don't need anything as IN parameter because, all it needed is fetched from triggering cursor.

Secondly, the procedure is not sending anything out as an OUT parameter.

>>>So coming to your question "How to design my data type in order to support calling this oracle store procedure" ?

In pure Java i would call this procedure as shown below.

CallableStatement preParedStatement =

conn.prepareCall("{DB2_PAYSLIP.proc_ins_payslip()}");

In simple words, when you call this procedure you don't have send (IN) or catch(OUT) parameters. Just make a blank call.

Sorry, i don't know how the similar call is made in XI.

Ramesh