This discussion is archived
1 Reply Latest reply: Nov 6, 2012 2:41 AM by 972756 RSS

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

640156 Newbie
Currently Being Moderated
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 Newbie
    Currently Being Moderated
    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.

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points