Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1723
  • Last Modified:

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
0
samir25
Asked:
samir25
  • 3
  • 3
  • 2
1 Solution
 
dqmqCommented:
Remove this line:

into testid1,testid2
0
 
samir25Author Commented:
then how do i refer it..can u explian me more thanks
0
 
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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
 
Naveen KumarProduction 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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now