Re: PL/SQL

From: Alexander V. Silantiev <silantiev_at_bashkortostan.ru>
Date: Wed, 11 Jul 2001 15:45:44 +0600
Message-ID: <9ih76g$bt4$1_at_poikc.bashnet.ru>

There are loopback from PL/SQL block to the host here. Its would be look like this:
PL/SQL procedure p is part of package p. package p have a initialized stage variable p.s p.p is called several times.
first stage: init call

 p.p is called
 p.p get the input params if needed
 p.p check the status of package stage variable p.s and determine is it the

first call
 p.p set the stage status in the status variable of package describing first call was made
 p return info about if user action is required  if answer from user is required, in question variable (out varchar2) will be
 the text of question
 control switch to the host
second stage:
 process that called the p analize loopback  analyze p.s
 ask user if needed
 do second call of p and pass user's answer to the p by means of answer variable (in varchar2 may be)
 p.p check the status of package stage variable p.s and determine is it the second call

 p.p analize answer
 p.p do the work
 p.p set the stage status in the status variable of package to initial value

(or to the next stage)
return to the first/next stage if needed

Daniel A. Morgan <Daniel.Morgan_at_attws.com> ñîîáùèë â íîâîñòÿõ ñëåäóþùåå:3B4B1A84.AC5DDD30_at_attws.com...
> Nono EL wrote:
>
> > 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
>
> PL/SQL in procedures is not interactive.
>
> And DBMS_OUTPUT is just that ... output ... not input.
>
> Daniel A. Morgan
>
Received on Wed Jul 11 2001 - 11:45:44 CEST