Oracle SQL - If exists, Drop Table & Create
Can someone please explain to me what happened to this question? In SQL Server, we just check for the Object_ID of the table to drop and recreate it. I am new to Oracle and wrote this query:
declare Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
EXCEPTION
WHEN NO_DATA_FOUND
THEN
Table_Exists :=0;
if(table_exists)=1
Then
Execute Immediate 'Drop Table TABLENAME1;'
'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
I get the output - ANONYMOUS BLOCK COMPLETED, but the table was not created. There was a table before, so I dropped it to check if PL / SQL actually creates the table, but no. What's wrong here? What am I missing? Please guide.
source to share
The EXCEPTION clause lasts until the next END, not just the next statement. If you want to continue playing after exclusion, you need to add an additional BEGIN / END:
declare
Table_exists INTEGER;
BEGIN
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
EXCEPTION
WHEN NO_DATA_FOUND THEN
Table_Exists :=0;
END;
if(table_exists)=1 Then
Execute Immediate 'Drop Table TABLENAME1;'
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
As Gordon pointed out, the EXCEPTION clause is really not needed in this case, as count(*)
it will always return one row. Thus, it is enough:
declare
Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.all_tables where table_name='TABLENAME1';
if(table_exists)=1 Then
Execute Immediate 'Drop Table TABLENAME1;'
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1;';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
source to share
When using, all_tables
filter the results for yours by adding where owner = 'your_schema'
or usingsys.user_tables
ALL_TABLES describes the relational tables available for the current user
USER_TABLES describes the relational tables owned by the current user.
If used, execute_emmidiate
remove ;
from request;
Modified request;
DECLARE
Table_exists INTEGER;
BEGIN
Select count(*) into Table_exists from sys.user_tables where table_name='TABLENAME1';
--or
--Select count(*) into Table_exists from sys.all_tables
--where table_name='TABLENAME1' and owner = 'your_DB';
if table_exists = 1 Then
Execute Immediate 'Drop Table TABLENAME1';
Execute Immediate 'Create Table TABLENAME1(num number)';
DBMS_OUTPUT.PUT_LINE('Table Dropped and Re-Created!');
Else
Execute Immediate 'Create Table TABLENAME1(num number)';
DBMS_OUTPUT.PUT_LINE('New Table Created!');
END IF;
END;
source to share
First note:
Select count(*) into Table_exists
from sys.all_tables
where table_name = 'TABLENAME1';
will always return one row. You don't need exception handling.
My best guess is that you have more than one table named TABLENAME1
. Run this query to find out:
Select *
from sys.all_tables
where table_name = 'TABLENAME1';
Oracle stores tables from all owners that you can access. You can also check OWNER_NAME
in the offer where
.
However, you seem to understand exception handling. So, just drop the table, ignore any errors, and then re-create the table.
source to share