Re: PLS-00201 when compiling function with cursor as parameter

From: <wilsonr_at_logica.com>
Date: 23 Jan 2006 02:49:33 -0800
Message-ID: <1138013373.763363.316950@g49g2000cwa.googlegroups.com>

Many apologies, it was a late night on a looong friday :) i trimmed the code to make it clearer but i guess i trimmed away too much

1. package with ref cursor created as above
2. attempt to create the following function

CREATE OR REPLACE FUNCTION cursor_function (p_dept_cur IN pkg_test.dept_cur_type) RETURN emp_details_table_type IS

CURSOR emp_cur(p_dept_cd VARCHAR2) IS

   SELECT emp_name
   FROM emp
   WHERE dept_cd = p_dept_cd;

v_separator         VARCHAR2(1) default null;
v_emp_names         VARCHAR2(100);
emp_details_table   emp_details_table_type;
dept_rec            p_dept_cur%ROWTYPE;

BEGIN    LOOP

      FETCH p_dept_cur INTO dept_rec;
      EXIT WHEN p_dept_cur%NOTFOUND;

      v_emp_names := null;
      v_separator := null;

      -- get the employees and build a concatenated string of employees
      FOR emp_rec IN emp_cur(dept_rec.dept_cd) LOOP
         v_emp_names := v_emp_names || v_separator || emp_rec.emp_name;
         v_separator := '-';
      END LOOP;

      -- add row to table
      emp_details_table.extend;
      emp_details_table(emp_details_table.count) :=

emp_details_object_type(dept_cd, v_emp_names);

   END LOOP;    RETURN emp_details_table;

END;
/

result: compile failed with following message "PLS-00201: identifier 'DEPT_CD' must be declared"

> All I see is a likely inappropriate use of a cursor loop.
inappropriate how? Received on Mon Jan 23 2006 - 04:49:33 CST