1 Reply Latest reply on Apr 11, 2009 8:01 AM by 584412

    ORA-01403: no data found error hile inserting into a table

    596585
      Hi,

      I am populating a PL/SQL table(Array) with the data. From that array inserting into a Actual table. Before inserting am checking array count. Array count is greater than 0. But I am getting "ORA-01403: no data found" exception. I am not getting why this error is displaying.

      Please someone help me. Below is the code.
      CREATE OR REPLACE PROCEDURE XXDL_TEST_INSERT
      IS
      BEGIN
         IF  t_array.count> 0 THEN
          FND_FILE.PUT_LINE (FND_FILE.LOG, t_array.count);
          FOR i IN t_array.FIRST..t_array.LAST
          LOOP
              INSERT INTO XXDL_TEST
              VALUES(t_array(i).name )
        END LOOP;
       END IF;
       EXCEPTION 
        WHEN OTHERS THEN
          FND_FILE.PUT_LINE (FND_FILE.LOG,SQLERRM);
      END;
      Thanks
      Alaka
        • 1. Re: ORA-01403: no data found error hile inserting into a table
          584412
          It looks like your Array is sparsely populated:
          SQL> DECLARE
            2    TYPE l_tab_type IS TABLE OF VARCHAR2(10)
            3      INDEX BY BINARY_INTEGER;
            4    l_tab l_tab_type;
            5  BEGIN
            6    l_tab(1) := 'A';
            7    l_tab(2) := 'B';
            8    l_tab(3) := 'C';
            9    FOR i IN l_tab.FIRST .. l_tab.LAST LOOP
           10      dbms_output.put_line(l_tab(i));
           11    END LOOP;
           12  END;
           13  /
          A
          B
          C
          
          PL/SQL procedure successfully completed.
          
          SQL>
          SQL> DECLARE
            2    TYPE l_tab_type IS TABLE OF VARCHAR2(10)
            3      INDEX BY BINARY_INTEGER;
            4    l_tab l_tab_type;
            5  BEGIN
            6    l_tab(1) := 'A';
            7    l_tab(3) := 'B';
            8    l_tab(4) := 'C';
            9    FOR i IN l_tab.FIRST .. l_tab.LAST LOOP
           10      dbms_output.put_line(l_tab(i));
           11    END LOOP;
           12  END;
           13  /
          A
          DECLARE
          *
          ERROR at line 1:
          ORA-01403: no data found
          ORA-06512: at line 10
          Edited by: JS1 on Apr 11, 2009 8:59 AM
          You should also be using the FORALL syntax with INDICES OF. Check out The Docs