Welcome Guest ( Log In | Register )


 
 
 
 
 
 

 
 
Oracle 

performance Tuning 10g reference poster
 
Oracle training in Linux 

commands
 
Oracle training 

Excel
 
Easy Oracle Jumpstart
 
Oracle training & performance tuning books
 
Burleson Consulting Remote DB Administration
 
 
 
Reply to this topicStart new topic
> Catching ORA-00942 (table or view not found) exception.
Jughead
post Jun 1 2009, 10:09 AM
Post #1


Newbie
*

Group: Members
Posts: 4
Joined: 3-April 09
Member No.: 40,256



Hi There

Could anyone let me know the name for the exception ORA-00942 (table or view does not exist).

Actually I am trying to write a pl sql script which drops a table if it does not exist.
Intention is to use simple exception handling to catch the "table or view does not exist" on execution
of the stament : drop table my_table;
Below is the script

==================================
drop_sql VARCHAR(200);

BEGIN
drop_sql := 'drop table my_table';
EXECUTE IMMEDIATE drop_sql;

EXCEPTION
WHEN <what should go in here to catch table or view not exixts i.e. ORA-00942 exception>
-- Use this to trap the ORA-00942: table or view does not exist
WHEN OTHERS THEN
< Want to throw back the exception when its not ORA-00942 i.e table or view not exists >
END;
/
===================================
Go to the top of the page
 
+Quote Post
RobinT
post Jun 1 2009, 10:22 AM
Post #2


Advanced Member
***

Group: Members
Posts: 128
Joined: 13-May 09
From: Canada, Quebec
Member No.: 40,668



Hello,

Use this part of code to help you resolve the problem. Use SQLCODE and SQLERRM to catch the appropriate error
and use WHEN OTHERS section.

CREATE OR REPLACE PROCEDURE getErrorInfo (
errcode OUT INTEGER,
errtext OUT VARCHAR2)
IS
c_keyword CONSTANT CHAR(23) := 'java.sql.SQLException: ';
c_keyword_len CONSTANT PLS_INTEGER := 23;
v_keyword_loc PLS_INTEGER;
v_msg VARCHAR2(1000) := SQLERRM;
BEGIN
v_keyword_loc := INSTR (v_msg, c_keyword);
IF v_keyword_loc = 0
THEN
errcode := SQLCODE;
errtext := SQLERRM;
ELSE
errtext := SUBSTR (
v_msg, v_keyword_loc + c_keyword_len);
errcode :=
SUBSTR (errtext, 4, 6 /* ORA-NNNNN */);
END IF;
END;
/

BEGIN
dropany ('TABLE', 'blip');
EXCEPTION
WHEN OTHERS
THEN
DECLARE
v_errcode PLS_INTEGER;
v_errtext VARCHAR2(1000);
BEGIN
getErrorInfo (v_errcode, v_errtext);
log81.saveline (v_errcode, v_errtext);
END;
END;
/



--------------------
Robin Tremblay
http://www.cgi.com
Oracle DBA and architect Consultant
Go to the top of the page
 
+Quote Post
RobinT
post Jun 1 2009, 10:26 AM
Post #3


Advanced Member
***

Group: Members
Posts: 128
Joined: 13-May 09
From: Canada, Quebec
Member No.: 40,668



Maybe taht more appropriate:

Using SQLCODE() and SQLERRM() in an OTHERS exception handler.

BEGIN
<statements>

EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
DBMS_Output.Put_Line (SQLERRM);

ELSE
RAISE;
END IF;
END;

This example calls the SQLCODE() function to determine what error is occurring and SQLERRM() to record the text of the error message.


Table 4.2 Predefined exceptions in PL/SQL.

Exception
Associated Oracle Error

CURSOR_ALREADY_OPEN
ORA-06511

DUP_VAL_ON_INDEX
ORA-00001

INVALID_CURSOR
ORA-01001

INVALID_NUMBER
ORA-01722

LOGIN_DENIED
ORA-01017

NO_DATA_FOUND
ORA-01403

NOT_LOGGED_ON
ORA-01012

PROGRAM_ERROR
ORA-06501

STORAGE_ERROR
ORA-06500

TIMEOUT_ON_RESOURCE
ORA-00051

TOO_MANY_ROWS
ORA-01422

TRANSACTION_BACKED_OUT
ORA-00061

VALUE_ERROR
ORA-06502

ZERO_DIVIDE
ORA-01476




--------------------
Robin Tremblay
http://www.cgi.com
Oracle DBA and architect Consultant
Go to the top of the page
 
+Quote Post
Jughead
post Jun 1 2009, 10:43 AM
Post #4


Newbie
*

Group: Members
Posts: 4
Joined: 3-April 09
Member No.: 40,256



Let me know if I am correct on my understanding of your suggestion below.
----------------------------------------------
BEGIN
<statements>

EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
DBMS_Output.Put_Line (SQLERRM);

ELSE
RAISE;
END IF;
END;
-------------------------------------------------
You used WHEN OTHERS to get any exception that might have been raised .
Then its checked if that particular exception is <table or view not exists> ORA-00942
The else part just throws back any exception other than ORA-00942

(Actually this EXCEPTION WHEN OTHERS sounds a little confusing so just wanted clarify )

Thanks for the response :^)



QUOTE (RobinT @ Jun 1 2009, 10:27 AM) *
Maybe taht more appropriate:

Using SQLCODE() and SQLERRM() in an OTHERS exception handler.

BEGIN
<statements>

EXCEPTION
WHEN OTHERS THEN
IF (SQLCODE = -942) THEN
DBMS_Output.Put_Line (SQLERRM);

ELSE
RAISE;
END IF;
END;

This example calls the SQLCODE() function to determine what error is occurring and SQLERRM() to record the text of the error message.


Table 4.2 Predefined exceptions in PL/SQL.

Exception
Associated Oracle Error

CURSOR_ALREADY_OPEN
ORA-06511

DUP_VAL_ON_INDEX
ORA-00001

INVALID_CURSOR
ORA-01001

INVALID_NUMBER
ORA-01722

LOGIN_DENIED
ORA-01017

NO_DATA_FOUND
ORA-01403

NOT_LOGGED_ON
ORA-01012

PROGRAM_ERROR
ORA-06501

STORAGE_ERROR
ORA-06500

TIMEOUT_ON_RESOURCE
ORA-00051

TOO_MANY_ROWS
ORA-01422

TRANSACTION_BACKED_OUT
ORA-00061

VALUE_ERROR
ORA-06502

ZERO_DIVIDE
ORA-01476

Go to the top of the page
 
+Quote Post
onkar
post Jun 2 2009, 05:13 AM
Post #5


Advanced Member
***

Group: Members
Posts: 77
Joined: 12-January 05
From: India
Member No.: 1,280



Try this:

PROCEDURE DROP_TABLE (pNAME IN VARCHAR2)
AS
C_DROP_TABLE CONSTANT VARCHAR2(200) := 'DROP TABLE '||UPPER(pNAME);
C_LOCK_TABLE CONSTANT VARCHAR2(200) := 'LOCK TABLE '||UPPER(pNAME)||' IN EXCLUSIVE MODE';
TABLE_MISSING EXCEPTION;
PRAGMA EXCEPTION_INIT(TABLE_MISSING,-942);
BEGIN
EXECUTE IMMEDIATE C_LOCK_TABLE;
EXECUTE IMMEDIATE C_DROP_TABLE;
EXCEPTION
WHEN TABLE_MISSING
THEN
NULL; -- use default error handling behaviour if you feel like or use your own error number and message..
END;

This way you can handle the situation where the table is missing and in that condition , you wont get any error but if you want to show an error at the time when table is missing then in place of NULL, you can use either SQLCODE,SQLERRM to display default error number and message else you can override the default error handling behaviour by adding your own code number & message like RAISE_APPLICATION_ERROR(-20000,'Table does not exist..');

Hope this helps..


--------------------
Thanks & Regards,
T. Onkar Nath
(Oracle Certified DBA 9i)
(Teradata Certified DBA V2R5)
email : to_onkar@yahoo.com
Go to the top of the page
 
+Quote Post
RobinT
post Jun 2 2009, 09:40 PM
Post #6


Advanced Member
***

Group: Members
Posts: 128
Joined: 13-May 09
From: Canada, Quebec
Member No.: 40,668



For best understanding :

See http://www.ss64.com/oraplsql/exception.html

Also http://download.oracle.com/docs/cd/B10501_...624/07_errs.htm



--------------------
Robin Tremblay
http://www.cgi.com
Oracle DBA and architect Consultant
Go to the top of the page
 
+Quote Post

Reply to this topicStart new topic
1 User(s) are reading this topic (1 Guests and 0 Anonymous Users)
0 Members:

 

Lo-Fi Version Time is now: 24th April 2014 - 09:54 PM