Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible to accept input from a user in PL/SQL
rkg100_at_erols.com (Ryan Gaffuri) wrote in message news:<6757365f.0203080606.5b10373_at_posting.google.com>...
> I know you can use the SQLPLUS ACCEPT command to input data into a
> substitution variable and pass that to PL/SQL.
>
> However, is it possible to use PL/SQL to prompt the user for input and
> then use that input in PL/SQL?
Ryan, I will not bore you with the entire script but:
28 --
29 v_return := test_tbl_exists('&OWNER','&TBL_NAME') ;
30 if v_return
31 then dbms_output.put_line('Found EMP ') ; 32 else dbms_output.put_line('Did not find EMP ') ; 33 end if ; 34 v_return := test_tbl_exists('MPOWEL01','XXX') ; 35 if v_return 36 then dbms_output.put_line('Found XXX ') ; 37 else dbms_output.put_line('Did not find XXX ') ;38 end if ;
old 29: v_return := test_tbl_exists('&OWNER','&TBL_NAME') ; new 29: v_return := test_tbl_exists('mpowel01','emp') ;Found EMP
PL/SQL procedure successfully completed.
It would appear the answer is yes you can use SQLPLUS to pass a variable to plsql in certain circumstances and with some limitiations. You could have used an accept statement above and then just referenced the variables in the pl/sql call. One of the limitations is you can not imbed the &variable in stored procedures, but you call the stored procedure using an &variable.
HTH -- Mark D Powell -- Received on Fri Mar 08 2002 - 14:07:43 CST