How to get OUT parameter from Oracle SP ?

Seven Stars

How to get OUT parameter from Oracle SP ?

call proc.JPG  

 

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.SQLExceptionSmiley SurprisedRA-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!

 

 

 


Accepted Solutions
Employee

Re: How to get OUT parameter from Oracle SP ?

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 :-)


All Replies
Employee

Re: How to get OUT parameter from Oracle SP ?

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 :-)

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 3

Read about some useful Context Variable ideas

Blog

Talend Studio Improvements for API Services

Taken a look at the Talend Studio improvements for API Services

Watch Now

Data Integration Success Stories

Take a look at some Data Integration success stories

Read