| DELETE MULTIPLE TABLES FROM QUERY RESULT [message #373367] |
Thu, 12 April 2001 17:04  |
badjuju
Messages: 4
Registered: April 2001
|
Junior Member |
|
|
I need to qrite a drop script that will drop multiple tables based on the data in a query. There can be as many as 1000+ tables, so I do not want to have the user manually change anything.
I have a query (QRYRSLT) that gives me all the usernames and tables that I want to drop. The query result is (let's just assume we have 1000+ rows):
Let's call this Query QRYRSLT.
USERID QRYNM
-------- -------
USER10 QRY10
USER1 QRY1
USER2 QRY2
USER3 QRY3
USER4 QRY4
USER5 QRY5
USER6 QRY6
USER7 QRY7
USER8 QRY8
USER9 QRY9
USER1 QRY11
This means I have all these tables named QRY1, QRY2, etc. Now I want to write a drop script that will drop all these tables.
I need to write a drop script that will accept QRYNM as a variable and drop all those tables.
Here are a few ways I think that could be done, but I know there's an error in the statements.
OPTION 1:
DROP TABLE WHERE QRYNM IN (SELECT QRYNM FROM QRYRSLT) AND USERID IN (SELECT USERID FROM QRYRSLT);
OR
OPTION 2:
DROP TABLE :1 WHERE QRYNM IN (SELECT QRYNM FROM QRYRSLT) AND USERID IN (SELECT USERID FROM QRYRSLT);
What I need is a way to specify the tablename variable that way I can have just one statement that will drop the tables.
OR (sorry about the OR's)
OPTION 3:
How can I automatically generate a multiple drop statement that will read the tablename and the username from QRYRSLT.
Thanks.
|
|
|
|
| Re: DELETE MULTIPLE TABLES FROM QUERY RESULT [message #373368 is a reply to message #373367] |
Thu, 12 April 2001 17:27  |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
would this work for you?
in sqlplus...
spool drop_tables.sql
select 'drop table '||userid||'.'||qrynm||';' from qryrslt;
spool off
then just run drop_tables.sql
you specify it should take QRYNM, if so...
select 'drop table '||userid||'.'||qrynm||';' from qryrslt where qrynm = upper('&1');
|
|
|
|
|
|
| Re: DELETE MULTIPLE TABLES FROM QUERY RESULT [message #373391 is a reply to message #373375] |
Fri, 13 April 2001 12:09 |
Andrew again...
Messages: 270
Registered: July 2000
|
Senior Member |
|
|
in 8i in sqlplus...
set serveroutput on size 1000000
declare
cursor c_QRYRSLT is select USERID, QRYNM from ...;
stmt varchar2(100);
begin
for rec in c_QRYRSLT loop
stmt := 'drop table '||rec.USERID||'.'||rec.QRYNM;
DBMS_OUTPUT.PUT_LINE ( stmt );
-- uncomment when ready!
--execute immediate (stmt);
end loop;
exception
when others then
raise_application_error(-20501, 'ERROR: Unable to drop table.', true);
end;
/
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
