Skip to Content
0
Former Member
Mar 05, 2009 at 05:11 AM

Oracle Stored Procedure Sender Adapter with Package

27 Views

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