DSXchange

Is there a way to see the output of:

dbms_output.put_line

in an "After SQL statement"?

It doesn't show up in the Director.

It won't if all you do is "put", to see the messages you need to call DBMS_OUTPUT.ENABLE first. And then I'm not sure as it is really a PL/SQL debugging tool so may not be possible to get the lines unless you wrap what you are doing up in a stored procedure.

I tried to raise an error by doing a SELECT into a variable from an empty table. The code hits the exception block and raises an error like it should in Oracle but when I put it in Datastage "After SQL statement", it executes fine. There are no messages in the Director. I also did the DBMS_OUTPUT.ENABLE in the exception handler.

I would think that this is a very common requirement.

Actually, not quite. The After Job and After Stage queries are typically seen as "do this after we're done." Typically maintenance-level stuff that are not critical, and failures are typically not captured as fatal (it is done at the end of the job's/stage's run).

If you are doing something critical, please use the Before Job/Stage logic.

If you still think the behavior is incorrect, please open a PMR with IBM Support to see if this is agreed upon by the development team there.

-T.J.

What exactly is your goal here, what do you plan on running After SQL? It's not really meant for supporting an anonymous block or items of that ilk AFAIK, if you need to do anything like that I would suggest you build a proc and then call the proc from there.

Thanks Teej and Craig for your responses. Craig, building a stored procedure in Oracle and calling it from Datastage is not an option. I have to do this from within the job.

Teej, it has to be done after all inserts are done in Oracle connector stage. That's why I chose the "After SQL statement".

So there is no way to log an error from this part of the stage?

"It has to be done"... what it? Can you answer my first question in the last post please? What you are needing to do is far too nebulous at the moment.

Craig, I need to update some data in the inserted rows after all rows have been inserted into the table. That's why I need to use "After SQL statement".

Let me know what other detail you need.

Okay... "update some data" is still pretty darn vague. Is something about this stopping you from using a simple DML statement to do the update? And why all the questions about put_line, anonymous blocks, error logging, exception handling and such? That would imply much more is needed than a simple update of some data, something requiring PL/SQL and yet a stored procedure "is not an option".

Seems to me there's not much else to say until we have a better idea what exactly it is you need to do. I would imagine this could all be done "in job", after SQL or elsewhere, but there's no way to know. Yet.