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

wilsonr_at_logica.com wrote:
> 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.

You seem bound and determined to provide inadequate information so YOYO. I understand you are geting a PLS-00201. But this is your interpretation of what Oracle wrote ... not the actual error message. Further you assume that we can see previous posts ... many of us can not so I have no idea the definition of p_dept_cur. Nor do I know the version.

What is apparent however is that this is convoluted, seemingly without reason, why is it taking a cursor as an input? why is it returning a PL/SQL table? The one thing that is apparent however is that the cursor loop could be replaced with array processing using BULK COLLECT.

-- 
Daniel A. Morgan
http://www.psoug.org
damorgan_at_x.washington.edu
(replace x with u to respond)