Re: How can I interpert error codes returned in PLSQL procedure?

In article <9u3187$cc0$1_at_panix1.panix.com>, stanb_at_panix.com says...
>
>Once upon a time in a universe far away, I knew this, but now I jave
>forgotten :-)
>
>I'm trying to get a PLSQL procdure that I used several years agao to study
>some things about indexes to run on my nice new Oracle server. There are
>several things that are different about the new one (7.3.4.5 if it matters,
>so I;m certain that my problem has to do with that.
>
>The real dificulty lies in the fact that I can't interpet the error code
>the procedure is returning. Like a good little programer, I did put in a

c/good/bad/

having a "when others" that does not immediate re-raise the exception is almost certainly a BUG. You silently ignore all errors. What if someone calls your procedure without serveroutput on? Or calls it from a java/jdbc app (no dbms_output there). You will silently *fail* and they will never ever know.

>catch all exception hadnler, and it's being trigered. But to use oerr to
>give me the text, I need to now the prefix as well as the error code :-(
>
>How can I figure this out?
>
>Here is teh message I'm getting:
>
>Other Exception -947 c_table_name B100 v_table_name B100
>

that means you have an "insert into values" and you don't have enough values to match the list of columns in the table OR to match the number of columns in the column list in the insert:

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> create table t ( x int, y int );

Table created.

ops$tkyte_at_ORA817DEV.US.ORACLE.COM> insert into t values ( 1 ); insert into t values ( 1 )

            *
ERROR at line 1:
ORA-00947: not enough values

>And it's being generated by this peice of code:
>
>DBMS_OUTPUT.PUT_LINE('Other Exception ' || error_code || ' c_table_n
>ame ' || c_table_name || ' v_table_name ' || c_table_name);
>

no, its being raised by the INSERT.

get rid of the WHEN OTHERS, let the error propagate to the client -- that way you'll

• get the error code and message you would see: "ORA-00947: not enough values"
• the line number in your code where it happens, useful if you have say 5 inserts, as you have it coded -- you really have no idea which one caused it.

>Thanks for re-educating me on this!
>
>--
>"They that would give up essential liberty for temporary safety deserve
>neither liberty nor safety."
> -- Benjamin Franklin

--
Thomas Kyte (tkyte@us.oracle.com)             http://asktom.oracle.com/ 
Expert one on one Oracle, programming techniques and solutions for Oracle.
http://www.amazon.com/exec/obidos/ASIN/1861004826/  
Opinions are mine and do not necessarily reflect those of Oracle Corp