DROP TABLE IF EXIST in oracle

Question

0.00/5 (No votes)

See more:

hi experts
i want to create 3 to 4 table in a loop in many diffrent locations but in some locations the table exist and gives the exception already exist so is there any command like while creating function CREATE OR REPLACE etc i have tried DROP TABLE IF EXIST TABLENAME but not working...
any suggessions....

Posted 28-May-13 23:50pm

Basmeh Awad

Updated 29-May-13 6:12am

Kenneth Haugland

v4

Add a Solution

Comments

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) 30-May-13 9:13am

Hi Basmeh Awad,

Please accept my answer, if it has helped you in any way.
This will help others to find the answer in one go and you will also be awarded with some points for this action...

Thanks,
Tadit

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ) · Answer 1 · 2013-05-29T05:23:00

Solution 2

Please follow the below solutions, they might help you.

1. Oracle: If Table Exists[^].
2. Oracle drop table if exists[^].

Posted 29-May-13 5:23am

Tadit Dash (ତଡିତ୍ କୁମାର ଦାଶ)

Aarti Meswania · Answer 2 · 2013-05-29T00:11:00

Solution 1

if you want to check table exist or not then query should like below...

SQL

select count(*)
from all_objects
where object_type in ('TABLE','VIEW')
and object_name = 'your_table_name';

OR...

SQL

DECLARE tbl_exist  PLS_INTEGER;

BEGIN
select count(*) into tbl_exist from user_tables where table_name = 'mytable';
if tbl_exist = 1 then
    execute immediate 'drop table mytable';
end if;

END;
--your create table query...

Note: when table created with double-quote then it's case sensetive name so in that case your_table_name should be in same case

Happy Coding!
:)

Posted 29-May-13 0:11am

Aarti Meswania

Updated 29-May-13 0:26am

v2

Comments

Basmeh Awad 29-May-13 6:21am

if i will write delete first and if table will not present it will raise an exception.."invalid table name"..i already mentioned "in some locations"

Aarti Meswania 29-May-13 6:26am

see updated soulution...

Basmeh Awad 29-May-13 6:43am

tried second one..."procedure successfully completed" but table is still there

Aarti Meswania 29-May-13 7:06am

have you read note?
about case-sensetiveness of tablename?

CHill60 29-May-13 11:38am

If it's not case sensitivity that's the problem then it may be that you are not the owner of the table and the select from user_tables will return 0 ... in which case try using select count(*) into tbl_exist from all_tables where table_name='mytable'; instead