PL/SQL and SQL plus ...

Hi everybody !

After two hours to search in Google Database, i still need help for my PL/SQL block :
I want to write a script who can , with a confirmation, delete all the table sfor the user who execute this script. I have permission to create procedure, and users have permissions to execute it. I have already written a procedure to "drop_table()" using the dbms_sql package.

here is my script :

set serveroutput on
set verify off
clear screen
DECLARE
 Vtablename varchar2(30);
 answer varchar2(1);

CURSOR c1 IS SELECT table_name FROM user_tables;

BEGIN
 DBMS_OUTPUT.ENABLE(20000);
 OPEN c1;
 LOOP FETCH c1 INTO Vtablename;
 EXIT WHEN(c1%NOTFOUND);
 DBMS_OUTPUT.PUT_LINE('The Table'||Vtablename||'is present on your account');
 DBMS_OUTPUT.PUT_LINE('Delete it ? (y/n)');

 /* here is the problem ... how read the answer of the user ????*/  

 if answer='y' then drop_table(Vtablename);  END IF
 END LOOP;
 CLOSE c1;
END; If i write a DBMS_SQL script to use "accept answer prompt "something"", the question is asked one time ... so...

Thank you for your answers ...
And apologize for my english :)

El Nono Received on Tue Jul 10 2001 - 10:16:38 CEST