DSXchange

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.

Oddly enough, the Stored Procedure stage. What exact 7.x version of DataStage do you have?

I am using DataStage 7.5..
I am new to Stored Procedures. I did not work with them before.
ould you plz tell me how to approach the situation. I read the manual, but I did not get a lot from it.

So First I need to run the job.
Then design one more job with a STP stage and call the procedure. Is this the right way of doing it

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.

Thanks.

I am working on it.

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

You don't pass output parameters. You receive them. They should then appear as two columns on the output link of the Stored Procedure stage.

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

Any Suggestions plz

pradkumar wrote:How to make appear my output parameters (ErrornUM and ErrorText) in column section..

Type them in.

Do you mean to type in columns. I tried in that way. I have seen the following in the output:

ProcCode ProcMessage Errornum Error Mssg
0

Errornum and Erromssg were typed by me.
Does DataStage automatically map the output parameters to the columns I have defined?

That's the idea, provided that the data types match.

I developed a job sequence:

Initial Job-->StoredpRocedure--->Notificationa ctivity

I would like the output of the stored procedure (error num and errortext) in my email.
Currently i am able to egt only logdetails.

How to send the errornum and errortext to my email

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.