PI 7.0 SP 11 support oracle cursor ?

[Former Member]

Hi

Does PI 7.0 SP 11 support oracle cursor ? Because i need to send multiple rows data to oracle store procedure.

Where do i have to search about the information how to call oracle store procedure with ref cursor input

Regards

Fernand

[justin_santhanam · Accepted solution]

Fernand,

PI 7.0 Supports oracle cursor from SP 7 onwards. Please find the below URL for JDBC document formats.

http://help.sap.com/saphelp_nw70/helpdata/en/2e/96fd3f2d14e869e10000000a155106/content.htm

Please see some weblog for Calling stored procedures,

/people/siva.maranani/blog/2005/05/21/jdbc-stored-procedures

Best regards,

raj.

[Former Member · Accepted solution]

Thank you Raj,

I manage to calling store procedure with normal input (single), but then still got problem to supply data to store proc with input CURSOR.

Base on link that you send me, i get this line out

"CURSOR (output; only in conjunction with the Oracle JDBC driver)." is it meaning that JDBC adapter does not support CURSOR for input ? even SP 11 ?

This is my sample oracle store procedure :

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;

Regards

Fernand

[justin_santhanam · Accepted solution]

Fernand,

I'm really sorry. I haven't worked on Stored procedures.I kindly request you to keep the thread open, our friends will response to ur query.

Best regards,

raj.

[bhavesh_kantilal · Accepted solution]

Is this a Stored Procedure in a Sender or a Receiver JDBC adapter?

In case of Sender JDBC adapter , the how to is described in the note, 941317. You would need to use Oracle Table Functions.

Receiver JDBC adapter, have not seen a successful implementation of a SP call to a Oracle DB so far. Thought it is possible, not sure how

Regards

Bhavesh

[Former Member · Accepted solution]

Hi Bhavesh,

This is for Receiver adapter, so that's mean at them moment it is impossible to implement ?

Regards

Fernand

[bhavesh_kantilal · Accepted solution]

Fernand,

Online Help states it is possible.

But, I have seen many people struggle to get it done It should be possible, but I dunno how as well

If you are working at a Client, and the client can afford consulting charges , an OSS with SAP should give an answer.

regards

Bhavesh