Stored Procedure
Moderators: chulett, rschirm, roy
Stored Procedure
Hi
I am having a stored procedure code in TOAD.
I am also having a DataStage Job.
My requirement is to call that procedure after this job finishes. The procedure has only output parameters and theya re absically Errorcode and Text. The procedure should return only these two values. These should be written to a log file.
How to accomplish this.
I am having a stored procedure code in TOAD.
I am also having a DataStage Job.
My requirement is to call that procedure after this job finishes. The procedure has only output parameters and theya re absically Errorcode and Text. The procedure should return only these two values. These should be written to a log file.
How to accomplish this.
Pradeep Kumar
Create a new job with a Stored Procedure stage linked to a Transformer and then a Sequential file stage. Set the Procedure type to 'Source' on the Syntax tab. Run it after the other job finishes, a Sequence job can do that easily for you unless you've got another form of job control in place.
And find someone there to help you who is familiar with stored procedures. Doesn't need to be a DataStage person, but of course that will help... they are pretty straight-forward to work into a job.
And find someone there to help you who is familiar with stored procedures. Doesn't need to be a DataStage person, but of course that will help... they are pretty straight-forward to work into a job.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
Thanks for all the suggestions.
Right Now I am able to work on STP stage. But I am facing with a trivial problem
My job requirement is (A) First I need to run a job (simple mapping). If this job finishes succesfully, it should call a Stored procedure.
(B) The stored procedure is just having only two output parameters (ErrorNum, Errortext). I want these two on output via an email.
So I went a head with a job sequence. First job activity is (A) second job activity is (B).
But how to pass these output parameters in the STP job.
My STP job is:
STP STAGE--->TX---->FLATFILE.. (TX is not doing anything).
I am unable to pass the output parameters
Right Now I am able to work on STP stage. But I am facing with a trivial problem
My job requirement is (A) First I need to run a job (simple mapping). If this job finishes succesfully, it should call a Stored procedure.
(B) The stored procedure is just having only two output parameters (ErrorNum, Errortext). I want these two on output via an email.
So I went a head with a job sequence. First job activity is (A) second job activity is (B).
But how to pass these output parameters in the STP job.
My STP job is:
STP STAGE--->TX---->FLATFILE.. (TX is not doing anything).
I am unable to pass the output parameters
Pradeep Kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
In my case ProcCode and ProcMesage are appearing by default.
How to make appear my output parameters (ErrornUM and ErrorText) in column section..
My piece of procedure looks like this
CREATE OR REPLACE PROCEDURE Proc_name
(errornum out number,
errortext out varchar2
)
AS
CURSOR c_user IS
select * from table_name
I declared my procedure as SOURCE type
How to make appear my output parameters (ErrornUM and ErrorText) in column section..
My piece of procedure looks like this
CREATE OR REPLACE PROCEDURE Proc_name
(errornum out number,
errortext out varchar2
)
AS
CURSOR c_user IS
select * from table_name
I declared my procedure as SOURCE type
Pradeep Kumar
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
First tell us what kind of activity you used for your stored procedure in a job sequence. I've searched my Palette and can't find one called Stored Procedure.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.