tDBSP does not return output parameters

[stephbzr]

Hello,

I am having problems returning the values of my output parameters from a PL/SQL procedure with the tDBSP component.

 

Here is how I set it up.

 

All input parameter values are given in my tMap. 

I did check the input parameter data. But I have no values for my output parameters. I have output the data to an Excel file.

 

The idea of the procedure is to execute two SQL queries and depending on the results provide a return code. So if I can't get the tDBSP to work, I'll make two tDBInputs that will take those two queries and provide the return code myself. But I would like to know how tDBSP works...

[Anonymous]

Are you expecting the values for id_org, id_ctiam, rep_centre, rep_caisse and rep_resgime to be supplied by the SP on every row returned by your MAIN input? If so, you need to use the "Reload at Each Row" Lookup Model for the SP Lookup and supply the values to the globalMap (that you will see appear). You then need to use the globalMap values that you are creating here to be input to your SP component.

The process is that every row of data from your MAIN input will trigger the SP to fire. But you need to capture the values that you want to use and provide those to the SP so that the SP can be run and the data sent into the tMap.

[stephbzr]

Hello @Richard Hall​ ,

Here are the values of my input parameters that I have to provide to the procedure.

 

The procedure uses them to execute two SQL queries and return a return code (output parameter) to me depending on the result. 

However, the values come from a file that represents several thousand lines. So the "Reload at Each Row" option may increase the processing time I suppose? I had already tried this option for another case. 

With the tDBInput, it is easy to inject data from the tMap into the SQL query, and then retrieve the result in an output from the tMap. But with the tDBSP, I can't do the same...

[Anonymous]

When you use a tMap you are either a) loading all rows from all sources and joining them, or b) loading all rows from the MAIN source and calling the other queries for every row. Option b) is the only option where your data from your MAIN source is being added to any other queries. Since you are using an SP, you only really have the option of option b) here.