Home » SQL & PL/SQL » SQL & PL/SQL » verifying whether or not a table exists
verifying whether or not a table exists [message #20574] Wed, 05 June 2002 11:43 Go to next message
V Sh
Messages: 8
Registered: June 2002
Junior Member
How can I verify whether or not a table exists because if that table exists then only in the intial SQL script I would like to drop the table.
I have figured out that the system has "tab" table in which it has "tname" and "tabtype" as two parameters but I don't know how to use them.
Please help
Re: verifying whether or not a table exists [message #20575 is a reply to message #20574] Wed, 05 June 2002 12:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Select from user_tables.

select table_name
into dummy_var
from user_tables
where table_name = 'EMP';

-- test contents of dummy variable
-- or check sql%rowcoult
if sql%rowcount = 1 then
execute immediate 'drop table emp';
end if;
...
Re: verifying whether or not a table exists [message #20578 is a reply to message #20574] Wed, 05 June 2002 14:35 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
Actually, I think Andrew meant this:

declare
  v_table_name  user_tables.table_name%type := 'LANGUAGE_CODE';
begin
  select table_name
    into v_table_name
    from user_tables
   where table_name = v_table_name;
 
  execute immediate 'drop table ' || v_table_name;
exception
  when no_data_found then
    null;
end;
Re: verifying whether or not a table exists [message #20580 is a reply to message #20578] Wed, 05 June 2002 15:27 Go to previous messageGo to next message
andrew again
Messages: 2577
Registered: March 2000
Senior Member
Thanks Todd - much cleaner.
Re: verifying whether or not a table exists [message #20602 is a reply to message #20578] Thu, 06 June 2002 08:04 Go to previous messageGo to next message
V Sh
Messages: 8
Registered: June 2002
Junior Member
Thanks for the previous uploads but this is what I get when I try to run the sql file using Oracle's SQLPLUS. Am I missing something while trying to execute the script?

Connected to:
Oracle9i Enterprise Edition Release 9.0.1.1.1 - Production
With the Partitioning option
JServer Release 9.0.1.1.1 - Production

SQL> @ temp2.sql
14

It just stops there at line number 14.
Please help.
Re: verifying whether or not a table exists [message #20604 is a reply to message #20578] Thu, 06 June 2002 08:34 Go to previous messageGo to next message
Todd Barry
Messages: 4819
Registered: August 2001
Senior Member
SQL*Plus always needs a '/' to indicate that a block should be run. Just add that plus a carriage return on a line after the 'end;'.

end;
/  -- hit Enter after this character
Re: verifying whether or not a table exists [message #20606 is a reply to message #20578] Thu, 06 June 2002 09:52 Go to previous message
V Sh
Messages: 8
Registered: June 2002
Junior Member
GOT IT WORKING !!!
Thanks a lot
Previous Topic: SQL running slow!
Next Topic: Can We Create a user of our choise entered through Parameters of a Procedure.
Goto Forum:
  


Current Time: Tue Nov 13 09:50:11 CST 2018