PI Publisher report for OTM

Antoine KAIBER

Summary

PI Publisher report for OTM

Content

Hello,

I am trying to generate a report in OTM (Oracle Transportation Management) that has a parameter: shipment ID. However I need to select multiple shipment ID’s in order to generate this report. I currently receive an error, because Ihave not properly set this up.

Here is the error message I get on OTM when I try to run multiple reports at a same time :

At 2017-10-31 10:25:44.456

oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/custom/Shipment/Pick Up Sheet.xdo] failed: due to oracle.xdo.server.ServerException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments  oracle.xdo.webservice.exception.OperationFailedException: PublicReportService::generateReport for reportAbsolutePath [/custom/Shipment/Pick Up Sheet.xdo] failed: due to oracle.xdo.server.ServerException: oracle.xdo.servlet.data.DataException: oracle.xdo.servlet.data.DataException: oracle.xdo.XDOException: java.sql.SQLSyntaxErrorException: ORA-00909: invalid number of arguments  [12501968] ORA-00909: invalid number of arguments --

Would you happen to know where this could come from ?

Antoine

Venkat Thota - BIP

Just close this thread with correct answer and then open new thread.

Antoine KAIBER

Actually I would like to reopen this thread if it's not a problem because my two reports are merged. Do you know how to make 2 distinct reports (either 2 pdf's or 1 pdf after another) ?

Antoine KAIBER

My bad I had made a moistake and now there is no more error message when I try to run multiple reports. Reports don't exactly seem to come out the way they are supposed to but I will look into it. Thank you for your help !

Venkat Thota - BIP

for single input value the report runs fine ?

problem is with query , can you post the query ?

Antoine KAIBER

I changed it but I still have the same error message "ORA-00909: invalid number of arguments --"

Antoine KAIBER

Someone else did this before me and added this in the sql for querying the parameter:

and SH.SHIPMENT_GID=nvl(:P_SHIP_ID,SH.SHIPMENT_GID)

Before this, the error message was "SQL command not properly entered", now it is as i've said "invalid number of arguments".

Sorry i'm a newbie on this, I'm trying to help someone.

Venkat Thota - BIP

SH.SHIPMENT_GID=nvl(:P_SHIP_ID,SH.SHIPMENT_GID) , This will not handle multiple input values at a time.

Use this condition SH.SHIPMENT_GID in(:P_SHIP_ID) instead of above.

Venkat Thota - BIP

Can you post how you defined parameter in sql query?

Generally To handle multiple values you have to use field_name in(:shipment_id) in sql query.