DSXchange

I want to write a routine, which needs to call a oracle database procedure and return the error or success code.

Based on that the job should get failed or success.

i have thought of ODBC stage, but we need to create ODBC datasource in the client for the stage to connect.

But since iam not sure how to deploy that thinking of writing a routine and just calling the procedure in the routine

I have searched forum and pdf also not able to trace a suitable example.

regards

Magesh S

In your routine use the DSExecute API to run system commands. By running sqlplus with the appropriate information you can get the results and error checking you desire.

You are going to need a properly configured Oracle client on the DataStage server machine no matter what you want to do. Whether you use a DataStage job (the preferred approach) or not, what you're creating is a client application as far as Oracle is concerned.

So do think about using the tool properly; the OCI and ODBC stages can already do what you want. Your alternatives are to write a routine using BCI functions (search the Forum) or to write a shell script to call sqlplus (again search the Forum).