4 Replies Latest reply on Feb 18, 2013 2:47 AM by sgudipudi

    ORA-01403: no data found

    983397
      Would someone please tell me where is my mistake here...

      =====================================
      SET SERVEROUTPUT ON
      DECLARE
      TYPE dept_table_type IS TABLE OF
      departments%ROWTYPE INDEX BY PLS_INTEGER;
      my_dept_table dept_table_type;
      max_count NUMBER(4,0):= 30;
      BEGIN
      FOR i IN 10..max_count
      LOOP
      SELECT * INTO my_dept_table(i) FROM departments
      WHERE department_id = i;
      END LOOP;
      FOR i IN my_dept_table.FIRST..my_dept_table.LAST
      LOOP
      DBMS_OUTPUT.PUT_LINE(my_dept_table(i).department_name);
      END LOOP;
      END;
      /
      =====================================

      I am getting the error...
      ===============
      Error report:
      ORA-01403: no data found
      ORA-06512: at line 9
      01403. 00000 - "no data found"
      *Cause:   
      *Action:
      ===============


      Thanks for your help!
        • 1. Re: ORA-01403: no data found
          sb92075
          post results from following SQL

          SELECT DEPARTMENT_ID, COUNT(*) FROM departments GROUP BY DEPARTMENT_ID ORDER BY 1;

          How do I ask a question on the forums?
          SQL and PL/SQL FAQ
          • 2. Re: ORA-01403: no data found
            ranit B
            Hi Ghazal,
            Ghazal-OCA wrote:
            FOR i IN 10..max_count
            LOOP
            SELECT * INTO my_dept_table(i) FROM departments
            WHERE department_id = i;
            END LOOP;
            Here, LOOP runs from i=10 to i=30 i.e. my_dept_table(10) to my_dept_table(30)
            FOR i IN my_dept_table.FIRST..my_dept_table.LAST
            LOOP
            DBMS_OUTPUT.PUT_LINE(my_dept_table(i).department_name);
            END LOOP;
            END;
            /
            But, here my_dept_table.FIRST will be the first element I guess ,and since my_dept_table(1) to my_dept_table(9) is not populated...
            my_dept_table(1).department_name fails.

            Please give print statements inside both the FOR Loops to check the value of counter 'i' for each iteration.
            i.e. DBMS_OUTPUT.PUT_LINE(i);

            HTH
            Ranit B.
            • 3. Re: ORA-01403: no data found
              SamFisher
              Hi Ghazal,
              BEGIN
              FOR i IN 10..max_count
              LOOP
              SELECT * INTO my_dept_table(i) FROM departments
              WHERE department_id = i;
              END LOOP;
              You're looping through 10 to 30.
              Like 10, 11, 12, 13... 30
              In Dept table you have deptno in 10, 20, 30 & 40.
              You won't find data for 11, 12, 13..

              For ex:
              If i =10 then
              SELECT * INTO my_dept_table(i) FROM departments
              WHERE department_id = 10;
              You find the data here.

              If i = 11 then
              SELECT * INTO my_dept_table(i) FROM departments
              WHERE department_id = 11;
              This is where you get no_data_found exception
              • 4. Re: ORA-01403: no data found
                sgudipudi
                associative array(my_dept_table) subscript has values from 10 to 30.There are no values from 1 to 10 and you are trying to fetch values from my_dept_table(1....10) since there is no data in your array you are ending up with ORA-01403.

                Try this: I am using Nested table instead of Associative arrays.

                 DECLARE
                TYPE dept_table_type IS TABLE OF departments%ROWTYPE;
                my_dept_table dept_table_type:=dept_table_type();
                max_count NUMBER(4,0):= 30;
                BEGIN
                SELECT * BULK COLLECT INTO my_dept_table FROM departments
                WHERE department_ID between 10 and max_count;
                FOR i IN  my_dept_table.First.. my_dept_table.LAST 
                LOOP 
                DBMS_OUTPUT.PUT_LINE(my_dept_table(i).department_name);
                exit when my_dept_table.COUNT=0;
                END LOOP;
                END;
                Edited by: Sg049 on Feb 17, 2013 6:45 PM