CODEWITHSUNDEEP
sqloracle-databaseplsqluser-defined-types
user2042156
user2042156

Reputation: 119

While using Table Type data type in PL/SQL getting 'NO DATA FOUND' error

Here defining a table type variable and trying to insert rows into that variable using for loop but unable to insert data . Getting data not found error... Please let me know whats wrong with this cod.

    DECLARE
TYPE T_EMP
IS
  TABLE OF EMP%ROWTYPE INDEX BY BINARY_INTEGER;
  V_EMP T_EMP ;
  V_MIN_EMP EMP.EMPNO%TYPE;
  V_MAX_EMP EMP.EMPNO%TYPE;
BEGIN
  --V_MIN_EMP:=7369;
  --V_MAX_EMP:=7934;
  SELECT MIN(empno) into V_MIN_EMP FROM EMP;
  SELECT MAX(EMPNO) INTO V_MAX_EMP FROM EMP;

  FOR I IN V_MIN_EMP..V_MAX_EMP
  LOOP
    SELECT * INTO V_EMP(I) FROM EMP WHERE EMPNO=I;
  END LOOP;
END;

Upvotes: 0

Views: 1541

Answers (1)

APC
APC

Reputation: 146349

In your code i is an index. It steps through the bounds monotonically. So if your minimum EMPNO is 1234 and your maximum EMPNO is 5678 the values of i will be 1234, 1235, 1236 ... 5676, 5677, 5678.

But that probably is what you want to do, because EMPNO is likely to be a sparse array, with missing numbers. So if you have no row in EMP where EMPNO=1235 your query will throw NO_DATA_FOUND.

What's the solution? Use a bulk collect instead:

select * 
bulk collect into v_emp
from emp ;

That will select all the rows in the table, and it will be more efficient as well.

Upvotes: 4

Related Questions