Re: PL/SQL Stored Procedure Question

leangl_at_ohsu.edu wrote:
>Have anyone encountered the following problem:
>

 [examples of working procedures snipped]
>
>The following create procedure is not working.
>
>SQL> create procedure shwemp (emp_id number) is
> 2 begin
> 3 select empno, ename from emp where empno = emp_id;
> 4 end;
> 5 /
>
>Warning: Procedure created with compilation errors.
>
>SQL>
>
>Your help is greatly appreciated.
>
>-Lyny

The problem is that procedures can't be used that way. If it could do a select like you have it wouldn't know what to do with the values once it selected them. Remember the procedure might just as easily be called from a C program as from sqlplus. An example of a procedure (actually it would have to be a function) to do this would be:

create function shwemp(emp_id number) return varchar2 is

   r_empno emp.empno%type;
   r_ename emp.ename%type;
begin

   select empno into r_empno, ename into r_ename from emp

      where empno = empid;
   return 'empno = '||r_empno||', ename = '||r_ename; end;

Then to execute it you would need to:

select shwemp(<id>) from dual;

Also, in case you didn't know, when it says "Procedure created with compilation errors." you can use the command "show errors" to see what the errors are.

Good luck

Ken Denny
Insight Industries, Inc.
RTP, NC The usual disclaimer. Received on Mon Jan 22 1996 - 00:00:00 CET