pl/sql varray with a cursor

Question

Could someone tell me what is wrong with this code. My aim is to write a procedure which is passed an employee’s name, loads all the names and salaries from the employee table into a VARRAY, and then prints out the names and salaries on screen.

CREATE OR REPLACE PROCEDURE VARRAY_Q2 
(
  PNAME IN VARCHAR2  
, PSAL OUT NUMBER  
) AS 

--declare and create cursor

CURSOR emp_cur IS
SELECT ename,sal
FROM EMP;

  TYPE varray_emp IS VARRAY(14) OF emp_Cur%ROWTYPE;

  --Creating new instance of varray
  x_varray_emp varray_emp := varray_emp();
  v_counter NUMBER := 0;

BEGIN

  x_varray_emp.EXTEND;

  FOR empRecs IN emp_Cur LOOP

  --Insert data into the varray
  x_varray_emp(v_counter) := empRecs;

  dbms_output.put_line(v_counter);

  v_counter := v_counter + 1;
  END LOOP;

  --Loop through the varray and print out all the elements
  FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST 
  LOOP
  dbms_output.put_line(x_varray_emp(i));    
  END LOOP;

END;

1. Why do you pass any parameters into the procedure if they are not used? 2. Why bother with the VARRAY when you can do your operation right in the cursor loop? 3. If you do not see output, you probably want to 'set serveroutput on'. — Glenn, Mar 01 '13 at 21:56
@Glenn I think he is learning how to do it and will use the parameters and varray after he figures this part out. — Matthew Moisen, Mar 01 '13 at 22:05
@Glenn: Yes, I am learning pl/sql and I have to use a varray. — Dave Fisher, Mar 02 '13 at 00:24

Matthew Moisen · Accepted Answer · 2013-03-01T22:41:17.557

First: PL/SQL Collections are not 0 based. That will throw a subscript out of limit error because your counter stared at 0. You also do not need a counter for this solution. If you want to continue to use it, see the first example. But if I were you I would use the second example.

Second, you extended the varray before the loop.That will throw a subscript beyond count error because the varray has only been extended to hold one row. You need to extend it at the beginning of the loop so that upon each iteration the varray is extended.

Thid, you cannot PUT_LINE a whole varray. You have to put_line the elements in the varray. So in this example, rather than dbms_output.put_line(x_varray_emp(i)) use this instead: dbms_output.put_line(x_varray_emp(i).ename || ' makes $' || x_varray_emp(i).sal)

Fourth: if your emp table has more than 14 records in it, your VARRAY(14) will cause a subscript outside of limit error. varrays have a max size set to them (bounded limit), which in your case is 14. In this example, I would have used a regular nested table (TYPE nested_emp IS TABLE OF emp_cur%ROWTYPE) so as to not worry about bounded limits (technically, a nested table has a maximum of 2147483647 aka PLS_INTEGER). If you are willing to use a NT over a VA, use the 3rd solution below instead.

Do the following and it will work.

change your v_counter NUMBER := 0 to v_counter NUMBER := 1;

Change that which is in the BODY to this:

BEGIN
    FOR empRecs IN emp_Cur LOOP
        x_varray_emp.EXTEND;
        --Insert data into the varray 
        x_varray_emp(v_counter) := empRecs;

        dbms_output.put_line(v_counter);

        v_counter := v_counter + 1; 
    END LOOP;
    --Loop through the varray and print out all the elements 
    FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;
END;

You don't need a counter, actually. If you are willing to abandon it, use the COUNT method of the varray instead:

BEGIN


    FOR empRecs IN emp_Cur LOOP
        x_varray_emp.EXTEND;

        --Insert data into the varray 
        x_varray_emp(x_varray_emp.count) := empRecs;

        dbms_output.put_line(x_varray_emp.count);


    END LOOP;

    --Loop through the varray and print out all the elements 
    FOR i IN x_varray_emp.FIRST .. x_varray_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;


END;

I would prefer that you use a nested table for this problem over a varray. IF you are willing, here is the solution:

DECLARE

    CURSOR emp_cur IS SELECT ename,sal FROM EMP;
    TYPE nestedtable_emp IS TABLE OF emp_cur%ROWTYPE;

    x_nestedtable_emp nestedtable_emp := nestedtable_emp();
BEGIN


    FOR empRecs IN emp_Cur LOOP
        x_nestedtable_emp.EXTEND;

        --Insert data into the varray 
        x_nestedtable_emp(x_nestedtable_emp.count) := empRecs;

        dbms_output.put_line(x_nestedtable_emp.count);


    END LOOP;

    --Loop through the varray and print out all the elements 
    FOR i IN x_nestedtable_emp.FIRST .. x_nestedtable_emp.LAST LOOP 
        dbms_output.put_line(x_varray_emp(i).ename || ' Makes $' || x_varray_emp(i).sal);
    END LOOP;


END;

If we're using a nested table BULK COLLECT would be a better approach. But this task doesn't need any sort of collection: a straightforward FOR CURSOR loop is all that is required. Which just demonstrates the uselessness of such homework exercises: they teach the mechanics of the language but not how to use it properly. So all the student actually learns is how to write bad PL/SQL. — APC, Mar 02 '13 at 08:02

pl/sql varray with a cursor

1 Answers1