OraFAQ Forum: Server Administration » problem with dbms

Home » RDBMS Server » Server Administration » problem with dbms_job executing

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

problem with dbms_job executing [message #61918]

Wed, 09 June 2004 22:41

_simma_dba
Messages: 34
Registered: November 2003

Member

I have very strange problem
When I try to execute some procedure (PROCEDURE_1) using dbms_job it looks like the job has been executed (there is no failures) but when i check my tables i see that job has done nothing.

The problem is when I execute my procedure using:
EXEC PROCEDURE_1; I dont have any problem. What is wrong with the job?

Report message to a moderator

Re: problem with dbms_job executing [message #61920 is a reply to message #61918]

Thu, 10 June 2004 01:20

Maaher
Messages: 7065
Registered: December 2001

Senior Member

Where do you commit?
How's your exception handling in the procedure?

MHE

Report message to a moderator

Re: problem with dbms_job executing [message #61939 is a reply to message #61920]

Fri, 11 June 2004 02:41

_simma_dba
Messages: 34
Registered: November 2003

Member

Here is the procedure that I try to execute using DBMS_JOB packet:

CREATE OR REPLACE PROCEDURE "PROCEDURE_1" IS
BEGIN
INSERT INTO TABLE_1@DB01 (
COL1, COL2, COL3, COL4, COL5, COL6, COL7, COL8, COL9,
COL10, COL11,COL12)

SELECT dnevna_telekom_seq.NEXTVAL@DB01, TO_CHAR(datum,'yyyymmdd') ,posta,'0'||SUBSTR(analitika,1,2),row_number() over(PARTITION BY datum,posta,analitika ORDER BY radnik) rbr,
alfa5, iznos,99,1,TO_DATE(SYSDATE),TO_CHAR(radnik),TO_CHAR(rbtran)

FROM TABLE_2@DB02 t
WHERE dnevnik=31
AND t.ind_ftp=0;

UPDATE TABLE_2@DB02
SET ind_ftp=1
WHERE dnevnik=31
AND ind_ftp=0;
COMMIT;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK;
END PROCEDURE_1;
/

As you see I use two database links DB01 and DB02. And commit is inside procedure.

Report message to a moderator

Re: problem with dbms_job executing [message #61953 is a reply to message #61939]

Sun, 13 June 2004 22:14

Maaher
Messages: 7065
Registered: December 2001

Senior Member

You have no trace whatsoever when your procedure fails.

Look at the example below. The first one is like yours: when an exception occurs, just rollback. Can you see that Oracle doesn't indicate that you have an exception? In the second version I issue a rollback as it is desired, and then I re-raise the exception, so that I have a trace about the error (no_data_found) that occurred.

SQL> begin
  2    raise no_data_found;
  3  exception
  4    when others then rollback;
  5  end;
  6  /

PL/SQL procedure successfully completed.

SQL> begin
  2    raise no_data_found;
  3  exception
  4    when others then
  5      rollback;
  6      raise;
  7  end;
  8  /
begin
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 6

Basically, you don't handle your exceptions. You just get "PL/SQL procedure successfully completed" without knowing whether all went well or not.

HTH,
MHE

Report message to a moderator

Re: problem with dbms_job executing [message #61955 is a reply to message #61953]

Mon, 14 June 2004 02:32

_simma_dba
Messages: 34
Registered: November 2003

Member

Well thx so much. I'll put some more usable exceptions in my code.I think that it can be something with two database links maybe. Maybe I must use some multimaster replication. Could it be problem??

Report message to a moderator

Previous Topic:	why two_task
Next Topic:	redo log contetnion & disk sort

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Fri Sep 20 14:31:08 CDT 2024