I beg to differ
Sam, November 10, 2001 - 6:34 pm UTC
"I would have them pass in the name of the table "
The chaces are typo are very great when developers try to type the full table names.
I differ with you. Actually only 2 to 3 developers use this procedure , and they very well know what the abbr's stand for. They find it very convenient to enter the abbr's, not the full name.
I accept with you totally with regard to the exception section.
November 10, 2001 - 6:47 pm UTC
If the developers cannot write a script -- get new developers. Geez, if your table names are so hard, you need new tablenames. It would seem to me that I would WANT to know exactly what table was getting truncated. This abbrev thing sounds like an accident just waiting to happen (Oh -- I though AAA was table_two, not table_one -- can the DBA do a recovery for me). Two or three developers today -- two or three NEW developers tomorrow. People come, people go -- just seems that explicit names would be "safer" -- if they don't like typing, they can create a script or even just create a procedure "trunc_aaa" so they can type even less.
anyway, let them do the abbrev then if you really wan. turn the validation into:
select decode( p_tname, 'AAA', 'FIRST_TABLE',
'BBB', 'SECOND_TABLE',
'CCC', 'THIRD_TABLE',
NULL ) into l_tname
from dual;
if ( l_tname is null )
then
raise_application_error( -20001, 'sorry '||p_tname||' is not valid...' );
end if;
Nirmal, November 11, 2001 - 1:06 am UTC
It's really good, usually i too many times "Hide" the errors while doing programs, later i'd suffered a lot to debug it.
I'm sure, now onwards i'll handle the exceptions properly.
Thanks.
Helpful discussion of exception handling
a DBA, January 11, 2002 - 5:15 pm UTC
question about your example
David, March 11, 2003 - 3:13 pm UTC
You said that:
begin
select x into y from t where ...;
exception
when no_data_found then y := default_for_y;
end;
is a good exception block because it handles the error. I agree that it handles the error. y gets set to a default. But doesn't processing stop at that point?
Suppose the proc looked like this:
begin
select x into y from t where ...;
insert into t1 values (y);
exception
when no_data_found then y := default_for_y;
end;
if the where clause is false, the default doesn't get inserted. right? in my testing it does not.
if i execute:
create or replace procedure test_tom_proc as
p integer;
begin
select 1 into p from dual where 1=0;
insert into test_tom values (p);
commit;
exception
when no_data_found then
p:=-1;
end;
/
nothing happens.
What if you wanted this to insert a -1 into the table? The only way I've found to do it is like this:
create or replace procedure test_tom_proc as
p integer;
f integer;
begin
f := 0;
select count(*) into f from dual where <clause>;
if f <> 0 then
select 1 into p from dual where <same_clause>;
else
p := -1;
end if;
insert into test_tom values (p);
commit;
end;
/
There's nothing wrong with this last approach, as far as I can tell, but unless I'm missing something (and that's likely) your block handles the exception, but cannot do anything with the default value.
March 11, 2003 - 3:29 pm UTC
processing doesn't "stop", it jumps.
If the code was:
begin
select x into y from t where ...;
insert into t1 values (y);
exception
when no_data_found then y := default_for_y;
end;
but you wanted the insert to happen regardless -- you made a coding error -- the exception block covered too much ground and should have been:
begin
begin
select x into y from t where ...;
exception
when no_data_found then y := default_for_y;
end;
insert into t1 values (y);
end;
so you have a programming bug, not an issue with exceptions. you just covered too much ground is all. move the exception block to cover the statement(s) you are EXPECTING an exceptional condition, that is all.
stop counting rows -- that stinks, it is really really slow and doesn't even assure you of success!
Now I get it
David, March 11, 2003 - 3:41 pm UTC
I see what you mean. I have to admit to a fundamental misunderstanding of the options concerning placement of exception blocks.
Thanks for your help.
More than 1 exception types
Tatiane, April 03, 2003 - 2:55 pm UTC
In a PL/SQL block, I can have:
insert into tbl (id, descr, quote, ... ) values (10, 'Some description', 6354, ... );
id is my pk
descr is an uk
quote is an uk
How can I treat the exception clause in order to know if the violation was in id, descr or quote, since the three of them yield the dup_val_on_index exception ?
Further, if there are other dml statements in the code, how do I know in the exception clause from which one (which line) I get the dup_val_on_index exception ?
Thank you for your services.
April 03, 2003 - 3:04 pm UTC
you would parse the error message in the dup_val_on_index handler.
if you want to know the exact statment, you would wrap each statment in an exception block. Exceptions are raised at the block level, not a line, not a statement.
begin
begin
insert into ..
exception when ....
end;
begin
insert into ..
exception when ....
end;
begin
insert into ..
exception when ....
end;