SQL procedure from TC on Server

[indianyogi]

All

We designed an interface(Table Coversion) to export jdedwards invoice information to Oracle Financials. There was need to call an Oracle Stored Procedure, which we implemented by calling a batch file which ran the SQL File. Now, we are trying to schedule the job on the server, and I am not sure, how to take care of the batch file thing. I am kind of getting lost as how to call a sql procedure after execution of a TC submitted on the server.

Thanks
Yogi

 

[rdowney]

Make a dummy table in Oracle. Make a trigger that calls you stored procedure. In the final section of ER code in your TC do some table IO to insert a row into the dummy table. This will fire the trigger and that will call your stored proc.

Warning.... The above solution runs inline, so the TC will pause until the SP is complete. If the SP performs massive work, you may get an error because the TS is waiting for the table IO to complete. In this case, you may have to look to Oracle AQ or a periodic DBMS_JOB to process the SP asynchronously.

Hope this helps,
Ralph.

 

[indianyogi]

Hi Ralph,
Although, I have not tried this solution out, I am working in it and I have a very good feeling that its gonna work.
Thanks for the direction..
Warm Regards
Yogi