1 Reply Latest reply: Nov 6, 2012 4:41 AM by 972756 RSS

    ORA-01403: no data found, ORA-06512: at line

    A R Khan
      Hi,

      I am getting the below error while running the below script. Please let me know how to fix this error.
      And let me know for any details

      Error:
      SQL> @a4_plsql.sql
      CUSTOM
      FAAP_CHECK
      declare
      ***
      ERROR at line 1:
      ORA-01403: no data found
      ORA-06512: at line 16


      Script:

      set serveroutput ON
      declare
      v_num_rows varchar2(100);
      v_tab_name varchar2 (40);
      v_owner varchar2 (40);
      cursor c1
      IS
      select owner,table_name from dba_tables where PARTITIONED='YES' and table_name in ('FAAP_CHECK');

      begin
      for r1 in c1
      loop
      v_tab_name := r1.table_name;
      v_owner := r1.owner;
      DBMS_OUTPUT.PUT_LINE(v_owner);
      DBMS_OUTPUT.PUT_LINE(v_tab_name);
      select num_rows into v_num_rows from DBA_TAB_PARTITIONS where TABLE_NAME='v_tab_name' and table_OWNER='v_owner' and PARTITION_POSITION=(select max(PARTITION_POSITION)-6 from DBA_TAB_PARTITIONS where TABLE_NAME='v_tab_name' and table_OWNER='v_owner');

      if v_num_rows > 0 then
      DBMS_OUTPUT.PUT_LINE('Partition table v_owner.v_tab_name is reaching max partition. Please look into it ' );
      END IF;
      end loop;
      end;
      /
      spool off;
        • 1. Re: ORA-01403: no data found, ORA-06512: at line
          972756
          Your select at line 16 should be without literal values, i.e.:

          select num_rows into v_num_rows from DBA_TAB_PARTITIONS where TABLE_NAME=v_tab_name and table_OWNER=v_owner and PARTITION_POSITION=(select max(PARTITION_POSITION)-6 from DBA_TAB_PARTITIONS where TABLE_NAME=v_tab_name and table_OWNER=v_owner);


          You have also to be sure that your table has at least 6 partitions.