How to get OUT parameter from Oracle SP ?

[Anonymous]

  

The source is a delimited file, it will call a Stored Proc (tDBSP_1) to update some tables. The SP has 30-40 IN parameters, after calling the SP, a few of them + OUT parameter + tDBSP_1 error message will be printed out.

It works well when all the rows call the SP successfully, the output will be like below:

|                                          Print Params                                          |
|=--+-------+--------+---------+------------+-------------------+-----------------=|
|no |vupc |vitemnbr|verrorout|create_id |create_time |tDBSP_ErrorMessage|
|=--+-------+--------+---------+------------+-------------------+-----------------=|
|1 |108020|65996 |         |Modify_Items|07/18/2019 13:24:15|null |
|2 |108039|66040 |         |Modify_Items|07/18/2019 13:24:15|null |
|3 |108040|66040 |         |Modify_Items|07/18/2019 13:24:15|null |
|4 |108078|66097 |         |Modify_Items|07/18/2019 13:24:15|null |

When SP are called successfully, there is no output for verrorout and tDBSP_ErrorMessge.

I would rather to catch them when it fails to call the SP.

When a row on the source file failed to call the SP, I did get an error message out of ((String)globalMap.get("tDBSP_1_ERROR_MESSAGE")) from tWarn_4 ( & same from tDBSP_1): 

|tWarn_4|ORA-06502: PL/SQL: numeric or value error: character string buffer too small

tDBSP_1|java.sql.SQLExceptionRA-06502: PL/SQL: numeric or value error: character string buffer too small

But the Oracle error message doesn't help me find out which row is bad -- the "Print Param" (tLogRow) didn't print out the params when a bad row hit the SP.

Please help me get the OUT param (verrorout) and the bad IN params when the row fails to call the SP.

Thanks!

[Anonymous · Accepted solution]

Hi ,

   The most easy to handle is to catch the error in PLSQL stored procedure using exception block (either specific or use when others)  and reroute the problematic data to an error table.

    You can print the output to log file also from the Stored procedure but now a days its not the recommended approach due to Privacy laws and audit policies since nobody want any customer data to be printed to log files.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved 🙂