ERROR IN ORACLE STORED PROCEDURE in JDBC RECIEVER

[Former Member]

Hi all

i have an FILE2JDBC2FILE sync scenario.have configured this w/o BPM.i need to call an stored procedure which will return me the output in the outpur parameters.input out put parametrs of the stored procedure are.

p_api_version IN NUMBER

,p_init_msg_list IN NUMBER DEFAULT EBS_ECOM_UTILS_PUB.G_TRUE

,p_ebs_place_in_t IN p_ebs_place_in_t(sql TABLE TYPE )

,p_ebs_std_call_o IN ebs_std_call_oty(sql TABLE TYPE )

,x_return_status OUT VARCHAR2

,x_msg_count OUT NUMBER

,x_msg_data OUT ebs_message_tty

,x_row_count OUT NUMBER

,x_ebs_place_out_t OUT ebs_place_out_toty(sql TABLE TYPE );

where

p_ebs_place_in_t is of type p_ebs_place_in_t which has 2 fields MDM_SYSTEM_REF,EMAIL_ADDR and START_DATE

p_ebs_std_call_o is of table type ebs_std_call_oty which has 2 fields WORK_GRP_ID and DEBUG_MODE

In XI i am passing the arguments in the following fashion to ORACLE (JDBC reciever adapter)

<p_api_version isInput="true" type="integer">1</p_api_version>

<p_init_msg_list isInput="true" type="integer">1</p_init_msg_list>

<p_ebs_place_in_t isInput="true" type="CHAR">

<MDM_SYSTEM_REF isInput="true" type="CHAR">MDM-237</MDM_SYSTEM_REF>( this is a field in p_ebs_place_in_t )

<EMAIL_ADDR isInput="true" type="CHAR"></EMAIL_ADDR>>(this is a field in p_ebs_place_in_t )

<START_DATE isInput="true" type="date"/>06-OCT-2009 00:00:00</START_DATE>>(this is a field in p_ebs_place_in_t )

</p_ebs_place_in_t>

<p_ebs_std_call_o isInput="true" type="CHAR">

<WORK_GRP_ID isInput="true" type="integer">95714</WORK_GRP_ID>

<DEBUG_MODE isInput="true" type="integer">5</DEBUG_MODE>

</p_ebs_std_call_o>

<x_return_status isOutput="true" type="CHAR"></x_return_status>

<x_msg_count isOutput="true" type="integer"></x_msg_count>

<x_msg_data isOutput="true" type="CHAR"></x_msg_data>

<x_row_count isOutput="true" type="integer"></x_row_count>

<x_ebs_place_out_t isOutput="true" type="CHAR">

</x_ebs_place_out_t>

i am gettng the below error when i am trying to post th message from RWB

Error when executing statement for table/stored proc. 'APPS.EBS_CUSTOMER_SERVICE_PUB.MAINTAIN_PLACE' (structure 'StatementName1'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00306: wrong number or types of arguments in call to 'MAINTAIN_PLACE' ORA-06550: line 1, column 7: PL/SQL: Statement ignored .

please suggest..

Edited by: Rohan Patil on Jun 13, 2010 12:55 AM

[Former Member]

Hi,

Plz check this.

http://sap-xi.com/sap-xi-scenarios-file-jdbc.html

Thanks and Regards

Hemant

[former_member181985]

Hi,

You need to pass all the mandatory input parameters as expected by stored procedure in sequence.

Regards,

Praveen Gujjeti

[Former Member]

HI Praveen,

thanks for your reply.

my stored procedure has input and output parameters both.

and in XI i have created the data type for both isInput(passedwith all the required values) and isOutput(passed the null values)

is this correct..?or should i take only isInput parameters in the data type?

do i need to take the isOutput also in the datatype?my stored procedure will return the output in the output parametrs.

can i take only 1 or 2 output paraetrs as there are 4 to 5 output parameters for my sotred procedure.?

and how will my response data type to send the output of the stored procedure to the FILE sustem.should it be also in the statement form?

and also would like to know..some of my isInput parameters to the stored procedure has the table type structure as the input parameters.now my question is i should i declare them in my data type?

can you please elaborate.