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.
HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Moderators: chulett, rschirm, roy
-
reddy.vinod
- Participant
- Posts: 36
- Joined: Mon Jul 16, 2007 3:37 am
- Location: USA
Re: HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
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
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.
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.
-
reddy.vinod
- Participant
- Posts: 36
- Joined: Mon Jul 16, 2007 3:37 am
- Location: USA
Re: HOW TO CALL STORED PROCEDURE FROM DATASTAGE SERVER
Thanks, Sachin . i will try u r method.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.
VINOD
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.
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.
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.
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.