Oracle Stored Procedure Call with parameters

[Su]

Hi,
I have a job that takes records from a sequential file , does some basic transformations and then links to an OCI8 stage.
In the OCI stage I want to call an Oracle STored procedure that will insert into multiple tables.
I need to pass the the sequential file fields as parameters to this stored proc.
How can it be done? we are forced to specify a tablename in the OCI8 stage. What tablename do i give here? What do i set the updateaction to? user-defined sql?
thanks in advance

[uneumann]

Use ODBC-Stage with Update Action "Call stored procedure"

Regards,
Udo

[Su]

Thanks

[fjgirante]

yes.
But how can i abort my job if my procedure fails.
I explain.
I have a procedure that raise an application error if something goes wrong.
But the best i could do in my job, in that case, was to put a warning in the job log (JDAEDWSITLOADAS4..TR_EXEC.my_job: DSD.BCIPut call to SQLExecute failed.
SQL statement:{call my_job(?,?,?,?)}
SQLSTATE=HY000, DBMS.CODE=20006
[DataStage][SQL Client][ODBC][MERANT][ODBC Oracle driver][Oracle]ORA-20006: my_message
ORA-01839: date not valid for month specified
ORA-06512: at "DW.my_job", line 181
ORA-06512: at line 1


p_cod_plataforma = "AS4"
p_month = "200211"
p_window = "36"
p_ctrl_load = 43755
)
I tried with the tab "Error Codes" of the ODBC stage. I did put the ORA-20006 (my code) in the Fatal Error box, but i only got an warning and the job succeds.

regards

[Triton46]

Only a DBA can kill a procedure.

[fjgirante]

I only want my job to abort if a called procedure raise an error...
Can i do that?

[ray.wurlod]

You can detect it by obtaining the values of link variables on the link that supplies rows via the stored procedure. Do this on another output link, marked as handling rejects, with its counter set to 1 to abort on the first occurrence of a reject.


Ray Wurlod
Education and Consulting Services
ABN 57 092 448 518