DSXchange

Posted: **Mon Mar 09, 2009 10:35 am**

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

Posted: **Mon Mar 09, 2009 11:32 am**

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.

Posted: **Mon Mar 09, 2009 11:48 am**

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.

Posted: **Mon Mar 09, 2009 11:49 am**

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

Code: Select all

BEGIN
sql statement;
commit;
END;

Posted: **Mon Mar 09, 2009 11:51 am**

repost

Posted: **Mon Mar 09, 2009 11:51 am**

repost

Posted: **Mon Mar 09, 2009 11:53 am**

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.

Posted: **Mon Mar 09, 2009 11:55 am**

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

Posted: **Mon Mar 09, 2009 11:57 am**

betterthanever wrote: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.

Posted: **Mon Mar 09, 2009 11:59 am**

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

Posted: **Mon Mar 09, 2009 2:22 pm**

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.

DSXchange

Oracle open command

Oracle open command

Re: Oracle open command

Re: Oracle open command

Re: Oracle open command

Re: Oracle open command