Home » SQL & PL/SQL » SQL & PL/SQL » droping more than one table using pl/sql
droping more than one table using pl/sql [message #250981] Thu, 12 July 2007 02:08 Go to next message
orafacjublu
Messages: 95
Registered: May 2006
Location: KOLKATA
Member
can any body solve why this block of code is sometimes running fine and other times showing invalid Drop option error ORA-00950

BEGIN
FOR rec in (select table_name from user_tables where table_name like 'T%')loop
execute immediate 'drop table'||rec.table_name||'cascade constraint';
end loop;
end;
Re: droping more than one table using pl/sql [message #251007 is a reply to message #250981] Thu, 12 July 2007 03:11 Go to previous messageGo to next message
Frank
Messages: 7880
Registered: March 2000
Senior Member
This block will only run without an error if no tables are found.
Check the actual sql statement that gets executed, you should be able to see the error you made then.
Re: droping more than one table using pl/sql [message #251009 is a reply to message #250981] Thu, 12 July 2007 03:16 Go to previous messageGo to next message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Please read and follow How to format your posts and How to get a quick answer to your question: TIPS AND TRICKS
Make sure that lines of code do not exceed 80 or 100 characters when you format.
Please always post your Oracle version (4 decimals).

Missing a space around the table name?
Weird table name (BIN$ table for instance...)?

Regards
Michel
Re: droping more than one table using pl/sql [message #251074 is a reply to message #250981] Thu, 12 July 2007 06:29 Go to previous messageGo to next message
jheronimus
Messages: 11
Registered: March 2007
Location: The Netherlands, Groninge...
Junior Member
I think you must add a few spaces in de dynamic sql statement

your statement:
execute immediate 'drop table'||rec.table_name||'cascade constraint';


change it in:
execute immediate 'drop table '||rec.table_name||' cascade constraint';


Kind regards,
Jeroen de Jong
OSA it
The Netherlands
Re: droping more than one table using pl/sql [message #251077 is a reply to message #251074] Thu, 12 July 2007 06:32 Go to previous message
Michel Cadot
Messages: 64103
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
Nice to see you agree with me but it would be better if you followed the links I posted and formatted your post.

Regards
Michel
Previous Topic: an interesting query
Next Topic: 2 CURSORS - access 1 cursor for processing???
Goto Forum:
  


Current Time: Sun Dec 04 17:06:04 CST 2016

Total time taken to generate the page: 0.17893 seconds