Re: Oracle stored procs

In article <01bc8596$de5a26d0$c47b6496_at_kosh>, dwetzel1_at_rodalepress.com says...

>Okay, I'm confused... I want to write a stored proc that does something
>similar to this:
>
>create proc show_dept(dept_id number)
>is
>begin
> select emp, job, sal
> from emp
> where deptno= dept_id;
>end;
>

 In other words, I want to run a report that returns any number of rows
>based on inputted data.
>
>It's beginning to look to me like Oracle can only handle sp's that deal
>with one and only one row. That can't be right, I must be doing something
>wrong.

You must define a cursor to hold your return value set, for queries that return more than one row. (It's actually a good idea to always use a cursor for your queries in PL/SQL). Read the app dev manual or get Feurstein's book, but basically

create proc show_dept(dept_id number)
is

CURSOR emp_cur is
  select emp, job, sal from emp
  where deptno = dept_id;

emp_rec emp_cur%ROWTYPE;

BEGIN
  open emp_cur;
  fetch emp_cur into emp_rec;
  WHILE emp_cur%FOUND
  LOOP
-- processing of emp_rec variable, whatever.   fetch emp_cur into emp_rec;
  END;
END; Hope this helps.

-- 
/(o\ Nathan D. Hughes
\o)/ nhughes_at_umich.edu