Help - Search - Members - Calendar
Full Version: Catching ORA-00942 (table or view not found) exception.
Oracle DBA Forums > Oracle > Oracle Forum
Jughead
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;
/
===================================
RobinT
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;
/

RobinT
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


Jughead
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

onkar
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..
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.
Invision Power Board © 2001-2014 Invision Power Services, Inc.