Oracle open command

[samsuf2002]

I have a requirement to delete some data from source before loading into target therefore I am running a delete command with where condition in oracle stage OPEN command option.

It runs fine till it find data to delete but when there is no data found to delete the job aborts with errors

Code: Select all

Oracle call failed; sqlcode = 1,403; message: ORA-01403: no data found
Fatal Error: Invalid open command : DELETE FROM ........

I searched the forum but couldn't find any solution. Can any one suggest me anything that can solve the issue.

Thanks in advance

[DSguru2B]

Look for any ORACLE env. variable to turn off/on warnings like this. I am not at a px site so cannot be of much more assistance.

[samsuf2002]

DSGuru2B,

I don't find any warnings of those kind, all other fatals/warnings are

Code: Select all

node_node0: Player 1 terminated unexpectedly.
main_program: APT_PMsectionLeader(1, node0), player 1 - Unexpected exit status 1.
main_program: Step execution finished with status = FAILED.
warning --- main_program: Received SIGPIPE signal caused by closing of the socket on port 13,400.
No output will be sent to port 13,400 for the rest of the job.

[betterthanever]

here is how you should use open/close command in oracle/odbc stages.

Code: Select all

BEGIN
sql statement;
commit;
END;

[betterthanever]

repost

[betterthanever]

repost

[bollinenik]

Hi,
You have to split the task into couple of jobs like, one job will read the data and write into a sequential file and at the same time you can delete records by using other ;pipeline', and second job would be loading data into target so that it will not give any problems.

[betterthanever]

Code: Select all

node_node0: Player 1 terminated unexpectedly.
main_program: APT_PMsectionLeader(1, node0), player 1 - Unexpected exit status 1.
main_program: Step execution finished with status = FAILED.
warning --- main_program: Received SIGPIPE signal caused by closing of the socket on port 13,400.
No output will be sent to port 13,400 for the rest of the job.

[/quote]

this error means you cannot see the job progress in the designer meaning the performance stats will not be shown on the designer

[samsuf2002]

here is how you should use open/close command in oracle/odbc stages.

Code: Select all

BEGIN
sql statement;
commit;
END;

I added the missing commands in my query and it worked great.

Thanks Betterthanever and DSGuru2B for you time and help.

[samsuf2002]

I would appreciate if I can know the reason why my previous query didn't work.

[chulett]

The previous query that you didn't post?

The anonymous block changes it from SQL to PL/SQL and the 'error' basically doesn't get reported since there's no exception handler code.