CODEWITHSUNDEEP
oracle-databasecollectionsplsql
Gaurav Soni
Gaurav Soni

Reputation: 6346

no_data_found exception in oracle pl/sql while using collection

DECLARE
   TYPE EmpList IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;

   temp    SYS_REFCURSOR;
   v_temp  varchar2(50);
   v_emp   EmpList;
BEGIN
   v_emp (1) := 'gaurav';
   v_emp (2) := 'manu';

   open temp for select v_emp(level) from dual connect by level<=2;
   loop
    fetch temp into v_temp;
     exit when temp%notfound;
       DBMS_OUTPUT.put_line (v_temp);
    end loop;
   close temp;

--the below part works, then why not the above part dint works
 for i in v_emp.first..v_emp.last
  loop
      dbms_output.put_line(v_emp(i));
  end loop;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Can anyone please tell me why this collection dint work here??,It is giving me no_data_found exception.

Upvotes: 1

Views: 2866

Answers (2)

user2001117
user2001117

Reputation: 3777

Try This:

DECLARE
   TYPE EmpList IS TABLE OF varchar2(50) INDEX BY BINARY_INTEGER;

   temp    SYS_REFCURSOR;
   v_temp  varchar2(50);
   v_emp   EmpList;
BEGIN
   v_emp (0) := 'gaurav';
   v_emp (1) := 'manu';

   open temp for select v_emp(level) from dual connect by level<=2;
   loop
    fetch temp into v_temp;
        exit when temp%notfound;
       DBMS_OUTPUT.put_line ('v_temp' || v_temp);
    end loop;
   close temp;

--the below part works, then why not the above part dint works
 for i in v_emp.first..v_emp.last
  loop
      dbms_output.put_line(v_emp(i));
  end loop;

EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (SQLERRM);
END;

Upvotes: 1

DazzaL
DazzaL

Reputation: 21993

the reason for failure is that 

v_emp(level)

is evaluated (as its a variable) at runtime so would actually evaluate on all rows to v_emp(0);

you could see this if you changed your array to 

v_emp (0) := 'gaurav';
v_emp (1) := 'manu';

the proper way (in case you were not aware) is:

create TYPE EmpList IS TABLE OF varchar2(50);
/

and then :

v_emp := EmpList('gaurav', 'manu');
open temp for select column_value from table(v_emp);

Upvotes: 2

Related Questions