How to do create the procedure

From: Violin <violin.hsiao_at_mail.pouchen.com.tw>
Date: 24 Dec 1999 06:56:52 GMT
Message-ID: <3864137b.3992831@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 - 00:56:52 CST