A reader, August 31, 2001 - 10:48 am UTC
Tom
So where should I put the commit. At the end, above the transaction?
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
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;
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
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?
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
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.
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.
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!
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
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.
October 13, 2003 - 12:00 pm UTC
in that case -- i'm not following what you are saying.