Hi ,
Getting no data found error,
Could any one suggest some code changes , it is really helpful.
It is printing the first value from 1st iteration , but failing from 2nd iteration.
set serveroutput on
declare
v_item varchar2(25);
l_count number;
begin
l_count :=1;
for i in 1..10 loop
select item into v_item from
( select il.*,dense_rank() over (partition by il.ITEM_PARENT order by il.ITEM,il.ITEM_PARENT )rnk from (
select il.item,il.item_parent from das_wv_item_loc il,das_wv_item_master im where
il.item=im.item and il.item_parent=im.item_parent and
DEPT in (select DEPT from das_wv_deps where GROUP_NO
in (select GROUP_NO from das_wv_groups where division in (select DIVISION from das_wv_division where DIVISION='2')))
and il.item_parent is not null and pack_ind='N' and loc='10028'
)il)
where rnk=1 and rownum=l_count;
dbms_output.put_line(v_item);
l_count :=l_count+1;
end loop;
end;
OUTPUT :
302756913
Error starting at line : 142 in command -
declare
v_item varchar2(25);
l_count number;
begin
l_count :=1;
for i in 1..10 loop
select item into v_item from
( select il.*,dense_rank() over (partition by il.ITEM_PARENT order by il.ITEM,il.ITEM_PARENT )rnk from (
select il.item,il.item_parent from das_wv_item_loc il,das_wv_item_master im where
il.item=im.item and il.item_parent=im.item_parent and
DEPT in (select DEPT from das_wv_deps where GROUP_NO
in (select GROUP_NO from das_wv_groups where division in (select DIVISION from das_wv_division where DIVISION='2')))
and il.item_parent is not null and pack_ind='N' and loc='10028'
)il)
where rnk=1 and rownum=l_count;
dbms_output.put_line(v_item);
l_count :=l_count+1;
end loop;
end;
Error report -
ORA-01403: no data found
ORA-06512: at line 7
01403. 00000 - "no data found"
*Cause: No data was found from the objects.
*Action: There was no data from the objects which may be due to end of fetch.