JDBC Receiver Adapter - Call Stored Procedure

[Former Member]

Hi,

I am using a JDBC receiver adapter to call a stored procedure in oracle.

My target structure looks like:-

<STATEMENT>

<storedProcedureName action="EXECUTE">

<table>realStoredProcedureName</table>

<serialDate hasQuot="NO" isInputType="true" type="date">TO_DATE('07-15-2008 08:00:11','MM-DD-YYYY HH24:MI:SS')</serialDate>

<serialNo isInputType="true" type="NUMERIC">123456</serialNo>

</storedProcedureName>

</STATEMENT>

The storedprocedure takes two input values and has one output value.

The first parameter of the stored procedure is serialDate and the second input parameter is serialNo.

I am getting an exception:-

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'storedProcedureName' (structure 'STATEMENT'): java.lang.NumberFormatException: For input string: "TO_DATE('07"</SAP:AdditionalText>

Any ideas how to fix this issue.

Thanks.

Krishnan

[Former Member · Accepted solution]

the date you want to insert it in database is not in the destination database format.

Thanks

Farooq

[Former Member · Accepted solution]

>>TO_DATE('07-15-2008 08:00:11','MM-DD-YYYY HH24:MI:SS')

This Blog will help you yo eradicate this problem.

thanks

farooq.

Edited by: Farooq Farooqui on Jul 15, 2008 11:39 PM

[Former Member · Accepted solution]

Hi Guys,

Now that we have changed the input parameter of date to string so that the stored procedure accepts this as a string and internally will change it to date.

Now my structure looks like:-

<STATEMENT>

<storedProcedureName action="EXECUTE">

<table>realStoredProcedureName</table>

<serialDate isInputType="true" type="VARCHAR">'07-15-2008 08:00:11'</serialDate>

<serialNo isInputType="true" type="NUMERIC">123456</serialNo>

</storedProcedureName>

</STATEMENT>

I get the following error:-

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'storedProcedureName' (structure 'STATEMENT'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'STOREDPROCEDURENAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored</SAP:AdditionalText>

Any suggestions.

Thanks.

Krishnan

[Former Member · Accepted solution]

Hi,

Create your structure like as mentioned below and it will work for sure.

<Statement>
<storedProcedureName action="EXECUTE">
<serialDate isInput="true" type="VARCHAR"></serialDate>
<serialNo isInput="true" type="VARCHAR"></serialNo>
</storedProcedureName >
</Statement

where storedProcedureName tag should be actual procedure name you are trying to invoke. Please check carefully, field attribute should be be isInput and not isInputType.

Thanks

Amit

reward point if suggestion helps

[Former Member · Accepted solution]

Hi Amit,

I have already done a similar structure as suggested by you. This is how my target structure looks like:-

<STATEMENT>

<storedProcedureName action="EXECUTE" TABLE="SP_SERIAL">

<serialDate isInput="true" type="VARCHAR">01-01-2008 03:43:10</serialDate>

<serialNo isInput="true" type="NUMERIC">984022</record_count>

<return_value isOutput="true" type="NUMERIC"></ret_value>

</storedProcedureName>

</STATEMENT>

But I still get the same error:-

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'storedProcedureName' (structure 'STATEMENT'): java.sql.SQLException: ORA-06550: line 1, column 7: PLS-00201: identifier 'STOREDPROCEDURENAME' must be declared ORA-06550: line 1, column 7: PL/SQL: Statement ignored</SAP:AdditionalText>

Regards.

Krishnan

[Former Member · Accepted solution]

Hi Krishnan,

Did it worked? Don't give Table attribute. Let me know if still it dioesn't worked.

thanks

amit

[Former Member · Accepted solution]

I'm having the exact same problems as Krishnan. Did anyone find the right answer?

[Former Member]

There doesn't seem to be an answer to this problem Krishnan mentioned even though the thread is set to answered.

For futher reference..there is no need for the TO_DATE function. Change the type attribute to "TIMESTAMP" and the Stored Procedure will insert the date and time.

[Former Member]

Hi Damien,

I have set the attribute type to "TIMESTAMP" but still its throwing the error "java.lang.IllegalArgumentException: Timestamp format must be yyyy-mm-dd hh:mm:ss.fffffffff". Then I changed the date format to the specified format now its throwing the error "Error when executing statement for table/stored proc. 'WD_WRAP_PROCESS_APPMT_REQ_PR' (structure 'STATEMENT'): java.lang.NullPointerException".

can u please tell me which date format you have used and do i need to configure the date format in communication channel?

Thanks in Advance,

B Vinoth.

[Former Member]

changing the data type to TIMESTAMP did the trick . dont specify any thing in adapter dateformat configuration. use PI standard date format (i.e. 2009-06-30 10:20:30.0) in the payload.

[GabrielSagaya]

Avoid using Date as an output parameter in a stored procedure

http://kbalertz.com/195471/Stored-Output-Param-Causes-Exception.aspx

[Former Member]

Hi,

Date is not an output parameter. The output parameter is just a number.

Date is an input paramter. Please check my structure. I have define isInput = "true".

Regards.

Krishnan

[Former Member]

The problem is the quote around the string function. Check this blog to overcome your problem

BR

[Former Member]

Hi Farooq and Sameer,

I have already eradicated the quotes by referring to the blog.

If you see my target output structure which I displaed above, TO_DATE does not have quotes. I have already implemented it. If you see one of the attribute within the structure, it mentions hasQuot = "NO". This will eradicate the quotes.

Thanks.

Krishnan

[Former Member]

Hi Krishnan,

<STATEMENT>

<storedProcedureName action="EXECUTE">

<table>realStoredProcedureName</table>

<serialDate hasQuot="NO" isInputType="true" type="date">TO_DATE('07-15-2008 08:00:11','MM-DD-YYYY HH24:MI:SS')</serialDate>

<serialNo isInputType="true" type="NUMERIC">123456</serialNo>

</storedProcedureName>

</STATEMENT>

The storedprocedure takes two input values and has one output value.

The first parameter of the stored procedure is serialDate and the second input parameter is serialNo.

I am getting an exception:-

<SAP:AdditionalText>com.sap.engine.interfaces.messaging.api.exception.MessagingException: Error processing request in sax parser: Error when executing statement for table/stored proc. 'storedProcedureName' (structure 'STATEMENT'): java.lang.NumberFormatException: For input string: "TO_DATE('07"</SAP:AdditionalText>

In the XML structure you cannot use TO_DATE function . Please pass the DATE in the correct format which the Stored procedure can accept . Get the out put what you expected from TO_DATE function and use that value in the structure.

Regards,

Kishore

[Former Member]

Hi Nanda,

I have used TO_DATE within the XML structure and used the attribute hasQuot = NO and it worked for INSERT.

Now that I am using the same login to do it for executing the stored procedure.

Thanks.

Krishnan

[Former Member]

Hi Krishnan ,

Are you able to run the storedprocedure directly thru SQL ? Please check wether you can run the above stored procedure succcessfully on DB with the arguments in the XML structure u mentioned above.

Regards

Kishore

[Former Member]

Yes, we are able to run the stored procedure through SQL-Plus but not through PI.

Regards.

Krishnan

[Former Member]

Hi Krishnan,

Check the SAP Note - 831162 Q 3

<DateField hasQuot="No">TO_DATE(&apos;2004-07-20 08:00:00&apos;, &apos;yyyy-mm-dd hh:mi:ss&apos;)</DateField>

Thanks,

Beena

[Former Member]

Hi Beena,

I have already done this. Refer to my structure above. I have used the attribute hasQuot = "No".

The receiver adapter also removes this quotes which sending it to the target oracle system.

Any other ideas.

Thanks.

Krishnan