[PL/SQL] DataBASE Interaction with PL/SQL

From: baka <mesundara_at_hotmail.com>
Date: 19 Jun 2005 23:44:10 -0700
Message-ID: <1119249850.446398.153100@g14g2000cwa.googlegroups.com>

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 Received on Mon Jun 20 2005 - 01:44:10 CDT