Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Is it possible to accept input from a user in PL/SQL

Re: Is it possible to accept input from a user in PL/SQL

From: Mark D Powell <mark.powell_at_eds.com>
Date: 8 Mar 2002 12:07:43 -0800
Message-ID: <178d2795.0203081207.7a48a511@posting.google.com>


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 ;
 39 end ;
 40 /
Enter value for owner: mpowel01
Enter value for tbl_name: emp
old  29: v_return := test_tbl_exists('&OWNER','&TBL_NAME')     ;
new  29: v_return := test_tbl_exists('mpowel01','emp')     ;
Found EMP
Did not find XXX

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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US