Oracle function and Out parameter

[Former Member]

Hi! Excuse me, my english is not good.

There is Oracle database. Package GET_XML_DOC_CLOB contains this function:

FUNCTION GetInvoiceDirectLoad(AINVOICE_ID IN NUMBER,

useMod11 IN NUMBER,

useCarNSI IN NUMBER,

usePrimaryCarNSI IN NUMBER,

notState IN NUMBER,

useCalcGraphPod IN NUMBER,

REQ_ID OUT NUMBER) RETURN CLOB

I need call this function using XI, JDBC adapter.

I try so (this method used for call stored procedurs):

<?xml version="1.0" encoding="UTF-8" ?>

<ns1:GetOrgEtranId_MT xmlns:ns1="http://mondibp.com/test_xi/namespace001">

<Statement>

<storedProcedureName Action="EXECUTE">

<table>GET_XML_DOC_CLOB.GetInvoiceDirectLoad</table>

<AINVOICE_ID IsInput="TRUE" type="INTEGER">1077273</AINVOICE_ID>

<useMod11 IsInput="TRUE" type="INTEGER">0</useMod11>

<useCarNSI IsInput="TRUE" type="INTEGER">0</useCarNSI>

<usePrimaryCarNSI IsInput="TRUE" type="INTEGER">0</usePrimaryCarNSI>

<notState IsInput="TRUE" type="INTEGER">0</notState>

<useCalcGraphPod IsInput="TRUE" type="INTEGER">0</useCalcGraphPod>

<REQ_ID IsOutput="TRUE" type="INTEGER">0</REQ_ID>

</storedProcedureName>

</Statement>

</ns1:GetOrgEtranId_MT>

But I get error:

<SAP:AdditionalText>

com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc.

'GET_XML_DOC_CLOB.GetInvoiceDirectLoad' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'GETINVOICEDIRECTLOAD' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored</SAP:AdditionalText>

When I try so (this method used for call function):

<?xml version="1.0" encoding="UTF-8" ?>

<ns1:MT_Oracle_Function xmlns:ns1="http://mondibp.com/test_xi/namespace001">

<stmt>

<function action="SQL_QUERY">

<access>select distinct(GET_XML_DOC_CLOB.GetInvoiceDirectLoad(1077273, 0, 0, 0, 0, 0, 0)) as return from SYS_PARAMS</access>

</function>

</stmt>

</ns1:MT_Oracle_Function>

I get error:

<SAP:AdditionalText>com.sap.aii.af.ra.ms.api.DeliveryException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'function' (structure 'stmt'): java.sql.SQLException: ORA-06572: Function GETINVOICEDIRECTLOAD has out arguments</SAP:AdditionalText>

Could you help me, please! How can I get both functions RETURN and OUT parameters?

[Former Member]

Hi,

I am also facing a similar issue. Can you please let me know how you defined the structure to call the oracle function.

Thanks in advance.

Krishnan

[Former Member]

Hi,

I assume that you are trying to fetch data from Oracle. So you will be using sender JDBC adapter, in which case you will specify SQL statement for selecting rows. Since you are using stored procedure, you will specify: execute <StoredProcedureName>. This will return you resultset of rows.

In case you are trying to insert data into Oracle, you will be using receiver JDBC adapter, in which case the structure mentioned by you would be used. In this case pleaseensure the package name and stored proc name is in correct case.

[Former Member]

I try fetch data from Oracle to R/3.

Way: R/3 -> (RFC adapter, sender) -> XI -> (JDBC adapter, receiver) -> Oracle

It is synchronous call.

Oracle function must return OUT Parameter and CLOB data (XML text).

In Java i call this function so:

cst = connect.prepareCall("{? = call GET_XML_DOC_CLOB.GetInvoiceDirectLoad(?,?,?,?,?,?,?)}");
		cst.registerOutParameter(1, java.sql.Types.VARCHAR);
		cst.setInt(2, 1077273); 
		cst.setInt(3, 0);
		cst.setInt(4, 0);
		cst.setInt(5, 0);
		cst.setInt(6, 0);
		cst.setInt(7, 0);
		cst.registerOutParameter(8, java.sql.Types.NUMERIC);
		cst.execute();
		int req_id  = cst.getInt(8);
		String clob  = cst.getString(1);

I can't understand how i must call this function in XI.

I try call it as stored procedure, but i get error: 'GETINVOICEDIRECTLOAD' is not a procedure or is undefined.

I try call it as function, but get error: Function GETINVOICEDIRECTLOAD has out arguments.

What message i must send to JDBC receiver?

[Former Member]

I need call this function ... Help, pls!

[Former Member]

I can call this function with PL/SQL:

set serverout on; 
declare myvar1 varchar2(10); 
		myvar2 varchar2(100);
begin
myvar2 := GET_XML_DOC_CLOB.getinvoicedirectload(1077273, 0, 0, 0, 0, 0, myvar1);
dbms_output.put_line(myvar1);
dbms_output.put_line(myvar2);
end;

But again!

I don't know how execute this code with JDBC adapter?!

[Former Member]

Hi refer this Blogs for ur querry

/people/bhavesh.kantilal/blog/2006/07/03/jdbc-receiver-adapter--synchronous-select-150-step-by-step

/people/saravanakumar.kuppusamy2/blog/2005/01/19/rdbms-system-integration-using-xi-30-jdbc-senderreceiver-adapter

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

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

<b>also refer these, as i replied earlier</b>

/people/yining.mao/blog/2006/09/13/tips-and-tutorial-for-sender-jdbc-adapter

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

Also, you can check Sriram's blog for executing Stored Procedures,

/people/sriram.vasudevan3/blog/2005/02/14/calling-stored-procs-in-maxdb-using-sap-xi

/people/jegathees.waran/blog/2007/03/02/oracle-table-functions-and-jdbc-sender-adapter

This blog might be helpfull on stored procedures for JDBC

JDBC Stored Procedures

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

Please go through these threads and see if it helps...

http://en.wikipedia.org/wiki/Stored_procedure

Thanks

pls reward if useful