Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: August 30, 2001 - 9:56 pm UTC

Last updated: October 13, 2003 - 12:00 pm UTC

Version: 817

Viewed 1000+ times

You Asked

Hi Tom
I have a question on a stored procedure Im trying to create as follows . I have tables x,y,z .
My procedure deletes all the tables and then populates each of them as follows:

Create or Replace procedure Ins as
Begin
Delete from x;
Insert into x
select * from tmp;

Delete from y;
Insert into y
select * from tmp2;

Delete from z;
Insert into z
select * from tmp3;

end;


Could you please help me in answering the following questions.

1) How should I error handle if I get errors at each insert stmt. Would the procedure fail at the first error or just give me an error and proceed to the other inserts. I have seen an error at the 3rd insert, the other 2 inserts run with success.

2) How should I incorporate the Exception handling, what kind of exceptions should I take care in this code. I have added :
EXCEPTION
When Others Then
ErrMsg:=SQLERRM;
RAISE_APPLICATION_ERROR (-20016,ErrMsg);
END;
Is this good enough?
3) Should I put Commit statements after each Insert?

Another question on dbms_job:

Is there any way I can put the job scheduling statement in a script and when my user runs it the script asks him at run time the time he wants to schedule it.Can I do the following:

dbms_job.submit(:jobno, 'package_name;', &a,&b)

But then how would I interpret his values as following if he enters 12:00am in the following stmt.

dbms_job.submit(:jobno, 'package_name;',
trunc(sysdate) + 1 + 0/24, 'trunc(sysdate) + 1 + 0/24');

How can I interpret the trunc(sysdate) + 1 + 0/24 AND
'trunc(sysdate) + 1 + 0/24' in the above stmt


Thanks


and Tom said...

If you want to make sure all three succeed or fail (regardless of what the CALLER does), you should use a savepoint. To understand what I mean and why I say that -- a simple example will help. You might have to read this two or three times to appreciate it (and if you are interested, I have more examples and more detail in my book in the chapter on transactions). We'll start with the tables:


ops$tkyte@ORA817DEV.US.ORACLE.COM> create table x ( a int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table y ( a int );
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table z ( a int check (a<0) );
Table created.

I put a check constraint on Z so the insert into Z will always fail in my examples...

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table tmp as select 1 a from dual;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table tmp2 as select 2 a from dual;
Table created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> create table tmp3 as select 3 a from dual;
Table created.

tmp, tmp2, tmp3 each have 1 row. Now we do your procedure as it exists:

ops$tkyte@ORA817DEV.US.ORACLE.COM> Create or Replace procedure Ins
2 as
3 Begin
4 Delete from x;
5 Insert into x
6 select * from tmp;
7
8 Delete from y;
9 Insert into y
10 select * from tmp2;
11
12 Delete from z;
13 Insert into z
14 select * from tmp3;
15 end;
16 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec ins
BEGIN ins; END;

*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C00873) violated
ORA-06512: at "OPS$TKYTE.INS", line 13
ORA-06512: at line 1


ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from
2 ( select * from x union all select * from y union all select * from z );

COUNT(*)
----------
0

that shows if the statement is executed AND the error is not handled (allowed to propagate back to the client) -- NONE of the inserts "sticks". That is because Oracle treated that statement (the procedure call) as an ATOMIC operation. It silently executed the code like this:

begin
savepoint X;
ins;
exception
when others then rollback to savepoint X; RAISE;
end;

so, when ins failed -- it rolled back ALL work done by ins (but only that work done by INS -- any prior outstanding work is NOT rolled back!!

Now, how could someone cause the outcome to be different? easy:


ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 ins;
3 exception
4 when others then NULL;
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from
2 ( select * from x union all select * from y union all select * from z );

COUNT(*)
----------
2

See -- here the first two inserts "stuck" (they are not committed yet, a rollback makes them go away as we see below but they are there). The third insert that failed -- its not there.

This happened because as far as Oracle was concerned INS did not fail this time. The caller caught and ignored the error!!. So, lets rollback and see how we can prevent this behavior.


ops$tkyte@ORA817DEV.US.ORACLE.COM> rollback;
Rollback complete.

ops$tkyte@ORA817DEV.US.ORACLE.COM>
ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from
2 ( select * from x union all select * from y union all select * from z );

COUNT(*)
----------
0

ops$tkyte@ORA817DEV.US.ORACLE.COM> Create or Replace procedure Ins
2 as
3 Begin
4 SAVEPOINT ins_sp;

5
6 Delete from x;
7 Insert into x
8 select * from tmp;
9
10 Delete from y;
11 Insert into y
12 select * from tmp2;
13
14 Delete from z;
15 Insert into z
16 select * from tmp3;
17 exception
18 when others then
19 rollback to savepoint ins_sp;
20 RAISE;
21 end;

22 /

Procedure created.

ops$tkyte@ORA817DEV.US.ORACLE.COM> exec ins
BEGIN ins; END;

*
ERROR at line 1:
ORA-02290: check constraint (OPS$TKYTE.SYS_C00873) violated
ORA-06512: at "OPS$TKYTE.INS", line 20
ORA-06512: at line 1


ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from
2 ( select * from x union all select * from y union all select * from z );

COUNT(*)
----------
0

here the behavior is of course unchanged (the line the error appears to be on is different that is all -- its coming from the exception block now -- not the line with the insert....

More importantly -- the other example:


ops$tkyte@ORA817DEV.US.ORACLE.COM> begin
2 ins;
3 exception
4 when others then NULL;
5 end;
6 /

PL/SQL procedure successfully completed.

ops$tkyte@ORA817DEV.US.ORACLE.COM> select count(*) from
2 ( select * from x union all select * from y union all select * from z );

COUNT(*)
----------
0

doesn't have the inserts either! Now, any prior outstanding work (inserts/updates/deletes you did before the call to INS that had yet to be committed) would still be pending -- we did not roll them back -- we only rolled back the work done in INS....




2) see above

3) NO, never, don't do it. You should commit when your business transaction is DONE. Never commit before that. Commit when your logical unit of work is complete and not a second before (again, I beat this topic to death in my chapter on transactions with more examples...)




As for the DBMS_JOB question -- you would have to write a routine that took 12am and figured out what that meant and then it would call dbms_job to schedule the job. That is, write a procedure "schedule_job( p_what, p_when )"

you would put into your script then:

exec schedule_job( 'package_name;', '&a' )

they would enter 12am -- your procedure schedule_job would get the string '12am' and would figure out using logic you write that that means "trunc(sysdate)+1" and call dbms_job appropriately.



Rating

  (13 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

A reader, August 31, 2001 - 10:48 am UTC

Tom
So where should I put the commit. At the end, above the transaction?

Tom Kyte
August 31, 2001 - 10:55 am UTC

My personally -- i like to let the CALLER do the commit. What if one day -- your INS procedure becomes part of a larger transaction?? In that case -- calling you would prematurely commit their transaction.

I believe transaction control should be in the hands of the caller. Your call would be something like:

begin
ins;
commit;
end;




A reader, August 31, 2001 - 12:53 pm UTC

Hi Tom
Can I put in the Exception block a method to record the error msg in a table if my proc fails as follows:

EXCEPTION
When Others Then
ErrMsg:=SQLERRM;
RAISE_APPLICATION_ERROR (-20016,ErrMsg);
insert into log(dt,msg)
values(sysdate,errmsg);
End;

But When the procedure fails it does not get recorded in the log table. what am I doing wrong.
Thanks


Tom Kyte
August 31, 2001 - 1:02 pm UTC

Well, in your example -- the insert it never gotten to! you raise application error before you get there.

You can only do this if you have Oracle8i or up -- you need an autonomous transaction to do this properly. You would code:

create procedure log_error( p_msg in varchar2 )
as
pragma AUTONOMOUS_TRANSACTION;
begin
insert into log ( dt, msg ) values ( sysdate, p_msg );
COMMIT; -- commits only the work in this procedure!!!

raise_application_error( -20016, p_msg );
end;
/

and your exception block would look like:

exception
when others then log_error( sqlerrm );
end;


See </code> http://asktom.oracle.com/~tkyte/autonomous/index.html <code>for info on autonomous transactions (and I have a bigger chapter on this in my book as well)


A reader, August 31, 2001 - 1:22 pm UTC

When a procedure failes it gives a big error message
including the line in the procedure where it failed .

begin
*
ERROR at line 1:
ORA-20016: Invalid number inserted in table
ORA-06512: at "ls.Proc", line 44
ORA-06512: at line 2


Is there a way to just show them the
ORA-20016: Invalid number inserted in table

which I raise as follows:

exception
when others then
if (SQLCODE = -1722) then
RAISE_APPLICATION_ERROR (-20016,'Invalid number inserted in table');
end if;
end;



Tom Kyte
August 31, 2001 - 1:50 pm UTC

The client would be responsible for doing that. For example, in sqlplus you could:

begin
ls.proc;
exception
when others then
rollback;
dbms_output.put_line( 'ERROR: ' || sqlerrm );
end;




While on the topic...

Andrew, August 31, 2001 - 4:37 pm UTC

Sometimes you want more error messages to get to the underlying cause. If so use "TRUE" as below.

SQL> declare
  2  x number;
  3  begin
  4  x := to_number('abc');
  5  exception
  6  when others then
  7  raise_application_error(-20501, 'Whoops - anonomous PL/SQL failed!', FALSE);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-20501: Whoops - anonomous PL/SQL failed!
ORA-06512: at line 7


SQL> declare
  2  x number;
  3  begin
  4  x := to_number('abc');
  5  exception
  6  when others then
  7  raise_application_error(-20501, 'Whoops - anonomous PL/SQL failed!', TRUE);
  8  end;
  9  /
declare
*
ERROR at line 1:
ORA-20501: Whoops - anonomous PL/SQL failed!
ORA-06512: at line 7
ORA-06502: PL/SQL: numeric or value error


SQL>  

explanation was good

srinivasa rao bachina, May 24, 2003 - 6:46 am UTC

Hi tom
one more doubt i am having regarding autonomous transactions
my requirement is
i am selecting a row with FOR UPDATE,and processing that row and creating some rows in the other tables,if any error comes during this process ,i have to update stauts field of the selected record (selected earlier with lock) then i will role back all the changes.for this i created a procedure to update the field ,as an autonomous transaction and giving the commit in that.if i start processing the row i am getting ora err -60.if i comment the FOR UPDATE clause it is working fine...
Can u Help me


Tom Kyte
May 24, 2003 - 10:25 am UTC

the proper way to do that is:



select for update the row
SAVEPOINT FOO;
process......
on error
ROLLBACK TO FOO;
update locked row
COMMIT;
on success
whatever



very good solution

srinivasa rao bachina, May 27, 2003 - 5:05 am UTC

so in that case there is no need of using atx(autonomous transaction,right?


Tom Kyte
May 27, 2003 - 7:45 am UTC

an autonomous transaction would be totally inappropriate here, yes.

Thank You for u r quick response

srinivasa rao bachina, May 27, 2003 - 8:14 am UTC

One more problem i am getting
select rows with update(table a)
process----->
in process: validating the records(tab a) if any error i am inserting into error_log table other wise i will process the records and create records (in b,c).i am having more than 10 validations.i may get that many erros all those i have to insert into error_log irrespective of the result of processing.so , i created the atx for creating the error_log transctions and given commit in that..

if i run first time i got 3 errors, first record is inserted into log but the 2,3 are not inserted and the error is agian ORA-00060: deadlock detected while waiting for resource.if i run second time 2 and 3 records are inserting and for first one same error is comming.

what is the problem with the method?
waiting for your Response

Tom Kyte
May 27, 2003 - 9:34 am UTC

why commit?

process should be:


open cursor;
loop
fetch bulk collect 100 records
for i in 1 .. fetched
loop
if validated move into valid array
else move into error array;
end loop
forall i in 1 .. valid array count insert into t1;
forall i in 1 .. error array count insert into t2;
end loop;
commit;


I don't see the point or logic in commiting error records independent. If you have a serious error that prevents you from getting to the place where you SHOULD commit -- you'll reprocess those records again later anyway.





A reader, June 18, 2003 - 10:47 pm UTC

Hi tom,
I wanted to know, if I create a stored procedure with only one select statement, then executing a stored procedure or executing that select statement in SQL*PLUS is more useful.

Tom Kyte
June 19, 2003 - 7:56 am UTC

i don't know, "is more useful" then what?

Used this method, but another problem

Riyaz, June 20, 2003 - 9:41 am UTC

I used log_error procedure in my application to trap the error.(added one more column for storing option name). But now i have another 2 problems.

1. Whenever the user (seesion) gets this error, it is going into log table. Hence log table is getting accessed all the time, the transaction is taking MORE time to savd. How to resolve this problem?

2. I am using GUI application, I want to show full text message for the oracle thrown errors, hence what logic I can use to fetch the error from log table and show it to the user from Front end application (ex VB). (If I am throwing the exception, truncated messages shown - full message not shown)
(meaning that, if i have trigger a, that calls proc b, proc b calls proc b1. In this case, if proc b1 throws exception, proc-b throws, then finally trigger a throws full error msg, which is "truncated one", ie "not full", hence difficult to fix the error by reading half message).

Now suggest me the good method to resolve the above issues.



Tom Kyte
June 20, 2003 - 5:13 pm UTC

1) i don't know, i don't even know what that problem is...

2) i'm not a vb programmer, I don't have the docs, but I would look in there and see what apis they have. We are not truncating the error message, they are.

ATX or not in logging error

Henry, June 20, 2003 - 11:52 am UTC

Tom, you said:

> I don't see the point or logic in commiting error records independent. If you
> have a serious error that prevents you from getting to the place where you
> SHOULD commit -- you'll reprocess those records again later anyway.

Assume that the original intention is to log any validation
failure no matter whether subsequent processing succeeds or
not, which makes sense if the business requirements want to
log the failed attempts on some particular operations.

With the solution you gave, if the processing of one record
fails, no prior validation failures will be logged.
Seems to me ATX would be needed. Actually I remember you
used error logging as example for ATX.

So please explain why you think ATX is not appriopriate
for this. Is it just an overkill or simply incorrect?

Thanks!




Tom Kyte
June 20, 2003 - 5:32 pm UTC

In a data load, I frankly do not see the point -- no. You'll simply reprocess those later.

the example I gave was auditing that cannot be rolled back for example -- in a TRIGGER audit that someone tried to insert "such and such data" -- commit that and then raise an error that causes the insert to "un-happen".

But for logging validation records during a data load? nope, I don't see the value or benefit or need.

Yes. It is a limitation in the GUI application

Riyaz, June 21, 2003 - 8:08 am UTC


Yes Tom. You are right. The limitation is in "Front End" application. Because of that only, errors are getting truncaated.



Is there a better way?

Thaha Hussain, October 13, 2003 - 3:06 am UTC

Dear Tom,

Please see this:-

CREATE OR REPLACE FUNCTION -----
----------------------
-------------------------
------------

SELECT COUNT(*) INTO v_count FROM Pre_requisites WHERE
for_subject_id = v_subject_id;
IF (v_count= 0) THEN
RETURN 1;
END IF;

----------------
-----------------

Can we Return 1 without using a variable(v_count)? I mean the condition in IF statement itself ;-)

Or in other words,
IF (there is now row satisfying the condition) THEN ---> RETURN 1

Regards,
Thaha Hussain



Tom Kyte
October 13, 2003 - 7:27 am UTC


why are you doing this ???? why why why?

I hate the "see if a row exists and then do something" logic. what a waste of cpu cycles.

so so so much better to "do something and if nothing exists upon which to do something, then return"

So, there is a better way. don't do it in the first place.




which is best way, then??

Ashiq Shamsudeen A, October 13, 2003 - 10:30 am UTC

Tom,

Your quote " do something and if nothing exists upon which to do something, then return"

There some conditions checks ,we've to do before returning some values or inserting into a tables.

In this case,what is the best way you're following.











Tom Kyte
October 13, 2003 - 12:00 pm UTC

in that case -- i'm not following what you are saying.

More to Explore

PL/SQL demos

Check out more PL/SQL tutorials on our LiveSQL tool.

PL/SQL docs

PL/SQL reference manual from the Oracle documentation library