fast fetching data using table function oracle apex

Question

Is there a way to solve the "ORA-01422 fetch return more than requested number of >rows" without using for loop to return value for table functions?Fetching data using for loop to use that data for oracle application using APEX becomes slower.

Answer 1

You may use DISTINCT or GROUP BY against ORA-01422 with listing all the rows those should be returned without using a LOOP but with just a single INTO as in the following example :

SQL> create table tab( col1 int, col2 varchar2(50) );

 Table created
SQL> insert all
2         into tab values(1,'abc')
3         into tab values(1,'abc')
4         into tab values(2,'def')
5  select * from dual;

3 rows inserted
SQL> set serveroutput on;
SQL> 
SQL> declare
  2    v_col1 tab.col1%type;
  3    v_col2 tab.col2%type;
  4  begin
  5    select t.col1, t.col2
  6      into v_col1, v_col2
  7      from tab t
  8     where t.col1 = 1;
  9  
 10     dbms_output.put_line(v_col1||'  '||v_col2);
 11  end;
 12  /     

ORA-01422: exact fetch returns more than requested number of rows
ORA-06512: at line 6

SQL> 
SQL> declare
  2    v_col1 tab.col1%type;
  3    v_col2 tab.col2%type;
  4  begin
  5    select t.col1, t.col2
  6      into v_col1, v_col2
  7      from tab t
  8     where t.col1 = 1
  9     group by t.col1, t.col2;
 10  
 11     dbms_output.put_line(v_col1||'  '||v_col2);
 12  end;
 13  /

1  abc

PL/SQL procedure successfully completed

SQL> 
SQL> declare
  2    v_col1 tab.col1%type;
  3    v_col2 tab.col2%type;
  4  begin
  5    select distinct t.col1, t.col2
  6      into v_col1, v_col2
  7      from tab t
  8     where t.col1 = 1;
  9  
 10     dbms_output.put_line(v_col1||'  '||v_col2);
 11  end;
 12  /

1  abc

PL/SQL procedure successfully completed