OraFAQ Forum: Server Administration » DELETE MULTIPLE TABLES FROM QUERY RESULT

Home » RDBMS Server » Server Administration » DELETE MULTIPLE TABLES FROM QUERY RESULT

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

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.

Report message to a moderator

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');

Report message to a moderator

Re: DELETE MULTIPLE TABLES FROM QUERY RESULT [message #373375 is a reply to message #373368]

Fri, 13 April 2001 09:20

badjuju
Messages: 4
Registered: April 2001

Junior Member

Actually, upper('&1') will not work because the user will have to manually enter the variables. I need a script that will process batch deletions.

Report message to a moderator

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;
/

Report message to a moderator

Previous Topic:	Count broken down By month. URGENT!!!!!!!!!!!!!!!!!!!!!!!!!
Next Topic:	loading hard returns in text fields

Goto Forum:

-=] Back to Top [=-

[ Syndicate this forum (XML) ] [

]

Current Time: Mon Dec 23 09:37:27 CST 2024