Urgent:exact fetch returns more than requested number of rows

hi
i am using this query in my function in oracle
---
BEGIN
   cursor c1 is
   select decode(operation,'MinFinal',test_id), decode(operation,'Final',test_id) into testid1,testid2
   from test.test_data_v7
   where operation in ('MinFinal','Final')
   and (pf = 1 or pf = 4) and serial = '' || in_serialno || '';

when i run the function i get this error
ORA-01422: exact fetch returns more than requested number of rows
do i need to use cursor then? this query is supposed to return 2 records if match found.
i
LVL 1
samir25Asked:
Who is Participating?
 
Naveen KumarConnect With a Mentor Production Manager / Application Support ManagerCommented:
if you want non pipelined version of code.. it is something like the below :

CREATE OR REPLACE FUNCTION FORMAT(in_serialno varchar2)
RETURN LINES_TABLE IS
  cursor c1 is
   select decode(operation,'MinFinal',test_id) test_id1, decode(operation,'Final',test_id) test_id2    from test.test_data_v7
   where operation in ('MinFinal','Final')
   and (pf = 1 or pf = 4) and serial = '' || in_serialno || '';
v_tab LINES_TABLE := LINES_TABLE();
...
..
BEGIN
 for r1 in c1
loop
--   dbms_output.put_line('testid ' || r1.testID1);
--   dbms_output.put_line('testid ' || r1.testID2);
   v_tab.extend;
   v_tab(v_tab.last) := r1.test_id1;
   v_tab.extend;
   v_tab(v_tab.last) := r1.test_id2;
end loop;
return v_tab;
end;
/
0
 
dqmqCommented:
Remove this line:

into testid1,testid2
0
 
samir25Author Commented:
then how do i refer it..can u explian me more thanks
0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
Naveen KumarProduction Manager / Application Support ManagerCommented:
firstly, please paste your full code for a better understanding.

From the above, if you have more than one record returned in the select then either you should have bulk collect or you should use a cursor and iterate through the two records to fetch them into variable as shown below.

declare

   cursor c1 is
   select decode(operation,'MinFinal',test_id) test_id1, decode(operation,'Final',test_id) test_id2    from test.test_data_v7
   where operation in ('MinFinal','Final')
   and (pf = 1 or pf = 4) and serial = '' || in_serialno || '';

test_id1 varchar2(10); -- use number if test_id1 is number data type, also note i have used 10
test_id2 varchar2(10); -- use number if test_id1 is number data type

BEGIN

for a in c1
loop

    dbms_output.put_line('id1:'||test_id1);
    dbms_output.put_line('id2:'||test_id2);
    -- the above will just the variables fetched.. just do whatever processing you want
    -- there instead of printing them.

end loop;
end;
/
   
0
 
dqmqCommented:
>then how do i refer it..can u explian me more thanks

Open the cursor, then fetch from the cursor into the variables.  Usually, you do it inside a loop because of the multiple rows.

0
 
samir25Author Commented:
here is what i did
CREATE OR REPLACE FUNCTION FORMAT(in_serialno varchar2)
RETURN LINES_TABLE PIPELINED IS
  cursor c1 is
   select decode(operation,'MinFinal',test_id) test_id1, decode(operation,'Final',test_id) test_id2    from test.test_data_v7
   where operation in ('MinFinal','Final')
   and (pf = 1 or pf = 4) and serial = '' || in_serialno || '';
some variables.....
...
..
BEGIN
 for r1 in c1
loop
   dbms_output.put_line('testid ' || r1.testID1);
   dbms_output.put_line('testid ' || r1.testID2);
end loop;
...
..
end;

no test id is outputted..why is this so?
0
 
samir25Author Commented:
btw i run it as
select * from table(FORMAT('2322503'))
0
 
Naveen KumarProduction Manager / Application Support ManagerCommented:
you should do

SQL> SET serveroutput on   -- to see the output on screen.

I am not sure what you are trying to do. if you need more info. on pipelined functions, then refer to http://www.oracle-base.com/articles/9i/PipelinedTableFunctions9i.php

Thanks
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.