Receiver JDBC adapter issue( Oracle Database)

[Former Member]

Hi All,

I am trying to call a Oracle function:

PACKAGE            USER_ACCTS AS

  FUNCTION ADD_USER (p_employee_number       NUMBER,

                 p_user_id                   VARCHAR2,

                 P_INITIAL_PASSWORD          VARCHAR2,

                 P_DISCONTINUED_FLAG         VARCHAR2,

                 p_last_name                 VARCHAR2,

                 p_first_name                VARCHAR2,

  RETURN NUMBER;

I am getting below error:

{

    "error": "Failed to deliver synchronous message: senderChannel '77be1349ee3a3f6f9638f3bcd9473beb': Catching exception calling messaging system: XIAdapterFramework:GENERAL:com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'USER_ACCTS.ADD_USER' (structure 'Statement'): java.sql.SQLException: ORA-06550: line 1, column 7:\nPLS-00201: identifier 'USER_ACCTS.ADD_USER' must be declared\nORA-06550: line 1, column 7:\nPL/SQL: Statement ignored\n (Software version: 1.0.10)"

}

This is my SAP PI request on JDBC side:

<ns0:MT_UserDBRequest>

<Statement>

<ADD_USER action="EXECUTE">

<table>AGN_IAM_USER_ACCTS.ADD_USER</table>

<p_employee_number isOutput="true" isInput="false" type="VARCHAR">1</p_employee_number>

<p_user_id isOutput="true" isInput="true" type="VARCHAR">Ravijeet123</p_user_id>

<p_initial_password isOutput="true" isInput="true" type="VARCHAR">abc321</p_initial_password>

<p_discontinued_flag isOutput="true" isInput="true" type="VARCHAR">active</p_discontinued_flag>

<p_last_name isOutput="true" isInput="true" type="VARCHAR">DAS</p_last_name>

<p_first_name isOutput="true" isInput="true" type="VARCHAR">RAVIJEET</p_first_name>

<p_response_code isOutput="true" isInput="true" type="VARCHAR">x</p_response_code>

<p_response_msg isOutput="true" isInput="true" type="VARCHAR">x</p_response_msg>

</ADD_USER></Statement>

</ns0:MT_UserDBRequest>

Regards

RJ

[Former Member · Accepted solution]

Hi,

Go through the below link for sol.

https://scn.sap.com/thread/957908

Regards

Venkat

[Former Member · Accepted solution]

Hi All,

Changed functions to stored procedure and it worked.

Regards

Ravijeet

[former_member184720]

As per the below thread - I see that you can not use execute for calling oracle functions. Insetad you should construct the strcture simillar to select stament.

https://scn.sap.com/thread/957908

Also refer to bhaskar's reply in the below thread:

http://scn.sap.com/thread/2112858

Refer the below sap note(at the bottom of the page)

http://www.stechno.net/sap-notes.html?view=sapnote&id=941317

[Former Member]

Hi Haressh,

My function accepts 5 values as input and then creates a record and then responds back with the inserted record values in response.

So how do I implement it as a select query ?

Regards

RJ

[former_member184720]

Hi Das - Please go thorugh the below link

http://help.sap.com/saphelp_nw2004s/helpdata/en/2e/96fd3f2d14e869e10000000a155106/frameset.htm

Refer to structure type 6 and explanation under action= SQL_QUERY | SQL_DML

else other way is to call this function inside a store procedure as suggested in the above provided threads