Page 1 of 1
HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Posted: Wed Aug 15, 2007 6:36 am
by reddy.vinod
Hai every body,
i have written one procedure ,using this procedure i am updating one table only,procedure don't need any input and output, but after one job i have to execute this job.how can i execute this procedure in datastage,can i execute procedure in before sql statement.
Re: HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Posted: Wed Aug 15, 2007 7:10 am
by sachin1
dear can you please elaborate on the sequence mentioned which confusing but after one job i have to execute this job, if we can analyze the sequence, you can get proper response.
Re: HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Posted: Wed Aug 15, 2007 9:06 am
by sachin1
i have worked with call a .sql file from after job routine which is in JOB Properties, General tab.
in after job subroutine drop down select ExecDos and next to is is Input value where you can type or call your .sql file which in turns calls a oracle procedure.
just a example
ExecDos sqlplus -s oracle/
oracle@BIODS.WORLD @#directory#\in\1.sql.
depending upon your situtation you can work it out.
Re: HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Posted: Wed Aug 15, 2007 8:57 pm
by reddy.vinod
sachin1 wrote:i have worked with call a .sql file from after job routine which is in JOB Properties, General tab.
in after job subroutine drop down select ExecDos and next to is is Input value where you can type or call your .sql file which in turns calls a oracle procedure.
just a example
ExecDos sqlplus -s oracle/
oracle@BIODS.WORLD @#directory#\in\1.sql.
depending upon your situtation you can work it out.
Thanks, Sachin . i will try u r method.
Posted: Wed Aug 15, 2007 10:44 pm
by bkumar103
It can be done in sequence job.
Use the stages exceute command and job activity stage.
The job activity stage will be used to run the job. "execute command" stage will be excute first which will run your stored prcedure as
sqlplus -S <login>@<database name>/<passoword> <sql file to excute>
put sqlplus -S in command and
others as the argument.
in sequence job the link will be driven from "execute command" to "job activity" stage.
Posted: Wed Aug 15, 2007 10:45 pm
by bkumar103
It can be done in sequence job.
Use the stages exceute command and job activity stage.
The job activity stage will be used to run the job. "execute command" stage will be excute first which will run your stored prcedure as
sqlplus -S <login>@<database name>/<passoword> <sql file to excute>
put sqlplus -S in command and
others as the argument.
in sequence job the link will be driven from "execute command" to "job activity" stage.