Oracle Stored Procedure Sender Adapter with Package

[Former Member]

Hi,

We have a written a Stored procedure to read data from Multiple tables in Oracle. We are using PI7.0.

It is giving "INVALID SQL STATEMENT ERROR".

We are using package also to write Stored Procedure. My Questions are below :

1) Can we use packages in SP, when we want to read the database ?

2) Oracle SP are supported in PI 7.0 ?

Thanks.

Code of My SP is below: - It is using package rec_rtgsneft_payments

PROCEDURE axis_dbtopayserver_rtgsneft(rec_rtgsneft_payments OUT tab_rtgsneftpayment)

IS

+ CURSOR transaction_det+

+ IS+

+ SELECT td.corp_code,ci.corp_name,ci.corp_addr1,+

+ ci.corp_location,ci.corp_state,ac.CORP_ACC_NUM,ac.corp_bank_name,ac.corp_bank_city,ac.corp_bank_branch,+

+ ac.corp_IFSC_code,td.vendor_code,vi.primary_name,vi.vendor_addr1,vi.vendor_addr2,vi.vendor_location,vi.vendor_state,+

+ vi.vendor_pincode,vc.BENEFI_ACC_NUM,vc.benefi_bank_name,vc.benefi_bank_city,vc.benefi_bank_branch,vc.benefi_IFSC_code,td.product_code,td.pay_run_date,td.dd_payable_location,+

+ td.cheque_number,td.userid,td.transactional_amount,td.transactional_currency,+

+ td.transactional_MUR,td.transactional_UTR,td.transactional_TRN,td.text_description1,+

+ td.text_description2,td.text_description3,td.text_description4,td.text_description5,+

+ td.text_description6,td.serial_num,td.base_code+

+ FROM axis_transaction_details td,axis_corp_info ci,axis_vendor_info vi,axis_corp_account ac,axis_vendor_account vc+

+ WHERE ci.corp_code = td.corp_code+

+ AND vi.vendor_code =td.vendor_code+

+ AND ci.corp_code = ac.corp_code+

+ AND vi.vendor_code = vc.vendor_code+

+ AND td.product_code IN ('R','N')+

+ AND td.CBS_flag ='SUCCESS';+

+ i NUMBER := 0;+

+ BEGIN+

OPEN transaction_det;

LOOP

FETCH transaction_det INTO axis_dbtopayserver_rtgsneft.rec_rtgsneft_payments(i);

+i := i + 1;+

EXIT WHEN transaction_det%NOTFOUND;

END LOOP;

CLOSE transaction_det;

END axis_dbtopayserver_rtgsneft;

END AXIS_dbtopaymentserver_pkg;

Regards

Kulwinder

[Former Member · Accepted solution]

Hi,

Can anyone provide me with the Sample Oracle Stored procedure for sender aadapter ?

Thanks

Kulwinder

[former_member187339 · Accepted solution]

Hi Kulwinder,

Check this note: Note 941317 - XI / PI JDBC Adapter: Known Problems / Incompatibilities

6. Oracle Database 10g 10.2.x JDBC Driver

The JDK 1.1.x, 1.2 and 1.3 versions (classes111. zip, classes12.zip, classes12.jar) of the driver are not compatible with the SAP XI JDBC Adapter. Use the JDK 1.4 driver (ojdbc14.jar) instead. For details, refer to Oracle MetaLink note # 203849.1.

Invoking Oracle stored procedures from within a JDBC sender channel is only possible for Oracle DBMS versions >= 10.2.x using so-called table functions:

Example:

-

---

pkg1 -

---

CREATE PACKAGE pkg1 AS

TYPE numset_t IS TABLE OF NUMBER;

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED;

END pkg1;

/

CREATE PACKAGE BODY pkg1 AS

-- FUNCTION f1 returns a collection of elements (1,2,3,... x)

FUNCTION f1(x NUMBER) RETURN numset_t PIPELINED IS

BEGIN

FOR i IN 1..x LOOP

PIPE ROW(i);

END LOOP;

RETURN;

END;

END pkg1;

/

-

---

pkg1 -

---

This function has to be invoked from the sender channel configuration (SELECT statement) as follows:

SELECT * FROM TABLE(pkg1.f1(5));

Hope this will be of some help

Regards

Suraj

[former_member187339 · Accepted solution]

Hi Kulwinder,

Please check this code its wroking for me.

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

FUNCTION f_trans

RETURN outrecset PIPELINED IS

out_rec outrec_typ;

CURSOR cur_t is

<select query>

BEGIN

FOR in_rec IN cur_t

LOOP

out_rec.a := in_rec.a;

and so on;

PIPE ROW (out_rec);

END LOOP;

END;

END refcur_pkg;

and call it using select * from TABLE(refcur_pkg.f_trans)

check this url http://download.oracle.com/docs/cd/B28359_01/appdev.111/b28370/tuning.htm#BABHAABE

Regards

Suraj