Environment variable to check Stored proc call

[abhishekachrekar]

We have a job which calls a stored Proc using Stored procedure stage.
The stored procedure has some insert statements. We have handled exceptions in the stored proc and any exception is captured in the log table.

When the job is run in dev environment the first insert in the stored proc fails due to NULL value in non-nullable field. Same is captured in the log table.
However when the same job is run in test environment the Stored proc runs successfully. However no data is inserted in any table nor in the log table.
We don't see any SP abort message in the DS logs nor in the Log table.
So we are not clear if the SP call was successful from datastage.
Is there any env variable which we can set which will help us in analyzing if the SP call was being made from Datastage?

[abhishekachrekar]

Today we ran the job in test environment again and the stored proc did actually run. The insert in the SP however failed due to NULL being inserted in Non nullable column. Same was captured in the log table.
However we have no way to know what happened in the last run which happened last month.

I am closing this post as the prob is temp solved. However I would like to know if there is a way to track if the SP call is actually being made from datastage.

Last week we had another issue with some other SP call from datastage job.
The job was aborting with error "Error: APT_CombinedOperatorController,0: Fatal Error: Fatal: ORA-06502: PL/SQL: numeric or value error: character string buffer too small."
This error was coming during the SP call from the job.

The SP was running fine when executed from the SQL developer however it was failing while running from DS job.
After analysis we found that the Length of the ReturnMessage field in the SP stage was insufficient. After increasing the length of the ReturnMessage field the error vanished and the SP finished successfully.

Looking at the issues we are facing in SP call from DS these last couple of weeks we want to be extra careful with this stage.

[JRodriguez]

Try calling the store procedure from a regular Oracle connector instead of from the Store Procedure stage, set the environment variable CC_MSG_LEVEL in the DataStage job to fit your desire level of messages...The connector will show the logs

Also check if NLS is enabled, watch out for VARCHAR parameters passed to the Store Procedure, recently we experienced a similar issue that the store procedure wasn't triggered and not reported in the DS logs and was due to the NVARCHAR. If allow call the SP from a Server job instead of a parallel one

Hope it help