using shell Script to access Stored Proc

Jamesvanam · Post by **Jamesvanam** » Wed Sep 08, 2004 7:29 am

I've a Stored Procedure in The Oracle DB which logs the start time, end time, process time and user name, these are send as IN params to stored proc. I need to use this in my Data Stage job and output a Logno. what is the best way to do this. I was thinking about using shell script and calling it in after stage sub-routine of Transformer. could anybody help me on this. like what would be the script if I use shell script.

Help is greatly appreciated.
Thank you
James

denzilsyb · Post by **denzilsyb** » Wed Sep 08, 2004 9:22 am

hi James

it sounds like a complicated way of doing things. If you have to use the stored procedure, then I would suggest you gather the required INPUT parameters for the stored proc by using parameters in a job control. This information (like start time, end time, process time) can be obtained by doing oconvs within the job control of the system date - Or DSGetJobInfo might give you this information; you'll have to check. The user name you get deends on whether you need the DB user or the DS user - you'll know what you are after.

You can have a batch file (I see you are on windows) that calls the stored procedure using the INPUT params you provide. How you code that the real fundis of the forum might be able to help.

The question now remains as to what you are doing with the log number. I take it the log number comes from the stored procedure - if you need to output the log number to the DS log, then you might end up having to do an ISQL query on the DB to get the log number provided the stored procedure writes the log number to a table somewhere.

But I think the solution lies in thinking out of the box; I get the feeling you are using DS in a system that has been working for a while, and now you need to conform with existing methods. Nothing wrong with that, its just that sometimes people need to be re-educated into new ways of doing old things.

by the way - I do believe the ODBC stage can execute a stored procedure, but I have not used it as yet and am not sure if paramaters can be parsed through; perhaps you can read up on it and let us all know if you get it working.

good luck

Jamesvanam · Post by **Jamesvanam** » Wed Sep 08, 2004 9:54 am

Thanks Denzilsyb,
what I'm doing is I INSERT these Input values[start time(DSJobStarttime, end time(SYSDATE), process name and user name(last eidted user) ] into a log TABLE and assign a log number for this record which is the output from the stored proc. when I import this stored proc into Data Stage it gives me error (I beleive Datastage doesn't support out params from a stored proc). and this stored procedure I'll run only once for the job not for every record processed.
please suggest.
James

Jamesvanam · Post by **Jamesvanam** » Wed Sep 08, 2004 2:01 pm

Actually I was wondering if there is a way to invoke a stored Proc from after SQL tab in the target OCI9stage. I'd be using the IN params from the JOB.

denzilsyb · Post by **denzilsyb** » Thu Sep 09, 2004 12:45 am

I am sure you can, even if it is via your original idea of a shell script/batch file.

If not, would you be able to have DS generate a log number in the same way the DB does?

chulett · Post by **chulett** » Thu Sep 09, 2004 7:19 am

Jamesvanam wrote:Actually I was wondering if there is a way to invoke a stored Proc from after SQL tab in the target OCI9stage. I'd be using the IN params from the JOB.

Sure, simply "call" the procedure in the Before or After SQL tab. Not sure about the use of Job Parameters at the same time, give that a shot and let us know if it works for you or not.

Jamesvanam · Post by **Jamesvanam** » Thu Sep 09, 2004 9:23 am

When I try to use the Procedure in After SQL it gives me Error: ORA-06576: not a valid function or procedure name.
the syntax I've used is {call procedure log_process(arguments)}
is something wrong with my syntax.
Help me.
Thank you
James

chulett · Post by **chulett** » Thu Sep 09, 2004 9:27 am

Jamesvanam wrote:the syntax I've used is {call procedure log_process(arguments)}

The syntax is simply "call log_process(arguments)".

Jamesvanam · Post by **Jamesvanam** » Thu Sep 09, 2004 11:44 am

Thanks craig,
one more small thing I'm trying to figure out. one of the arguments in the stored Proc i'm calling needs current date like SYSDATE ,and the other three arguments i'm using from the columns of the target OCIstage(where i'm writing the stored proc) and a job parameter. basically what I'm trying to do is send the jobstart time , endtime of the job,processname and a last edit user name.
Thank you

kduke · Post by **kduke** » Thu Sep 09, 2004 12:25 pm

James

You can download EtlStats which is several jobs which will extract job start and end times plus row counts and push them into a database. You download on Ascential's ADN or my tips page below. Save you lots of time. It will get row counts for all job, all jobs in a sequence, all jobs in a category or one specific job. There is a DSX to import the jobs. There is documentation to show you how it works.

Jamesvanam · Post by **Jamesvanam** » Thu Sep 09, 2004 12:55 pm

call GPADMIN.PKGGPI.LOG_PROCESS('process_name','LAST_EDIT_DATE','TO_CHAR(SYSDATE,'YYYY-MM-DD HH24:MI:SS') ','LAST_EDIT_USER')

I'm trying to use this in my after sql, and error message I get is
:Missing right paranthesis. no clue as to where i missed it.

Thanks in advance
James

chulett · Post by **chulett** » Thu Sep 09, 2004 1:09 pm

Me, I'd do one of two things:

* Ditch the "sysdate" parameter and just generate it inside the procedure, unless there are times when something else is passed in other than the current date / time.

* Dump the procedure and do the equivalent in DataStage, which is what Kim suggested. Writing something to capture stats is pretty straight-forward and besides, Kim has done most (if not all) of it for you already.

Jamesvanam · Post by **Jamesvanam** » Thu Sep 09, 2004 1:17 pm

Thanks Kim and Duke appreciate your help. I'll have to talk to my team leader about using Data stage instead of the procedure.
Thanks Again

kduke · Post by **kduke** » Thu Sep 09, 2004 3:20 pm

Please just Kim. Duke is my last name. Kim is my first name. I know Kim is a last name in other countries but I am not a formal type of guy. Just an old country boy from Oklahoma.