store procedure in JDBC Receiver adapter

[madhupusala3]

Hi,

I am calling stored procedure using jdbc reeciver adapter . showing below error. In oracle database it is under functions not in the procedure. functions and stored procedure are same? is it should be create under procedure?

error message:

Message processing failed. Cause: com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'GET_ALL_SALESREPINFO' (structure 'statement'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00221: 'GET_ALL_SALESREPINFO' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Sql query:

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

<ns1:TerritoriesAndSalesRepsRequest xmlns:ns1="http://allergan.com/TerritoriesAndSalesRepsServiceList/1.0">

<statement><GET_ALL_SALESREPINFO action="EXECUTE">

<P_REP_LOGIN isInput="1" type="VARCHAR">sdfsfd</P_REP_LOGIN>

</GET_ALL_SALESREPINFO>

</statement></ns1:TerritoriesAndSalesRepsRequest>

oracle function

DECLARE

RETURN_VALUE BEACONDM_ETL.ACCT_TABLE_TYPE;

P_ZIP_CODE VARCHAR2(2000) := '-';

BEGIN

RETURN_VALUE := BEACONDM_ETL.GET_ACCT_INFO(P_ZIP_CODE);

--DBMS_OUTPUT.PUT('RETURN_VALUE: ');

--DBMS_OUTPUT.PUT_LINE(RETURN_VALUE);

DBMS_OUTPUT.PUT('P_ZIP_CODE: ');

DBMS_OUTPUT.PUT_LINE(P_ZIP_CODE);

END;

GO

DECLARE

RETURN_VALUE BEACONDM_ETL.SALESREP_TABLE_TYPE;

P_REP_LOGIN VARCHAR2(2000) := '-';

BEGIN

RETURN_VALUE := BEACONDM_ETL.GET_ALL_SALESREPINFO(P_REP_LOGIN);

--DBMS_OUTPUT.PUT('RETURN_VALUE: ');

--DBMS_OUTPUT.PUT_LINE(RETURN_VALUE);

DBMS_OUTPUT.PUT('P_REP_LOGIN: ');

DBMS_OUTPUT.PUT_LINE(P_REP_LOGIN);

END;

GO

Please send me if you have any blogs for calling stored procedure using jdbc receiver adapter.

[Former Member]

Hi Madhu,

>>>I am calling stored procedure using jdbc reeciver adapter . showing below error. In oracle database it is under functions not in the procedure. functions and stored procedure are same? is it should be create under procedure?

Of course there is difference between Oracle functions & Stored procedures:

As far as i know and have configured and used Stored procedures with the JDBC adapter, it can be used with Oracle stored procedures (leave it to the experts to comment on the Oracle functions part). So if you change it to stored procedure then it should work fine.

You may then define your Message structure as follows:

http://help.sap.com/saphelp_nw73/helpdata/en/44/7b72b2fde93673e10000000a114a6b/content.htm

And you may also like to refer the following blog on stored procedures:

Hope this helps. Regards, Gaurav

[rajasekhar_reddy14]

couple of blogs available in sdn.please do search your self.