oracle - Oracle11g ORA-01403 No data found using Select Into -

i quite new oracle , have issue have been struggelig hours.

sample:

 create table accounts (id number(10),balance number(16,3), status varchar2(50),owner_id number(10));  create table transactions (id number(10),amount number(16,3), trxn_date date, account_id number(10));  create table owner (id number(10), firstname varchar2(50),lastname varchar2(50));   insert accounts(id,balance,status,owner_id) values (1,1000,'open',10);  insert accounts(id,balance,status,owner_id) values (2,5000,'closed',11);  insert accounts(id,balance,status,owner_id) values (3,1000,'open',12);  insert accounts(id,balance,status,owner_id) values (4,5000,'closed',13);  insert accounts(id,balance,status,owner_id) values (5,1000,'open',14);  insert accounts(id,balance,status,owner_id) values (6,5000,'closed',15);  insert accounts(id,balance,status,owner_id) values (7,1000,'open',16);  insert accounts(id,balance,status,owner_id) values (8,5000,'closed',17);  insert accounts(id,balance,status,owner_id) values (9,1000,'open',18);  insert accounts(id,balance,status,owner_id) values (10,5000,'closed',19);  insert accounts(id,balance,status,owner_id) values (11,1000,'open',20);  insert accounts(id,balance,status,owner_id) values (12,5000,'closed',21);   insert owner(id,firstname,lastname) values (10,'john','test1');  insert owner(id,firstname,lastname) values (11,'john','test2');  insert owner(id,firstname,lastname) values (10,'john','test3');  insert owner(id,firstname,lastname) values (11,'john','test4');  insert owner(id,firstname,lastname) values (10,'john','test5');  insert owner(id,firstname,lastname) values (11,'john','test6');  insert owner(id,firstname,lastname) values (10,'john','test7');  insert owner(id,firstname,lastname) values (11,'john','test8');  insert owner(id,firstname,lastname) values (10,'john','test9');  insert owner(id,firstname,lastname) values (11,'john','test10');  insert owner(id,firstname,lastname) values (10,'john','test11');  insert owner(id,firstname,lastname) values (11,'john','test12');   insert transactions(id,amount,trxn_date,account_id) values (1,10,'02-feb-2015',5);  insert transactions(id,amount,trxn_date,account_id) values (2,10,'02-apr-2015',5);  insert transactions(id,amount,trxn_date,account_id) values (3,10,'02-jun-2015',5);  insert transactions(id,amount,trxn_date,account_id) values (4,10,'02-aug-2015',5);  insert transactions(id,amount,trxn_date,account_id) values (5,10,'02-feb-2015',2);  insert transactions(id,amount,trxn_date,account_id) values (6,10,'02-apr-2015',2);  insert transactions(id,amount,trxn_date,account_id) values (7,10,'02-jun-2015',2);  insert transactions(id,amount,trxn_date,account_id) values (8,10,'02-aug-2015',2);      

data check:

   select unique(account_id) accounts  inner join owner b on b.id=a.owner_id   inner join transactions on i.account_id=a.id    i.trxn_date between '01-feb-2015' , '01-jul-2015'   , a.status='closed'  , a.balance=5000;/*1 row returned*/ 

the loop must exit @ first id returned

   declare    l_newdate date:='01-feb-2015';    l_olddate date:='01-jul-2015';    l_pid number(10);   begin    in (select account_id transactions     trxn_date between l_newdate , l_olddate)     loop     select id l_pid     (select b.id accounts     inner join owner b on a.owner_id = b.id    a.status = 'closed' , a.balance = 5000 , a.id=i.account_id)    rownum < 2;    dbms_output.put_line(l_pid);    exit;     end loop;    end;   ora-01403: no data found   ora-06512: @ line 12 

i fail understand why no data found when data check above states otherwise.

regards j. olsen

like say, data check query:

select unique(account_id)   accounts  inner join owner b on b.id=a.owner_id   inner join transactions on i.account_id=a.id  i.trxn_date between '01-feb-2015' , '01-jul-2015'     , a.status='closed'    , a.balance=5000; 

... returns single row single account_id value of 2.

but then, pl/sql code splits logic in 2 queries. query loop on is:

select account_id   transactions   trxn_date between '01-feb-2015' , '01-jul-2015' 

and, when run it, returns:

5 5 5 2 2 2 

now above's order not guaranteed, don't have order by clause. if results in same order me, first loop iteration execute next query using 5 input:

select *   accounts   inner join owner b on a.owner_id = b.id  a.status = 'closed'    , a.balance = 5000    , a.id = 5 

... doesn't return data, why error.

if have been lucky enough have started value of 2:

select *   accounts   inner join owner b on a.owner_id = b.id  a.status = 'closed'    , a.balance = 5000    , a.id = 2 

... have worked expected.

i wish recommend proper solution, don't understand trying do. feels shouldn't need pl/sql loops want. simple query should sufficient. data check query seems start.

edit

for it's worth, think more straight forward way of doing exact same thing intending (no loops):

declare    l_newdate date:='01-feb-2015';    l_olddate date:='01-jul-2015';    l_pid number(10);  begin    select o.id l_pid     transactions t     join accounts       on a.id = t.account_id      , a.status = 'closed'      , a.balance = 5000     join owner o       on o.id = a.owner_id    t.trxn_date between l_newdate , l_olddate      , rownum < 2;    dbms_output.put_line(l_pid); end;