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

Question

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;

Alex Poole · Accepted Answer · 2013-07-11 14:30:12Z

4

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.

edited Jul 11, 2013 at 14:30

Alex Poole

192k11 gold badges198 silver badges349 bronze badges

answered Jul 11, 2013 at 13:48

APC

147k20 gold badges180 silver badges289 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

APC Over a year ago

Thanks Alex, I botched the edit after reading the question :)

user2042156 Over a year ago

@Alex Poole AFTER UR COMMENT I CHANGED MY CODE BUT AN ERROR IS THROWN SAYING TOO MANY VALUES

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 * BULK COLLECT INTO V_EMP FROM SCOTT.emp; END;

Alex Poole Over a year ago

@user2042156 - as far as I can see that's only possible if you're seeing two EMP tables, which would imply you are connected as someone other than SCOTT; so T_EMP is using your schema's EMP table, but you're selecting from SCOTT's, and they must differ. Does it work if you make it TABLE OF SCOTT.EMP%ROWTYPE, or remove the SCOTT. from the SELECT?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest