Did you know? Running Oracle blocks, trapping rejects &

[GIDs]

We have ben trying to come up with a solution to trap rejects... our requirements were...

1. Abort the job on 1st record reject, but trap the rejected record before aborting (on couple of jobs)
2. Trap all types of rejects (not only NOT NULLs, but even referential etc) but don't abort the job (on couple of other jobs)

So, here is how we did it...

1. In the user-defined SQL, we wrapped the SQL in a begin....end block and basically added in an EXCEPTION handler within the block
2. Within the exception handler, as you all know, we can handle the different oracle thrown errors in the way we want to, you can insert the rejected record onto a table or do something else
3. If you want to abort, you can put in a raise application error, which basically tells datastage that it is a fatal error and so it ABORTs, but before you do this, you would have obviously pushed the rejected record into a separate table

This works pretty neatly and best-of-all gives us the power to handle DB rejects in the way we want to.

And, of course, since Datastage seems to be fine with passing these blocks to the database, we can do much more (whoever knows PL/SQL will obviously like this)

Just thought of sharing this with everybody

[henryb01]

I have tried using a begin end block but I get a runtime error. The code works fine when we don't have the begin/end portion. But I would like to use your technique for trapping errors. Any ideas what my problem might be? Or does the begin/end only work in version 7?

Here is the runtime error:

HRMS3..ORAOCI9_1: ORA-06550: line 1, column 29:
PLS-00103: Encountered the symbol "END" when expecting one of the following:

:= . ( % ;

Here is my code:

begin apps.TEST_DATASTAGE() end;

[rsrikant]

Hi Henry,

This is a syntax problem.

Each statement in a Begin End block in PL/SQL should terminate with a semicolon.

The code should be

Code: Select all

begin apps.TEST_DATASTAGE(); end;

HTH,
Srikanth

[mandyli]

Hi

I hope this is syntax problem only. you shoud use ; for each and every SQL stmt..

[ray.wurlod]

Arguably, though, "end" is not an SQL statement but rather a metasyntactic variable marking the end of a block of SQL statements.

[henryb01]

I should have noted that this is a Target/Update Oracle OCI. My 'update action' is 'user-defined sql'. Ideally I would like to put my begin/end block in the user defined tab of the SQL tab but I get the following error when I try that: "%1 is not supported for pre-defined update action". I assume this means that I don't have an 'Update/Insert' sql statement in the user defined sql area and that I don't reference my input column data field. I have tried this "BEGIN apps.TEST_DATASTAGE(:1) END;" in the user defined area but I get the same error. Has anyone called an Oracle procedure/package/function on a Target OCI?