Oracle Connector "After SQL statement"

Post questions here relative to DataStage Enterprise/PX Edition for such areas as Parallel job design, Parallel datasets, BuildOps, Wrappers, etc.

Moderators: chulett, rschirm, roy

Post Reply
abc123
Premium Member
Premium Member
Posts: 605
Joined: Fri Aug 25, 2006 8:24 am

Oracle Connector "After SQL statement"

Post by abc123 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
abc123
Premium Member
Premium Member
Posts: 605
Joined: Fri Aug 25, 2006 8:24 am

Post by abc123 »

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.
Teej
Participant
Posts: 677
Joined: Fri Aug 08, 2003 9:26 am
Location: USA

Post by Teej »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
abc123
Premium Member
Premium Member
Posts: 605
Joined: Fri Aug 25, 2006 8:24 am

Post by abc123 »

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?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

"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

"You can never have too many knives" -- Logan Nine Fingers
abc123
Premium Member
Premium Member
Posts: 605
Joined: Fri Aug 25, 2006 8:24 am

Post by abc123 »

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.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

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.
-craig

"You can never have too many knives" -- Logan Nine Fingers
Post Reply