Re: How to do create the procedure

Hi Violin,
You are confusing database tables and pl/sql tables. Pl/sql tables are sparse arrays, you can't select from them, and you can't return them in a cursor. The ref cursor can return an emp%rowtype, of course. At first glance this looks like a very cumbersome way to do

open v_emp for select * from emp where sal > 1000

Hopefully I am mistaken!

Best wishes,

--
Sybrand Bakker, Oracle DBA
Violin <violin.hsiao_at_mail.pouchen.com.tw> wrote in message news:3864137b.3992831_at_172.16.7.5...
> Hello,
> I have to create the package and procedure :(Database is Oracle 8.0.5)
>
> Create Or Replace package Pkg_emp as
> Type emp_list is record(
> empno emp.empno%type,
> ename emp.ename%type,
> job emp.job%type,
> sal emp.sal%type);
> Type emp_table Is Table of emp_list index by binary_integer;
> TYPE emp_cur IS REF CURSOR RETURN emp_list;
> End;
> /
>
> CREATE OR REPLACE PROCEDURE pro_emp
> (dept IN NUMBER,v_emp IN OUT Pkg_emp.emp_cur)
> AS
> emp_no emp.empno%type;
> emp_name emp.ename%type;
> emp_job emp.job%type;
> emp_sal emp.sal%type;
>
> emp_tab Pkg_emp.emp_table;
>
> CURSOR C1 IS
> SELECT EMPNO,ENAME,JOB,SAL FROM EMP WHERE DEPTNO = dept;
>
> BEGIN
> OPEN C1;
> LOOP
> FETCH C1 INTO emp_no,emp_name,emp_job,emp_sal;
> EXIT WHEN C1%NOTFOUND;
> /* I have to check my business rule here,then return the result
back. */
> IF emp_sal > 1000 THEN
> emp_tab(C1%ROWCOUNT).empno := emp_no;
> emp_tab(C1%ROWCOUNT).ename := emp_name;
> emp_tab(C1%ROWCOUNT).job := emp_job;
> emp_tab(C1%ROWCOUNT).sal := emp_sal;
> END IF;
> END LOOP;
> CLOSE C1;
>
> OPEN v_emp FOR select * from emp_tab;
> END;
> /
>
> But when compiler in SQL*Plus,having compile errors
> PLS-00950: in this version, PL/SQL tables cannot be used in this SQL
statement
> PLS-00356: 'EMP_TAB' must name a table to which the user has access
>
> First, the procedure will open a cursor.
> When loop in the cursor,procedure has to do business rule checking and
store the
> resultset into pl/sql table.
> Finanlly returns the result sets back,but the error meesage shows I am
wrong.
>
> Have any idea??
> Thank you for any tips.
>
> Violin.
> violin.hsiao_at_mail.pouchen.com.tw
>
Received on Fri Dec 24 1999 - 10:29:07 CST