sqlcode & sqlstate

[getsatish_gk]

Hi Etlers,

using Oracle Enterprise Stage to load data to oracle db.

need to know the sqlstate along with sqlcode at Job log for every record reject.

Currently, collecting rejects in peekstage with sqlcode & message type=Info.

But, can I get both sqlcode & sqlstate if message type=Info.?
Is there a way to do this ?


(I can get both sqlcode & sqlstate if message type=Fatal.)


Setted arraysize=1

Thanks
Satish

[vmcburney]

Turn column propogation on for your database stage on the Column tab and then replace your Peek stage with whatever stage you want to use to capture the reject messages and add sqlcode and sqlstate as column on the reject link. DataStage will write to those two fields if you put them on the link.

[getsatish_gk]

can anyone ellaborate on this and how to get sqlstate please.

[ray.wurlod]

SQLState is specific to ODBC. You will not get it in an Oracle Enterprise stage.

[vmcburney]

If a column shows up in your Peek stage it means it is being propogated down the link - you can replace the Peek with a Transformer or whatever and add the column that showed up in the Peek. So put a Peek on your Oracle stage reject link, note down the columns produced and add them into your job as named columns.

[dstest]

Hi vmcburney

I added a copy stage to the oracle enterprise stage reject link and in the output columns of the copy stage added a column sqlstate and turned on runtime column propagation option.so I am able to compile the job and when i ran the job i am getting the following error when i try to write the output to sequential stage from the copy stage..

Sequential_File_26: Error when checking operator: Could not find input field "sqlstate".

This tells that there is no column like sqlstate from oracle enterprise stage reject link.

[ray.wurlod]

Read my earlier post.

SQLState is for

Code: Select all

ODBC only

You will not get it in an Oracle Enterprise stage.