Stored Procedure

pradkumar · Post by **pradkumar** » Mon Nov 20, 2006 10:32 pm

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.

chulett · Post by **chulett** » Mon Nov 20, 2006 10:35 pm

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

pradkumar · Post by **pradkumar** » Mon Nov 20, 2006 10:38 pm

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.

pradkumar · Post by **pradkumar** » Mon Nov 20, 2006 10:49 pm

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

chulett · Post by **chulett** » Mon Nov 20, 2006 11:05 pm

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.

pradkumar · Post by **pradkumar** » Mon Nov 20, 2006 11:17 pm

Thanks.

I am working on it.

pradkumar · Post by **pradkumar** » Tue Nov 21, 2006 1:53 pm

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

ray.wurlod · Post by **ray.wurlod** » Tue Nov 21, 2006 3:48 pm

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.

pradkumar · Post by **pradkumar** » Tue Nov 21, 2006 4:48 pm

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

pradkumar · Post by **pradkumar** » Tue Nov 21, 2006 6:10 pm

Any Suggestions plz

chulett · Post by **chulett** » Tue Nov 21, 2006 6:20 pm

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

Type them in.

pradkumar · Post by **pradkumar** » Tue Nov 21, 2006 7:15 pm

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?

ray.wurlod · Post by **ray.wurlod** » Wed Nov 22, 2006 7:38 am

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

pradkumar · Post by **pradkumar** » Wed Nov 22, 2006 12:03 pm

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

ray.wurlod · Post by **ray.wurlod** » Wed Nov 22, 2006 1:27 pm

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.