Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question, Juan Carlos.

Asked: February 17, 2009 - 11:24 am UTC

Last updated: April 30, 2012 - 8:10 am UTC

Version: 10.2.3

Viewed 10K+ times! This question is

You Asked

Hi Tom,
If you want to add several columns to a table you must do a create as select, but this is'nt so easy, you have several steps to do:

1) Recreate foreing constraints referencing constraints in the current table
2) recreate current table constraints
3) regrant privileges
4) recreate indexes
5) in the command create as select you must specify casts, so the new table has the same column specifications
CREATE TABLE SOA.HICARTERA_RO AS
SELECT HCA_CLASE_OPERACION,
CAST (NULL AS VARCHAR2(60)) HCA_CODCAREMP,

6) and finally modify to reset the default values
for example
ALTER TABLE SOA.HICARTERA_RO
MODIFY (
HCA_CUSTEO DEFAULT 0,

The question is if you have a trick, a complete script to do all this work, or an advice.

Thanks :)

and Tom said...

... f you want to add several columns to a table you must do a create as select, ...

no you don't. You just alter and add them. I would never consider using a create table as select for this operation.


Or, if you want to ad them with defaulted values in 10g and before (11g has a fast add column, with or without defaults), you can use dbms_redefinition and it'll copy over the constraints and everything for the newly reorganized table.

Rating

  (24 ratings)

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

Comments

Juan Carlos Reyes, February 17, 2009 - 11:55 am UTC

Thanks Tom
I must fix
If you want to add several columns in a table having a few millions of records, and you have oracle Standard Edition.
:)
Tom Kyte
February 17, 2009 - 2:26 pm UTC

then realize that SE has *no* online administrative features and alter table T add column is what you are going to do.

Still cannot imagine using create table as select - why would you consider that?

A reader, February 17, 2009 - 5:25 pm UTC

It takes too much time because this table has several millions of records, at least on 10g.

ALTER TABLE SOA.HIVENTA_PACTO_RW
ADD (
HVP_MONEDA VARCHAR2 (3)
)
/
UPDATE SOA.HIVENTA_PACTO_RW SET
HVP_MONEDA = 'x';


Previously we had similar situations and ctas solved this situations, including row migration

I don't understand why you say about ctas

>Still cannot imagine using create table as select -
>why would you consider that?

Thanks
Tom Kyte
February 17, 2009 - 8:23 pm UTC

if you need online capabilities like that, that is what enterprise edition is about. It has the capabilities you are requesting.

Juan Carlos Reyes, February 18, 2009 - 8:34 am UTC

I did one for me maybe this can be helpful to some one

CREATE OR REPLACE FUNCTION SYS.CTAS_GENERATION( cOwner VARCHAR2, cTable VARCHAR2) RETURN CLOB
AUTHID DEFINER IS
nCount NUMBER;
cTableTemp VARCHAR2(100) := 'TEMP_'||cTable;
cCaracteres VARCHAR2(3);
cCast VARCHAR2(100);
eError EXCEPTION;
cColumna VARCHAR2(100);
cReturn CLOB;
cReturnPostCreate CLOB;
cReturnDefault CLOB;
BEGIN


FOR A IN (
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||CONSTRAINT_NAME||';' CONS
FROM DBA_CONSTRAINTS
WHERE R_CONSTRAINT_NAME
IN (SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE OWNER = cOwner AND TABLE_NAME = cTable )
) LOOP
cReturn := cReturn || A.CONS||CHR(10);
END LOOP;
cReturn := cReturn || CHR(10);


FOR A IN (
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||CONSTRAINT_NAME||';' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || A.CONS||CHR(10);
END LOOP;


FOR A IN (SELECT 'DROP TRIGGER '||TABLE_OWNER||'.'||TRIGGER_NAME||';' TRIG
FROM DBA_TRIGGERS WHERE TABLE_OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || A.TRIG ||CHR(10)||CHR(10);
END LOOP;

FOR A IN (SELECT REPLACE(TRIM(DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',cTable,cOwner)),
CHR(10)||' '||CHR(10),';'||CHR(10) ) PRIV FROM DUAL) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.PRIV) ||CHR(10);
END LOOP;
cReturnPostCreate := cReturnPostCreate || '/' ||CHR(10);

FOR A IN (
SELECT 'DROP INDEX '||OWNER||'.'||INDEX_NAME||';' IDX
FROM DBA_INDEXES WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || TRIM(A.IDX) ||CHR(10);
END LOOP;

FOR A IN (
SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)||'/' IDX
FROM DBA_INDEXES WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.IDX) ||CHR(10);
END LOOP;

BEGIN
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable AND NOT CONSTRAINT_TYPE = 'R'
) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;
EXCEPTION WHEN OTHERS THEN
cReturnPostCreate := cReturnPostCreate || SQLERRM;
END;
cReturnPostCreate := cReturnPostCreate || CHR(10);

-- Constraints de otras tablas a esta tabla
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME
IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = cTable AND OWNER = cOwner)
ORDER BY OWNER,TABLE_NAME) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;



BEGIN
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable AND CONSTRAINT_TYPE = 'R'
) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;
EXCEPTION WHEN OTHERS THEN
cReturnPostCreate := cReturnPostCreate || SQLERRM;
END;
cReturnPostCreate := cReturnPostCreate || CHR(10);


cReturn := cReturn || 'ALTER TABLE '||cOwner||'.'||cTable||' RENAME TO '||cTableTemp||';'||CHR(10);

cReturn := cReturn || 'CREATE TABLE '||cOwner||'.'||cTable||CHR(10)||' AS '||CHR(10);
cReturn := cReturn || 'SELECT ';


FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP



IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cCast := NULL;
IF r.DATA_TYPE='VARCHAR2' THEN
cCast := 'CAST ('||r.COLUMN_NAME||' AS VARCHAR2('||r.DATA_LENGTH||')) ';
ELSIF r.DATA_TYPE='NUMBER' THEN
cCast := 'CAST ('||r.COLUMN_NAME||' AS NUMBER('||r.DATA_PRECISION||','||r.DATA_SCALE||')) ';
END IF;
cColumna := cCast||r.COLUMN_NAME;

IF r.DATA_DEFAULT IS NOT NULL THEN
cReturnDefault := cReturnDefault || r.COLUMN_NAME|| ' DEFAULT '|| R.DATA_DEFAULT || ',';
END IF;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTableTemp ||';'||CHR(10);
cReturn := cReturn ||CHR(10);

FOR A IN (SELECT REPLACE ( DBMS_METADATA.GET_DDL('TRIGGER',TRIGGER_NAME,OWNER)||'/',
'ALTER TRIGGER ', '/'||CHR(10)||CHR(10)||'ALTER TRIGGER') TRIG
FROM DBA_TRIGGERS WHERE TABLE_OWNER=cOwner AND TABLE_NAME = cTable) LOOP
cReturn := cReturn || A.TRIG ||CHR(10)||CHR(10);
END LOOP;



cReturn := cReturn ||CHR(10);
cReturn := cReturn ||cReturnPostCreate;
cReturn := cReturn ||CHR(10);

IF cReturnDefault IS NOT NULL THEN
cReturnDefault := SUBSTR(cReturnDefault,1,LENGTH(cReturnDefault)-1);
cReturnDefault := 'ALTER TABLE '||cOwner||'.'||cTable||' MODIFY ('||
cReturnDefault||')'||CHR(10)||'/';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||cReturnDefault;
cReturn := cReturn ||CHR(10);

END IF;

cReturn := cReturn ||'--Before drop temporary tables, do comparations and check objects';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'--DROP TABLE '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'------------------------ ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'-- Validations ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'SELECT '''||cTable||''',COUNT(*) FROM '||cOwner||'.'||cTable ;
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'UNION ALL';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'SELECT '''||cTableTemp||''',COUNT(*) FROM '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'------------------------ ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'--DROP TABLE '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);


cReturn := cReturn || 'SELECT ';
cColumna := NULL;
FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP

IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cColumna := r.COLUMN_NAME;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTableTemp ||''||CHR(10);
cReturn := cReturn ||CHR(10);
cReturn := cReturn || 'MINUS'||CHR(10);
cReturn := cReturn ||CHR(10);
cReturn := cReturn || 'SELECT ';


cColumna := NULL;
FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP

IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cColumna := r.COLUMN_NAME;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTable ||';'||CHR(10);
cReturn := cReturn ||CHR(10);



RETURN cReturn ;
EXCEPTION
WHEN OTHERS THEN
RETURN cReturn||CHR(10)||CHR(10)||SQLERRM ;
END;
Tom Kyte
February 18, 2009 - 3:41 pm UTC

EXCEPTION
   WHEN OTHERS THEN
    RETURN cReturn||CHR(10)||CHR(10)||SQLERRM ;
END;



I hate your code.

http://asktom.oracle.com/pls/ask/search?p_string=%22i+hate+your+code%22

A reader, February 18, 2009 - 5:25 pm UTC

I followed your link, but I didn't find the specific, could you giveme a suggestions, thanks :)
Tom Kyte
February 18, 2009 - 5:51 pm UTC

Juan Carlos - you must have seen me write a billion times:

when others, not followed by raise or raise_application_error, is almost certainly a bug in your developed code.


Why, why why would you have a when others like that? That is *the single worst coding practice I see over and over*.


It makes me want to remove when others from the language specification itself.

I hate it so much, I refuse to even look at code that has that construct in it anymore, I just erase it - it leads me to believe (to know) the code above the when others is likely not safe at all, because if you make such a basic mistake....



just follow that link....
click on the articles it finds...
ctl-f for

i hate your code


and read.

most useful !

A reader, February 19, 2009 - 7:45 am UTC


Juan Carlos Reyes, February 19, 2009 - 8:16 am UTC

You are right tom there should be an raise, but this is a function for internal use only.
I call in this way
SELECT daz.DB_UTL_GENERA_CTAS( 'SOA', 'HIVENTA_PACTO_RO') FROM DUAL
So I execute and check at the end if it generate without error, if I would include a raise, there wouldn't be a return.
Tom Kyte
February 19, 2009 - 8:57 am UTC

but why have the when others there at all


Here is my take on this - if something is a bad practice, if something shouldn't be done, we just shouldn't do it.

This function should just FAIL if it fails, it would then be obvious "hey, I FAILED".

There is no good reason to have it (the when others)
There are only a slew of reasons NOT to have it.

Others might read your code and say "ok, I see Juan Carlos does this - this must be the right way to code, we should do it too"

A reader, February 19, 2009 - 6:41 pm UTC

Hi Tom,
After repeating this is a code for my personal use,

This is the difference

--USING RAISE

    RETURN cReturn ;
EXCEPTION
   WHEN OTHERS THEN
    RAISE eErrorVersion;
    RETURN cReturn||CHR(10)||CHR(10)||SQLERRM ;
END;

SQL> SELECT sys.DB_UTL_GENERA_CTAS( 'SOA', 'HIVENTA_PACTO_RO') from dual;
ERROR:
ORA-06510: PL/SQL: excepci¾n definida por el usuario no tratada
ORA-06512: en "SYS.DB_UTL_GENERA_CTAS", lÝnea 246
ORA-01476: el divisor es igual a cero

I know there is an error but I have to debug.

-- NOT USING RAISE


SQL> SELECT sys.DB_UTL_GENERA_CTAS( 'SOA', 'HIVENTA_PACTO_RO') cod from dual;

/*IMPORTANTE a·n cuando este script ya ha sido probado,
  debe revisarse que las estructuras y datos sean iguales antes y despuÚs.*/

** ESTA ES UNA VERSION CREATE TABLE AS Y *REQUIERE UN FULL BACKUP* / *NO REQUIERE FULL BACKUP*;



ORA-01476: el divisor es igual a cero


SQL> SELECT sys.DB_UTL_GENERA_CTAS( 'SOA', 'HIVENTA_PACTO_RO') cod from dual;

/*IMPORTANTE a·n cuando este script ya ha sido probado,
  debe revisarse que las estructuras y datos sean iguales antes y despuÚs.*/

** ESTA ES UNA VERSION CREATE TABLE AS Y *REQUIERE UN FULL BACKUP* / *NO REQUIERE FULL BACKUP*;

ALTER TABLE SOA.HIVENTA_PACTO_RO DROP CONSTRAINT CST_HVP_FECHA_CODCAR_NROOPVEN;
ALTER TABLE SOA.HIVENTA_PACTO_RO DROP CONSTRAINT CST_VEP_HVP_FECODCA;


ORA-01476: el divisor es igual a cero



I know there is an error an I know where :)

Tom Kyte
February 21, 2009 - 8:37 pm UTC

if something fails, it fails... period, plain and simple - all you did with that horrible WHEN OTHERS is make it HARDER TO DEBUG.


You moved the error line number - you now have to figure out "what line of code might have caused this"

You have done *nothing* good with it.
You have made it harder to debug.
It is the worst practice possible.



If it is for your personal use, do not post it on internet sites then - you are publishing it and saying "THIS IS GOOD STUFF, I AM PROUD OF IT, IT SHOWS THE BEST I CAN DO" -

but I hate it, that when others - horrible, never good the way you've done it, does not belong there, there is no GOOD reason to have it (there are NO positives to be gained), there are only a host of good reasons to NOT HAVE IT.

Juan Carlos Reyes, February 25, 2009 - 11:37 am UTC

Ok Tom, could you show me how would you do please.

Tom Kyte
February 25, 2009 - 6:10 pm UTC

I already told you

the exception block should be erased, it does not provide any value whatsoever, it DETRACTS, SUBTRACTS value.

just delete it and you've added value to your code.

...
    RETURN cReturn ;
<b>/*</b>
EXCEPTION
   WHEN OTHERS THEN
    RAISE eErrorVersion;
    RETURN cReturn||CHR(10)||CHR(10)||SQLERRM ;
<b>*/</b>
END;
......



tell me, of the following which one returns more "useful" diagnostic information?

ops$tkyte%ORA10GR2> create or replace function function_to_be_called_from_sql
  2  return number
  3  as
  4          x number;
  5  begin
  6          for i in 1 .. 10
  7          loop
  8                  x := ln(i);
  9          end loop;
 10          x := x/0;
 11          for j in 1 .. 10
 12          loop
 13                  x := x/j;
 14          end loop;
 15          return x;
 16  exception
 17          when others then raise;
 18  end;
 19  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select function_to_be_called_from_sql from dual;
select function_to_be_called_from_sql from dual
       *
ERROR at line 1:
ORA-01476: divisor is equal to zero<b>
ORA-06512: at "OPS$TKYTE.FUNCTION_TO_BE_CALLED_FROM_SQL", line 17
</b>

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> create or replace function function_to_be_called_from_sql
  2  return number
  3  as
  4          x number;
  5  begin
  6          for i in 1 .. 10
  7          loop
  8                  x := ln(i);
  9          end loop;
 10          x := x/0;
 11          for j in 1 .. 10
 12          loop
 13                  x := x/j;
 14          end loop;
 15          return x;
 16  exception
 17          when no_data_found then raise program_error;
 18  end;
 19  /

Function created.

ops$tkyte%ORA10GR2> select function_to_be_called_from_sql from dual;
select function_to_be_called_from_sql from dual
       *
ERROR at line 1:
ORA-01476: divisor is equal to zero<b>
ORA-06512: at "OPS$TKYTE.FUNCTION_TO_BE_CALLED_FROM_SQL", line 10
</b>


Juan Carlos Reyes, February 26, 2009 - 8:44 am UTC

Thanks Tom, I understand your position, I'm continuing the conversation, I'm not saying what you say is wrong.

Imagine you have several complex function some of them more than 10,000 characters each, and that functions are called frequently from other functions.

Is easy an safe to use when others for me, if you would have an "ON-ERROR" trigger so you could create a common procedure for all blocks, then it could be a good idea to san when no_data_found when... when... etc.
But you can't copy all that specific treatment in each block of code is unreal.

The benefit for me of using when others is to guarantee if there is any error, then it will be capture and the process will be stopped in that point and don't jump to the next block, I can capture it for example when calling a funcion and say "error in function A" and not leave that error move away through many blocks of code and executing code i don't want to be executed, because another programmer can call it and I see realiable instead of hoping some calling blocks treat it adequately.

I'm talking about this

WHEN OTHERS
RETURN 'F-'||cret||SQLERRM


if the return of a function beggings with 'F-', then the process is stoped, and i get the sqlerrm, and aditionally other information showing the position or value of a variable

For me is more reliable to stop the process when there is an error, than leave to luck hoping it stop in a calling block which treat that error correctly.

What do you think?
Tom Kyte
March 03, 2009 - 7:51 am UTC

this is the worst possible practice, it is called swallowing an exception, which is as distasteful as it sounds.

WHY DO YOU WANT TO CATCH AN ERROR YOU CANNOT DEAL WITH, THE CODE HAS FAILED - IN A MANNER THAT IS NOT CORRECTABLE - LET IT CONTINUE FAILING ALL OF THE WAY UP THE STACK - TO MAKE IT OBVIOUS, PAINFULLY UTTERLY, COMPLETELY OBVIOUS THAT THE CODE MESSED UP.


This is even more true for 10's of thousands of complex functions - just let them FAIL WHEN THEY FAIL.

this is a basic mistake (swallowing errors) made be people fresh out of college - they are afraid to let errors propagate.


... if the return of a function beggings with 'F-', then the process is stoped, and
i get the sqlerrm, and aditionally other information showing the position or
value of a variable ...


gah, ARG, blah. Think about this - if you get an error you know

a) the process is stopped and is UNWINDING SAFELY, in a transactional manner
b) you get sqlerrm (duh, you just proved you can, you are getting it!!!)
c) you get the proper SQLCODE
d) you get the error stack that shows the entire progression of the error!!!!! A called B called C called D - D died on line 27.


Your approach

a) you change the way transactions work (bad bad bad). You turned the error INTO NOT AN ERROR, YOU MADE SUCCESS OUT OF FAILURE - and that is not a good thing.

b) you hide part of the sqlerrm (just part of it) in a string, big deal. whoopee - you made it harder to interpret

c) you lost the sqlcode

d) you lost the error stack, entirely, you just threw out the most valuable bit of information anywhere.

google for


swallow exceptions



and start reading, here is something I wrote on this recently as well:


....
here has been no misunderstanding. What I was relating is well known in all programming languages that throw exceptions. Developers have a very bad habit of catching any and all exceptions and hiding them. It is written about a lot:

http://gen5.info/q/2008/07/31/stop-catching-exceptions/
is a good example of what I'm talking about..

https://www.oracle.com/technetwork/issue-archive/2007/07-jul/o47asktom-092692.html
Is one I wrote.

Please read and feel free to pass along the link. WHEN OTHERS - not followed by a raise or raise_application_error - is almost certainly a bug in the developed code. There is exactly one place it might be useful - and that would be the top level caller (as I mentioned), the top level invocation of the procedure block. For example, if you are coding in APEX, it might make sense to have the top level procedure have a when others - that logs the error and returns to the end user a nice looking error page (in this case, the top level client is PLSQL).

If it were a java application, then the plsql should contain NO when others not followed by a raise or raise_application_error, the top level in this case is the java application and it has a definite need to know there was an error that could not be handled.

Do you know that by putting when others then <NO raise/raise application error> - the coder would break the Atomic part of the ACID principles - the stored procedure will allow some of it's work - what it did up to the exception - but not the bits that were skipped to remain in the database - not be transparently rolled back?

The reason for this new feature and what it looks like to the developer follows:

ops$tkyte%ORA11GR1> create table t( x varchar2(4000) );

Table created.

ops$tkyte%ORA11GR1> create or replace
  2  procedure maintain_t
  3  ( p_str in varchar2 )
  4  as
  5  begin
  6    insert into t
  7    ( x ) values
  8    ( p_str );
  9  exception
 10    when others
 11    then
 12      -- call some log_error() routine
 13      null;
 14  end;
 15  /

Procedure created.


There we have a procedure, it uses when others to capture the error into a logging table and continues on.

ops$tkyte%ORA11GR1> exec maintain_t( rpad( 'x', 4001, 'x' ) );

PL/SQL procedure successfully completed.


Someone invokes it, it sure appears to work right? Everything is good... or is it?

ops$tkyte%ORA11GR1> select * from t;

no rows selected

Hmm, that table is empty, that is not good - what happened?
ops$tkyte%ORA11GR1> alter procedure maintain_t compile
  2  PLSQL_Warnings = 'enable:all'
  3  reuse settings
  4  /

SP2-0805: Procedure altered with compilation warnings

ops$tkyte%ORA11GR1> show errors procedure maintain_t
Errors for PROCEDURE MAINTAIN_T:

LINE/COL ERROR
-------- -----------------------------------------------------------------
9/8      PLW-06009: procedure "MAINTAIN_T" OTHERS handler does not end in
         RAISE or RAISE_APPLICATION_ERROR

Ahh, it was the dreaded when others not followed by a raise or raise application error. And it is reported as a warning - just like java reports bad programming practices and C does and C++ and Ada and so on. The developer is free to ignore these warnings, at their own peril of course.

Beware of turning exceptions into return values!!!

Michael a. Istinger, February 27, 2009 - 3:46 am UTC

Juan Carlos,

please forgive my butting in here.

To turn a exception into a return value is an extremely bad idea because it utterly completely changes the semantics of
the code!

You see, when a piece of PL/SQL code is exited via an exception, Oracle will *automagically* undo any changes done
by the code.

To illustrate what I am saying here, you might think of
Oracle handling a call to my_proc like this:

BEGIN
SAVEPOINT my_savepoint;
my_proc;
EXCEPTION
WHEN OTHERS THEN
ROLLBACK to my_savepoint;
RAISE;
END;

When your procedure exits via an exception, any changes the procedure did to the database will be rolled back. In other words, the database will be in the exact same state as it was before your code was executed.

When you exit with a return code, this will not happen.
Any changes your code did to the database before the exception was raised will be retained, which means you
will leave the database in an inconsistent state.

If you want to check this behaviour out, here's an
example:

create table test_exceptions(
f1 number(5)
, app varchar2(40)
, info varchar2(40)
);

CREATE OR REPLACE PROCEDURE Exception_demo AS
BEGIN
INSERT INTO test_exceptions(f1, app, info)
VALUES(1, 'Konsistenz-Exceptions', 'insert #1');
raise zero_divide;
INSERT INTO test_exceptions(f1, app, info)
VALUES(1, 'Konsistenz-Exceptions', 'insert #2');
END Exception_demo;
/

CREATE OR REPLACE FUNCTION return_value_demo RETURN NUMBER AS
BEGIN
INSERT INTO test_exceptions(f1, app, info)
VALUES(1, 'Konsistenz-Exceptions', 'insert #1');
raise zero_divide;
INSERT INTO test_exceptions(f1, app, info)
VALUES(1, 'Konsistenz-Exceptions', 'insert #2');
return 0;
EXCEPTION
WHEN OTHERS THEN
return SQLCODE;
END return_value_demo;
/

Let me repeat here that the function return_value_demo is
terrible, not to be copied or used, will be hated by Tom, etc.... This is just to illustrate why using a return value is a bad idea!

The procedure Exceptions_demo will not leave any record in
table test_exceptions, the function return_value_demo will!

As your code is probably much more complex than those two
examples, imagine what a mess they might leave in your database when you turn exceptions into return values.

This idea is real scary, please reconsider!!!!

All the best
Michael
the

Juan Carlos Reyes, February 27, 2009 - 11:12 am UTC

Thanks Michael, now I'm understanding

The fact you don't have the raise in the function doesn't means there aren't a raise.

This is the exapmle

-- database
funcion test
...
exceptoin when others
return 'f'||sqlerrm;
end;

--application

cret=test;
if cret like 'f%' then
stop_message( substr(cret,2,100));
rollback;
exit_form;
end if;

Obviously the error must be stoped and a rollback back must be done.

I agree is horrifying not doind a rollback or doing a exception then others null; end;

But use when others return sqlerrm; is ok, meanwhile you treat the error in other place. In the example of the function I'm not modifying the database, so I don't need a rollback, 2nd it helps me to find specifically where the error had been generated. 3rd this is for me, if this will be for an apllication for a customer I will have to do another implementation.

Anyway if Tom don't like it, finally this is his site, so I'll respect that and I'll don't do again.

;)
Tom Kyte
March 03, 2009 - 9:49 am UTC

... I agree is horrifying not doind a rollback or doing a exception then others
null; end;
...

you are missing the point entirely. I also wish PLSQL could NOT rollback NOR commit (I wish we could remove that ability from the language). Your stored procedure should NOT

a) swallow exceptions
b) change the transactional flow

period, you hit a grievous error - JUST LET IT PROPAGATE TO THE CLIENT AND LET THE CLIENT FIGURE OUT WHAT IT WANTS TO DO.

It might not understand that you rolled back or whatever, you mess everything up, you break the assumptions.


... 2nd it helps me to find specifically where the error
had been generated. ...

and I'll say one more time - by catching the exception - all you have done is REMOVED THE ACTUAL LINE NUMBER. You say "I can find the error more easily by catching it" - but the opposite is very very very much the case!!!!. You hide information using this "newbie programmer" technique.


Unless you are self employed and not under contract, you are writing NOTHING for yourself, everything you do will be reused later by some other poor unfortunate individual.

If you don't think so, read this:

http://thedailywtf.com/Articles/The-Apocalypse-Must-Have-Occured!.aspx


Things morph, your code will be used by someone else later. It is NOT YOURS.


And funny, I just read this this morning:

http://blog.damianbrady.com.au/2009/03/02/things-i-hate-seeing-in-legacy-code/

5. Error handling that hides errors

This is pretty common, and unfortunately it¿s really only something that gets noticed when you have a problem. While things are working perfectly (and you don¿t care how it works), there¿s no problem. As soon as there¿s an error though, you can¿t find any details about it because the error handling is crap.


Juan Carlos Reyes, March 02, 2009 - 6:14 pm UTC

Hi Tom, :)

Sorry, but I don't understand your advise, if you have a large function, and you don't know if one day this is going to give a uncommond error, for example divided by 0, incorrect column width, I think is better to use when others; otherwise could you clarify how do you manage it, do you do an extensive analysis of all possible errors and include one by one all the posibilities?
What do you do when an error you never imagine is raised, for example a trigger error, having a RAISE_APPLICATION_ERROR( -20002, 'Trigger TGB_INS_UP_USR_PERCI' );, you can't planify it.


I honestly don't understand, thanks.

Thank you
Tom Kyte
March 03, 2009 - 4:17 pm UTC

why - why do you incorrectly think that.


Unexpected errors are JUST THAT, unexpected, not expected, not anticipated - NOTHING you can do to fix them.

You have to (I'm begging you) you HAVE to allow them to propagate to the client, so the client is made aware UNAMBIGUOUSLY "something really bad happened, we are dead, best to stop"


You do NOT do an extensive analysis of all errors, that is the point, you just let it propagate out to the client, let the client tell the end user "so sorry, something really bad just happened, the database is left in the state it was in before this really bad thing happened - but it wasn't expected so we sort of have to stop - we cannot do what you just asked"

You are 100% right - you cannot plan for it - so what are you doing in your "when others" - I mean - if you caught it, you must know how to deal with it.


What is this FASCINATION with catching all exceptions????????? They are errors, just let them go up to the client and the client will let the user know.


You know what, if you have a zero divide - that is the equivalent of a blue screen of death, you are doing something so wrong, you crashed, you died, game over, stop kidding yourself, you are finished - time to exit basically.



Please google

swallow exceptions


read some of the articles, this is important.


It is time to STOP "when others", just stop - the only valid use of when others is


begin
   ....
exception
   when others
   then
        log_error_using_procedure_that_uses_autonomous_transaction();
        RAISE;
end;



and the CLIENT submits that block (that is YOUR CODE does not use when others, ONLY THE CLIENT does - at the top level, it can log that a called b called c called d and d done DIED a horrible death).


Clarification on Raise / Raise Application Error

Stephan, March 03, 2009 - 10:32 am UTC

Hi Tom,

Excellent reading, as usual. A clarification please, though: repeatedly I've seen you say: "WHEN OTHERS - not followed by a raise or raise_application_error - is almost certainly a bug in the developed code".

But scrolling up a bit, you provided the following example:
ops$tkyte%ORA10GR2> create or replace function function_to_be_called_from_sql
  2  return number
  3  as
  4          x number;
  5  begin
  6          for i in 1 .. 10
  7          loop
  8                  x := ln(i);
  9          end loop;
 10          x := x/0;
 11          for j in 1 .. 10
 12          loop
 13                  x := x/j;
 14          end loop;
 15          return x;
 16  exception
 17          when others then raise;
 18  end;
 19  /

Function created.

ops$tkyte%ORA10GR2>
ops$tkyte%ORA10GR2> select function_to_be_called_from_sql from dual;
select function_to_be_called_from_sql from dual
       *
ERROR at line 1:
ORA-01476: divisor is equal to zero
ORA-06512: at "OPS$TKYTE.FUNCTION_TO_BE_CALLED_FROM_SQL", line 17


Now to me, all the RAISE call did was change the line of the exception to something not helpful.

Is it time to change your advice? After WHEN OTHERS, while RAISE is better than not using RAISE, it surely mucks up troubleshooting...
Tom Kyte
March 03, 2009 - 8:59 pm UTC

ummm, no - it is not (time to change the advice)


did you see the words: "tell me, of the following which one returns more "useful" diagnostic information? "

right before that example?

I presented that as "not a good idea", followed by "a good idea", I was trying to make the point that just having a when others there - MOVES THE ERROR NUMBER - making it *harder* to debug, not *easier* (as Juan Carlos was putting forth - his premise was the when others makes it somehow 'easier', mine is 'it makes it so so so very much harder')



I've said many times:



It is time to STOP "when others", just stop - the only valid use of when others is

begin
   ....
exception
   when others
   then
        log_error_using_procedure_that_uses_autonomous_transaction();
        RAISE;
end;




and the CLIENT submits that block (that is YOUR CODE does not use when others, ONLY THE CLIENT does - at the top level, it can log that a called b called c called d and d done DIED a horrible death).

Juan Carlos Reyes, March 03, 2009 - 5:04 pm UTC

Definetively I think is better the raise in the code calling the function than in the function itself.

Obviously there must be a raise and a rollback nobody discusses it.

The discussion is now if the raise SHOULD BE in the function without options, or in the code that calls the function.
Tom Kyte
March 03, 2009 - 9:26 pm UTC

... Definetively I think is better the raise in the code calling the function than
in the function itself. ...


WHY??????????????


The function already raised the issue, the function already RAISED the exception. So, why would you un-raise it and have someone one level up raise something totally different, to have someone at another layer un-raise it, to have it be raised again under another name. UGH - unbelievable.


Please, begging you, before you post here again - google

swallow exception

And read. And read this linked to from above:




5. Error handling that hides errors

This is pretty common, and unfortunately it¿s really only something that gets noticed when you have a problem. While things are working perfectly (and you don¿t care how it works), there¿s no problem. As soon as there¿s an error though, you can¿t find any details about it because the error handling is crap.





... Obviously there must be a raise and a rollback nobody discusses it. ...

that comment makes no sense whatsoever.



... The discussion is now if the raise SHOULD BE in the function without options,
or in the code that calls the function. ...

no it isn't.

the discussion is clearly, simply and plainly:

when others - do not use.

except in one case:


It is time to STOP "when others", just stop - the only valid use of when others is
begin
   ....
exception
   when others
   then
        log_error_using_procedure_that_uses_autonomous_transaction();
        RAISE;
end;





and the CLIENT submits that block (that is YOUR CODE does not use when others, ONLY THE CLIENT does - at the top level, it can log that a called b called c called d and d done DIED a horrible death).




When I see "when others" in a stored procedure/function - I'm pretty sure I'm looking at a bug


If it is followed by RAISE - it is an "ok" bug, just annoying as all get out since it MOVED THE ERROR from point A to point B

If it is not followed by a RAISE - it is a p1 high priority bug, comment it out right away. Period.

add columns with default value and undo

zzr_man, March 04, 2009 - 11:49 am UTC

Dear Tom,

You said :

[Quote]
no you don't. You just alter and add them. I would never consider using a create table as select for this operation.
[/Quote]

We use Oracle 10.2.0.3 Enterprise Edition.
We have a table about 1 billion rows. We try to add a column with a default value. This method generate a lot of undo and never complete because we run out of space.

We have a lot of space in the "data" tablespace and we don't need that data was still available during maintenance.

We use this method :
-create a temporary table with this new column
-insert /*+ append */
-drop main table
-rename table
-recreate grants, indexes, trigger etc.. in parallel.

May be it's not the best way, but it's faster than a add column with no default value and update.

Is there a better way ?


Can you give us an example with DBMS_REDEFINITION.

Thank you.



My $0.02

djb, March 05, 2009 - 9:51 am UTC

As I was reading through this thread I didn't see anything like what I'm about to say (though the thread is long - I could have missed it).

I think one of the things that Carlos and others might be missing is: Write code for the edge cases that are causing exceptions.

Do what Tom says, let exceptions propagate. Find out what's causing them and write code that fixes the problem.

For example: Divide by Zero

If this exception is being raised then CHECK FOR THE CONDITION THAT IS CAUSING IT - don't swallow it. Write code that determines what to do with it. If a variable is zero and it shouldn't be, then write code that handles it, but don't swallow the exception.

I've been following this paradigm for some years now. Whenever something breaks, I know it's a bug in the code. I write code that handles that particular edge case (or I let it be, the customer has to fix it on their end).

Viola, bug-free code.

I find that at least half my code (usually more) is validating input...

Juan Carlos Reyes, March 06, 2009 - 2:27 pm UTC

Hi Tom.

I had analyzed it and I know when Tom Kyte say something in this desesperated way, in the same other older dbas, is because he had lived a problem in some software implementation or know about one had this problem.

I had analyzed it and I must say he is write.

1. The rule is THERE SHOULD BE AN EXCEPTION IN
EVERY "WHEN OTHERS" because the risk is too big to get raise be skipped. I agree it. If you allow programers or accustom yourself to see when others without raise, this could some day cause a serious problem in your data.
This is not about if is necessary, else about mentally block it always as a norm to avoid serous problems.


function A
when others then

globalvariable=sqlerrm || otherdebugifno;
raise error;

end;

BUT
2. In the caller I don't agree with him, there must be a fascination to catch all exception, this could be a datafile can increase, a corrupted index, and I have a dozen of rare exception I had handled one or twice, not rarely are ora-0600, this is not only a diveded by zero exception.

that fascination is neccesary to stopped and rollback transaction, otherwise the transactions will be hang, and can commit involuntary for some process, exit sqplus a commit in another form, having the same shared process, etc, etc.

I think this could be in this way.

begin
Function a
exception when error then
display messageglobalvariable;
rollback;
stop_process;
end;


Honestly I don't think we are going to change the way we program, in this moment, because we always raise in the caller of the function; but I agree with Tom Kyte emphasis on always put a raise.

So I fix my function


CREATE OR REPLACE FUNCTION SYS.CTAS_GENERATION( cOwner VARCHAR2, cTable VARCHAR2) RETURN CLOB
AUTHID DEFINER IS
nCount NUMBER;
cTableTemp VARCHAR2(100) := 'TEMP_'||cTable;
cCaracteres VARCHAR2(3);
cCast VARCHAR2(100);
eError EXCEPTION;
cColumna VARCHAR2(100);
cReturn CLOB;
cReturnPostCreate CLOB;
cReturnDefault CLOB;
BEGIN


FOR A IN (
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||CONSTRAINT_NAME||';' CONS
FROM DBA_CONSTRAINTS
WHERE R_CONSTRAINT_NAME
IN (SELECT CONSTRAINT_NAME
FROM DBA_CONSTRAINTS
WHERE OWNER = cOwner AND TABLE_NAME = cTable )
) LOOP
cReturn := cReturn || A.CONS||CHR(10);
END LOOP;
cReturn := cReturn || CHR(10);


FOR A IN (
SELECT 'ALTER TABLE '||OWNER||'.'||TABLE_NAME||' DROP CONSTRAINT '||CONSTRAINT_NAME||';' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || A.CONS||CHR(10);
END LOOP;


FOR A IN (SELECT 'DROP TRIGGER '||TABLE_OWNER||'.'||TRIGGER_NAME||';' TRIG
FROM DBA_TRIGGERS WHERE TABLE_OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || A.TRIG ||CHR(10)||CHR(10);
END LOOP;

FOR A IN (SELECT REPLACE(TRIM(DBMS_METADATA.GET_DEPENDENT_DDL('OBJECT_GRANT',cTable,cOwner)),
CHR(10)||' '||CHR(10),';'||CHR(10) ) PRIV FROM DUAL) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.PRIV) ||CHR(10);
END LOOP;
cReturnPostCreate := cReturnPostCreate || '/' ||CHR(10);

FOR A IN (
SELECT 'DROP INDEX '||OWNER||'.'||INDEX_NAME||';' IDX
FROM DBA_INDEXES WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturn := cReturn || TRIM(A.IDX) ||CHR(10);
END LOOP;

FOR A IN (
SELECT DBMS_METADATA.GET_DDL('INDEX',INDEX_NAME,OWNER)||'/' IDX
FROM DBA_INDEXES WHERE OWNER=cOwner AND TABLE_NAME = cTable ) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.IDX) ||CHR(10);
END LOOP;

BEGIN
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable AND NOT CONSTRAINT_TYPE = 'R'
) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;
EXCEPTION WHEN OTHERS THEN
cReturnPostCreate := cReturnPostCreate || SQLERRM;
END;
cReturnPostCreate := cReturnPostCreate || CHR(10);

-- Constraints de otras tablas a esta tabla
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS WHERE R_CONSTRAINT_NAME
IN (SELECT CONSTRAINT_NAME FROM DBA_CONSTRAINTS
WHERE TABLE_NAME = cTable AND OWNER = cOwner)
ORDER BY OWNER,TABLE_NAME) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;



BEGIN
FOR A IN (
SELECT DBMS_METADATA.GET_DDL('REF_CONSTRAINT',CONSTRAINT_NAME,OWNER)||'/' CONS
FROM DBA_CONSTRAINTS
WHERE OWNER=cOwner AND TABLE_NAME = cTable AND CONSTRAINT_TYPE = 'R'
) LOOP
cReturnPostCreate := cReturnPostCreate || TRIM(A.CONS) ||CHR(10);
END LOOP;
EXCEPTION WHEN OTHERS THEN
cReturnPostCreate := cReturnPostCreate || SQLERRM;
END;
cReturnPostCreate := cReturnPostCreate || CHR(10);


cReturn := cReturn || 'ALTER TABLE '||cOwner||'.'||cTable||' RENAME TO '||cTableTemp||';'||CHR(10);

cReturn := cReturn || 'CREATE TABLE '||cOwner||'.'||cTable||CHR(10)||' AS '||CHR(10);
cReturn := cReturn || 'SELECT ';


FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP



IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cCast := NULL;
IF r.DATA_TYPE='VARCHAR2' THEN
cCast := 'CAST ('||r.COLUMN_NAME||' AS VARCHAR2('||r.DATA_LENGTH||')) ';
ELSIF r.DATA_TYPE='NUMBER' THEN
cCast := 'CAST ('||r.COLUMN_NAME||' AS NUMBER('||r.DATA_PRECISION||','||r.DATA_SCALE||')) ';
END IF;
cColumna := cCast||r.COLUMN_NAME;

IF r.DATA_DEFAULT IS NOT NULL THEN
cReturnDefault := cReturnDefault || r.COLUMN_NAME|| ' DEFAULT '|| R.DATA_DEFAULT || ',';
END IF;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTableTemp ||';'||CHR(10);
cReturn := cReturn ||CHR(10);

FOR A IN (SELECT REPLACE ( DBMS_METADATA.GET_DDL('TRIGGER',TRIGGER_NAME,OWNER)||'/',
'ALTER TRIGGER ', '/'||CHR(10)||CHR(10)||'ALTER TRIGGER') TRIG
FROM DBA_TRIGGERS WHERE TABLE_OWNER=cOwner AND TABLE_NAME = cTable) LOOP
cReturn := cReturn || A.TRIG ||CHR(10)||CHR(10);
END LOOP;



cReturn := cReturn ||CHR(10);
cReturn := cReturn ||cReturnPostCreate;
cReturn := cReturn ||CHR(10);

IF cReturnDefault IS NOT NULL THEN
cReturnDefault := SUBSTR(cReturnDefault,1,LENGTH(cReturnDefault)-1);
cReturnDefault := 'ALTER TABLE '||cOwner||'.'||cTable||' MODIFY ('||
cReturnDefault||')'||CHR(10)||'/';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||cReturnDefault;
cReturn := cReturn ||CHR(10);

END IF;

cReturn := cReturn ||'--Before drop temporary tables, do comparations and check objects';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'--DROP TABLE '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'------------------------ ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'-- Validations ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'SELECT '''||cTable||''',COUNT(*) FROM '||cOwner||'.'||cTable ;
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'UNION ALL';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'SELECT '''||cTableTemp||''',COUNT(*) FROM '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'------------------------ ';
cReturn := cReturn ||CHR(10);
cReturn := cReturn ||'--DROP TABLE '||cOwner||'.'||cTableTemp ||';';
cReturn := cReturn ||CHR(10);


cReturn := cReturn || 'SELECT ';
cColumna := NULL;
FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP

IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cColumna := r.COLUMN_NAME;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTableTemp ||''||CHR(10);
cReturn := cReturn ||CHR(10);
cReturn := cReturn || 'MINUS'||CHR(10);
cReturn := cReturn ||CHR(10);
cReturn := cReturn || 'SELECT ';


cColumna := NULL;
FOR r IN
(SELECT COLUMN_NAME,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,DATA_DEFAULT FROM DBA_TAB_COLS
WHERE TABLE_NAME = cTable AND OWNER = cOwner
AND NOT SUBSTR( COLUMN_NAME, 1, 3 )= 'SYS'
ORDER BY COLUMN_ID) LOOP

IF cColumna IS NOT NULL THEN
cReturn := cReturn || cColumna ||','||CHR(10);
ELSE
cReturn := cReturn ||cColumna ||CHR(10);
END IF;
cColumna := r.COLUMN_NAME;

END LOOP;
cReturn := cReturn ||cColumna ||CHR(10);
cReturn := cReturn || 'FROM '|| cOwner ||'.'||cTable ||';'||CHR(10);
cReturn := cReturn ||CHR(10);



RETURN cReturn ;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE(cReturn||CHR(10)||CHR(10)||SQLERRM) ;
RAISE eError ;
END;
/

Thanks :)
Tom Kyte
March 06, 2009 - 2:46 pm UTC

1) the rule is there should be one "when others" in a code path at most and it should *never* be in the procedure/function itself, it should ONLY be in the top level invoking block of code.

when others - in a procedure or function - never.

when others, used like this from a CLIENT:

begin
  p;
exception when others then
  log_error();
  probably, almost always, re-raise-exception;
end;




In your code:

EXCEPTION
   WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(cReturn||CHR(10)||CHR(10)||SQLERRM) ;
    RAISE eError ; 
END;



all that accomplishes is:

a) you might see sqlerrm twice - if you are in sqlplus
b) you will LOSE the line number where the error occurred, it will be harder to figure out where the error was coming from.


This when others - still falls into the:

"I hate your code"

category, there is nothing good about it, nothing.


... 2. In the caller I don't agree with him, there must be a fascination to catch 
all exception, this could be a datafile can increase, a corrupted index, and I 
have a dozen of rare exception I had handled one or twice, not rarely are 
ora-0600, this is not only a diveded by zero exception. 

that fascination is neccesary to stopped and rollback transaction, otherwise 
the transactions will be hang, and can commit involuntary for some process, 
exit sqplus a commit in another form, having the same shared process, etc, etc.
 ...

sorry, but that is all, I'll be blunt, nonsense.


If you catch "cannot grow datafile" in your stored procedure (that MUST have been doing something to cause the datafile to need to grow) - WHAT CAN YOUR PROCEDURE DO ABOUT IT??? NOTHING, IT CAN DO NOTHING, IT HAS FAILED, LET IT DIE ALREADY

You say "is necessary to stop and rollback transaction" - you must have missed the point that

if the error propagates back the client, the statement causing the error is already rolled back - it is only when you (not intelligently) catch and hide the error that you must intervene manually!!!! meaning - if you let nature take its course - everything would be OK BY ITSELF, the database does transactions - it supports the ACID concepts (A = Atomicty, a statement either entirely succeeds, or entirely fails - unless you the developer MESS IT ALL UP - and that is all your when others would do)


this is really important stuff - I will continue to write what I have written over and over and over again


o when others in procedure - almost certainly a bug unless followed by RAISE.

o when others in procedure with raise - certainly a bad idea as it accomplishes nothing GOOD and only accomplishes BAD.

o when others at the very top level to log an error and then re-raise - that is acceptable.




.... Honestly I don't think we are going to change the way we program, ....

you should, your current approach is fundamentally flawed.

A reader, March 10, 2009 - 9:59 am UTC

Hi Tom, Thank you for your answer, I agree with you, but I'm trying to conceptually visualize how to integrate in a complex software and have some questions please.

Another situation,

you have a lot of procedures and a customer ask you to create a procedure to call from another software, the answer is the same?, he must add in the client the when others? Wouldn't be more appropiated if this a database of mission critial don't leave to him, and cath it in the procedure itself.

Because if he forgets to catch the error correctly and there is a data corruption, because he mistakenly commited the data instead of rolling back when the when others raised, even if you will finally demostrate he was the responsible, is the software providers who will be accused of not foresee this problem.

Thanks :)

Tom Kyte
March 11, 2009 - 2:49 am UTC

if a customer asks you to create an API.

And you give the customer an API that always returns success - even when it failed utterly, miserably, completely - due to error outside of your control

You have just failed your customer in the WORST WAY POSSIBLE. The customer doesn't need to have a when others - they would do that if they WANTED TO (I'm saying when others belongs in one and only one place if it exists at all - at the top level. No on ever said "it HAS to be there", only that "if you are going to use it, that is the ONLY PLACE it belongs")

Your customer has right, a need to know that your procedure FAILED.

... Because if he forgets to catch the error correctly and there is a data
corruption, ....

You are missing the point that if you let the error propagate back to the client - IT IS ALREADY ROLLED BACK@!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
ops$tkyte%ORA11GR1> create table t ( msg varchar2(40) );

Table created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> create or replace procedure p1
  2  as
  3  begin
  4          insert into t values ( '1 if you see me, I am a bug...' );
  5          raise program_error;
  6          insert into t values ( '2 if you see me, I am a bug...' );
  7  exception
  8          when others then
  9                  dbms_output.put_line( 'whoopsy' );
 10  end;
 11  /

Procedure created.

ops$tkyte%ORA11GR1> create or replace procedure p2
  2  as
  3  begin
  4          insert into t values ( '3 if you see me, I am a bug...' );
  5          raise program_error;
  6          insert into t values ( '4 if you see me, I am a bug...' );
  7  end;
  8  /

Procedure created.

ops$tkyte%ORA11GR1>
ops$tkyte%ORA11GR1> select * from t;

no rows selected

ops$tkyte%ORA11GR1> exec p1;
whoopsy

PL/SQL procedure successfully completed.

ops$tkyte%ORA11GR1> select * from t;

MSG
----------------------------------------
1 if you see me, I am a bug...

ops$tkyte%ORA11GR1> exec p2;
BEGIN p2; END;

*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "OPS$TKYTE.P2", line 5
ORA-06512: at line 1


ops$tkyte%ORA11GR1> select * from t;

MSG
----------------------------------------
1 if you see me, I am a bug...




and furthermore, if

Because if he forgets to catch the error correctly and there is a data
corruption, because he mistakenly commited the data instead of rolling back
when the when others raised, even if you will finally demostrate he was the
responsible, is the software providers who will be accused of not foresee this
problem.


that paragraph is so warped in what it says (so bizarre, so strange, so wrong, so .... )

If your customer is processing transactions - your customer is the software engineer at that point, they are developing software.

And if your customer doesn't do error handling - well, think about this for a minute.

And if they "corrupt their data" - remember - it was already rolled back, IT DID THE ROLLBACK, it rolls back, it is atomic, it never happened - then they did it entirely themselves.

By not handling errors, anyone - ANYONE that sits does to type in a single line of code is responsible for error detection and handling.



What if
a) you catch when others
b) you rollback
c) you re-raise

that is not any different than if you :

<space left intentionally blank>

as far as the work performed by your procedure is concerned (it is different however, the first one would be WRONG since you would be rolling back work you did not perform, work that is NOT YOURS to control, the second one is correct)

A reader, March 11, 2009 - 2:31 pm UTC

Thanks Tom
>You are missing the point that if you let the error propagate back to the client - IT IS ALREADY ROLLED BACK@!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

I didn't knew that

I used a savepoint, a trigger raising an error, an error in the insert because a wrong data type, and you are right.

The question to you to be sure is
No matter what kind or ORA- error, manual raise, etc.
Oracle will always rollback?

I asked other dbas and some they said there was a need to rollback. So this is not only me.

I'm not sure if the reason for this erroneous thinking was
because in oracle forms there was the need to clean form blocks aditionally , or a bug we hit on 7.3.something causing some times the insert didn't happend , and we had to do a count(*) before an after to be sure, and this is real, that time there wasn't documentation and all about oracle was a secret this is not like now.


Tom Kyte
March 12, 2009 - 12:33 pm UTC

...
The question to you to be sure is
No matter what kind or ORA- error, manual raise, etc.
Oracle will always rollback?

.....

statement level atomicity - a statement either entirely succeeds or entirely fails. If you fail, it fails - entirely, the database is put back the way it was when the statement was submitted.. Unless you MESS IT UP by committing or rolling back yourself of course.

...
I asked other dbas and some they said there was a need to rollback. So this is
not only me.
.....

You are right, it is not only you that doesn't know how transactions work in the database, I agree.

In fact, it is a fact that if you read this page - you would have known it, we demonstrated this fact above - before you followed up (look for maintain_t
- an example before this last example). I've written about this a lot, others have - it is well documented out there.

forms is a client application, it isn't the database at all.... And I hate paragraphs like this:

I'm not sure if the reason for this erroneous thinking was
because in oracle forms there was the need to clean form blocks aditionally ,
or a bug we hit on 7.3.something causing some times the insert didn't happend ,
and we had to do a count(*) before an after to be sure, and this is real, that
time there wasn't documentation and all about oracle was a secret this is not
like now.


That is FUD - fear, uncertainty, doubt.

So, there was a need to clear a form block - because you had put some data in a data structure in a client application - so what? what does that have to do with transaction processing?

"sometimes the insert didn't happen" - sorry, you were doing something wrong there, the insert happened or not - but not because the insert said "i happened, but I've tricked you and didn't really happen", there was a bug in your form.


And the behavior of statements in the database was documented - even back in 7.3

in defense of when others

William, March 11, 2009 - 4:58 pm UTC

Dear Mr. Kyte,
While I see that you are very passionate about this subject and rightfully so I have to tell you that many developers write this - as a "conversion data debugging" technique :
When you do - like you have advised:

begin
....
exception
when others
then
log_error_using_procedure_that_uses_autonomous_transaction();
RAISE;
end;


you need to run the proc as many times as the data caused the proc to fail.
When you remove RAISE - just once

Thanks!


Tom Kyte
March 12, 2009 - 12:35 pm UTC

what you wrote makes no sense to me whatsoever. None, please explain

if you remove the raise - you might as well just remove the code represented by the .... because you don't know how much of it ran, one line, zero lines, all but one line, 42 lines - whatever.


Please give me a real world example whereby you think it is "fine"

A reader, March 12, 2009 - 2:39 pm UTC

Hi Tom
>You are right, it is not only you that doesn't know how transactions work in the database, I agree.

In sql server 2000, you had to check after every command if there were error, you work in Oracle and know this but this is not obvious to everyone,
I had asked several people and they didn't suppose that.

Maybe could be a good idea to certify in documentation this will happen in Oracle Database: "Any kind of raise including ora-600 will rollback the transaction" In a notorious manner. Because that don't happen in sql server 2000, this is not as obvious. I can't suppose this will work, and risk to corrupt database.
I read all documentation to certify 9i and I didn't got that idea.

That is FUD - fear, uncertainty, doubt. Was present until I certify and received your help, thanks for that.


The insert was executed, and it didn't insert, and didn't return an error in sqlplus, we tested maybe it was a bug we hit once, and that's the reason we did that. Now if you don't trustme I can't do too much.

Was documentation available on internet on that time?

Thanks :)
Tom Kyte
March 12, 2009 - 2:58 pm UTC

... In sql server 2000, you had to check after every command if there were error,
you work in Oracle and know this but this is not obvious to everyone,
I had asked several people and they didn't suppose that. ....

reason #1142354325 that "database independence" ----- isn't.



... Because that don't happen in sql server
2000, this is not as obvious. ...

I'm sorry that sql server doesn't apply the ACID (atomicity, consistency, isolation, durability) principles that relational databases are supposed to - but to say "we should document that we do the right thing according to the rule of ACID" simply because their database does not??? Do not people realize that different databases are different, they do things differently, if they did not do things differently - why would there be more than one database?!?!?!?

We do document that statements either

a) entirely complete
b) entirely fail

we do not leave the database inconsistent (as other databases might)


... Because that don't happen in sql server
2000, this is not as obvious.....

Frankly, I don't really give a hoot what sqlserver (or ibm, or informix or mysql or postgres or ........ does - not anymore than windows cares what linux, solaris, hp/ux, aix, MVS does - they are DIFFERENT)


... The insert was executed, and it didn't insert, and didn't return an error in
sqlplus, we tested maybe it was a bug we hit once, and that's the reason we did
that. Now if you don't trustme I can't do too much.

....

No, I do not trust you, this is you saying "10 years ago, I remember something funny happening - I cannot reproduce it or tell you how to, but I'm sure it happened". It didn't happen that way. 7.3 did not have a bug of any sort that had an insert "sometimes not work", nope, it did not happen that way. NO ONE COULD HAVE USED THE PRODUCT FOR ANYTHING if that were true. Something else was afoot (I'd guess "you had triggers" and "you hid errors" and "you did this yourself"), but since we don't have a time machine - we cannot tell.


.... Was documentation available on internet on that time? ....

what does that have to do with anything? In 1995 when 7.3 was out - you would have read a paper book, or a pdf (I had the 7.3 documentation in pdf format, everyone that had 7.3 could have that). Why would the "internet" be relevant here - you are in IT, you read, you learn, you master the technology...

A reader, March 12, 2009 - 6:52 pm UTC

Thank you Tom, this was very help ful to me, thank you for the time to explainme until I understand I am less stupid than yesterday.

And I have to remember not to take too much coffee, it makes me nervous.

:)

follow up

William, April 14, 2009 - 4:05 pm UTC

Dear Mr. Kyte,
While I see that you are very passionate about this subject and rightfully so I have to tell you
that many developers write this - as a "conversion data debugging" technique :
When you do - like you have advised:

begin
....
exception
when others
then
log_error_using_procedure_that_uses_autonomous_transaction();
RAISE;
end;


you need to run the proc as many times as the data caused the proc to fail.
When you remove RAISE - just once

Thanks!




Followup March 12, 2009 - 12pm US/Eastern:
what you wrote makes no sense to me whatsoever. None, please explain

if you remove the raise - you might as well just remove the code represented by the .... because you don't know how much of it ran, one line, zero lines, all but one line, 42 lines - whatever.


Please give me a real world example whereby you think it is "fine"

real world example:
when processing a files from legacy systems people log successful records processed and mark them as successful and when one encounters "other" exception - just log the exception, mark the record as failed and then proceed to the next record instead of exiting the program.
On the end of the program you report successful records and failed records.
This way you have at least some records loaded - then you fix the failed ones and reprocess.
If this is really crime against PL/SQL coding so be it but there are hundreds of Oracle Apps implementers who use this "technique" just to get job done.
Thanks!



Tom Kyte
April 14, 2009 - 4:20 pm UTC


Basically - you are saying "errors are not errors, they are expected and we are ok with that and we want to keep on trucking on"

Fine, I'm OK with that, you are doing a data load maybe - you are loading lots of records and you want to have 'bad data' get logged and 'good data' get loaded. Perfect example of when this others thing MIGHT BE USEFUL. (in fact - on other pages, we've discussed this and I've even demonstrated how to do it! search this site for "save exceptions" for example)


However, it is also a teeny tiny fraction of the plsql code in existence.


And should mostly be replaced with LOG ERRORS in 10g and above.


Am I aware of cases where it could be useful? Yes, absolutely.

Am I aware that there are some people that are knowledgeable enough to use it correctly? Yes, absolutely.

Am I aware of how to properly use this feature myself? Yes, I think so.

Will I continue to flag virtually every "when others" I see with "I hate your code" (search for that string on my site as well :) ) - positively, definitely, YES. Why? Because virtually every use I see of it is WRONG, BAD, INCORRECT. Look at the example on this page - using when others was just the WRONG THING to do - absolutely the wrong thing to do.



And remember, I write:

when others, not followed by raise or raise_application_error, is almost certainly a bug in your developed code.


is almost certainly


I want people to think 1,000 times before coding when others, to understand the ramifications, to know what they are doing. And, if in my zeal, I make some people so afraid of "when others" they do not use it - so be it, that is better then if they use it without thinking about it.

When others without raise

Stew Ashton, April 28, 2012 - 9:35 am UTC


Tom, I'm putting this here because you very recently explained yet again your opposition to overuse of WHEN OTHERS without a RAISE.

Today on the OTN forum someone explained the FORMAT_ERROR_BACKTRACE function by copying an example from the official documentation. This example contains WHEN OTHERS not followed by a RAISE. Here is the link to the documentation. http://docs.oracle.com/cd/E11882_01/appdev.112/e25788/d_util.htm#i1003874
The example is the "Top_With_Logging" procedure.

I went on the forum to say that I thought it was misleading to give such an example without putting the RAISE in there and that the documentation should be changed.

In the past you have agreed to submit documentation change requests concerning WHEN OTHERS. If you agree the "Top_With_Logging" example needs improvement, could you make the request? It's no fun fighting the official documentation on otn.
Tom Kyte
April 30, 2012 - 8:10 am UTC

do you understand that is a demonstration of how to log an error - not necessarily "this is how to deal with all errors". That code obviously doesn't "deal" with the error, it is just demonstrating the use of the format error utility function.

I'll file a request to have the raise there - but - please take demos for what they are. demonstrations of a particular FUNCTION - not "this is production ready code that shows you the best way possible to code"

More to Explore

DBMS_REDEFINITION

More on PL/SQL routine DBMS_REDEFINITION here