How to capture oracle exceptions in DS Px

[srini_ramesh]

Hi.
I need to capture oracle raised exceptions like Inserting Null into Non-nullable field, lookup failure exception, or any other exception that would cause datastage to abort the job while running. I wish to capture such atributes in a separate table. Please suggest if there is a method to do so in Datastage Px.

[T42]

You wish to capture all of the output log. There are several options for that -- and a search of this forum will help you find the answer you may want to use.

One option is to use "dsjob" to pull the log. A search will yield further information.

Good luck.

[vmcburney]

If you are doing an update and not a bulk load then you can use an output reject link. When you choose a write method of Upsert you will see the option appear for "Output Reject Records = False". Set this to try and create an output link leading to a staging file or to your database errors table. You will see the link automatically take the dashed appearance of a reject link.

Failed update rows will be sent down this link. I am not sure if there is any way to get the error message or whether you have to refer back to the job log for these details.

[ailuro]

Automatically included in the reject link of an Oracle Enterprise stage with properties mentioned in vmcburney's reply is the sqlcode (Integer).

E.g., -1400 (cannot insert NULL)

[mmorelli]

I am using a reject link from an Oracle Enterprise stage to capture rejects from an Upsert as you describe. I am particulary interested in the Oracle SQLCODE, but it does not seem to be "automatically" included in the reject link. Can you tell me exactly how you built your reject link?

Thank you.

Automatically included in the reject link of an Oracle Enterprise stage with properties mentioned in vmcburney's reply is the sqlcode (Integer).

E.g., -1400 (cannot insert NULL)

[ailuro]

Hi mmorelli

Make sure that:
Ora Ent Stage > Input > Properties > Write Method = Upsert

The column sqlcode:Integer will then be included as the last item under:
Output > Columns

Also, don't forget to set:
Input > Properties > Output Reject Records = True


děkuji.

[mmorelli]

Hi děkuji,

Thanks very much for you help. Unfortunately, a "sqlcode:Integer" column is not automatically being added to our Ora Ent stages when they are configured as you describe.

I wonder if we're using different versions. We have 7.1r2, and I'm using the Oracle Enterprise Stage, apparently named "PxOracle", version 1.0.0, against Oracle 9i.

Your input is greatly appreciated.

Mike

[ailuro]

Hi Mike

Ah, we're using v7.5.

Can you manually specify the table definition of the reject link? In v7.5, the columns are read-only and automatically taken from the input link with the additional sqlcode.

If you can, try to specify the table definition as v7.5 would.


PS. "děkuji" means "thank you"

[mmorelli]

FYI, if anyone is using 7.1r2 and is stuck like I was, a colleague figured it out:

Send the Oracle reject link to a Modify stage which has the specification "SQLCODE=SQLCODE". Then include a column named "SQLCODE" in the output from the Modify stage.

[manuel.gomez]

Appart from this, there was another field that could be added to the reject link, to get the ORA error.

I am sure I saw this before in this forum (and even some entry in the chulet blog talking about this) but I cant find either!!!!!

If anyone could tell me, thanks a lot

[ray.wurlod]

I suspect you're thinking of the link variables available in server jobs.

[chulett]

I suspect so too.

[manuel.gomez]

So, according to your answer, is not possible to get oracle code error (ORA-XXXX) when inserting data to Oracle database, using reject link?