Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: [PL/SQL] DataBASE Interaction with PL/SQL
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
You answered your question in your very first sentence when you wrote: "I know that PL/SQL does't accept any user inputs."
You need a front-end tool to do this.
Quite frankly DBMS_OUTPUT is intended for debugging, not an application front-end. You need to rethink what you are trying to do.
-- Daniel A. Morgan http://www.psoug.org damorgan_at_x.washington.edu (replace x with u to respond)Received on Mon Jun 20 2005 - 08:28:30 CDT