Re: PL/SQL and SQL plus ...
From: Robert Fazio <rfazio_at_home.com.nospam>
Date: Tue, 10 Jul 2001 12:07:09 GMT
Message-ID: <NdC27.12124$Y6.3745369_at_news1.rdc2.pa.home.com>
Date: Tue, 10 Jul 2001 12:07:09 GMT
Message-ID: <NdC27.12124$Y6.3745369_at_news1.rdc2.pa.home.com>
Inside of the actual procedure it can't be done. It can be done outside of the actual procedure in SQLPLUS using prompt and &answer syntax. You can look that up in the Sqlplus User Guide.
-- Robert Fazio Senior Technical Analyst dbabob_at_yahoo.com "Nono EL" <arthoar_at_hotmail.com> wrote in message news:ebc3dfb2.0107100016.7a5c1ff6_at_posting.google.com...Received on Tue Jul 10 2001 - 14:07:09 CEST
> 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