Re: [PL/SQL] DataBASE Interaction with PL/SQL

From: Rauf Sarwar <rs_arwar_at_hotmail.com>
Date: 20 Jun 2005 08:15:35 -0700
Message-ID: <1119280535.100920.217720@g14g2000cwa.googlegroups.com>

baka wrote:
> Hello Oracle Guru's,
>
> I know that PL/SQL does't accept any user inputs.
> but i need the following functionality like And Please do not ask the
> reason for.
> (My question is coming at the end of this part)
>
> Algorithm
>
> 1.Execute a procedure
> 2. accept some input parameters
> 3. check for some condition
> 4. pass those accepted parameter to the Dynamic SQL/cursor etc....
> 5. Print the output using DBMS.Package
> 6.The END
>
> Here is a sample procedure, but this procedure
> will accept parameters only when a run the entire procedure (first
> time)
>
> -------------------------------------------
> create or replace procedure test as
>
> t_empno varchar2(10) := '&empno';
> t_ename varchar2(100);
> begin
> Select empname into t_ename
> from empmaster
> Where empno=t_empno;
> dbms_output.put_line('Name: '|| t_ename);
> -- p(t_ename) ; --thanks for asktom.oracle.com
> end;
>
> ----------------------------------------------
>
> Out/put (Image only some i/o is translated from japaneseto english)
>
> empno....?: E1234567
> old 3: v_empno varchar2(10) := '&empno';
> new 3: v_empno varchar2(10) := 'E1234567';
>
> Procedure created
> SQL> exec test
> nameofthe employ here
> PL/SQLcompleted succ....
>
> -------------------------------------------------
>
>
>
> My question is
>
> How can i write a PL/SQL to perform the above algorithm
> i.e When i say EXEC test
> the following screnn should appear
>
> empno....?: E1234567
> old 3: v_empno varchar2(10) := '&empno';
> new 3: v_empno varchar2(10) := 'E1234567';
>
>
> Thanks in Advance

& is treated as any other character outside of SQL*PLus (GUI and command line). Even within SQL*Plus, it can be switched on/off using "set define off/&". You should declare an IN parameter to your stored procedure if you want to execute it with different values. Stored code remains the same as compiled, however, your input value could differ at runtime. e.g.

SQL> set define off
SQL> set serverout on
SQL> create or replace procedure foo

  2 is
  3 begin
  4 dbms_output.put_line('&value is just test');   5 end foo;
  6 /

Procedure created.

SQL> execute foo;
&value is just test

PL/SQL procedure successfully completed.

SQL> create or replace procedure foo (
  2 input_ in varchar2)
  3 is
  4 begin
  5 dbms_output.put_line(input_);
  6 end foo;
  7 /

Procedure created.

SQL> execute foo('&value is just test'); &value is just test

PL/SQL procedure successfully completed.

SQL> set define &
SQL> execute foo('&value is just test'); Enter value for value: This
This is just test

PL/SQL procedure successfully completed.

SQL> drop procedure foo;

Procedure dropped.

SQL> http://tahiti.oracle.com is your best bet for detailed answers.

Regards
/Rauf Received on Mon Jun 20 2005 - 10:15:35 CDT