CODEWITHSUNDEEP
oracle-databaseplsqloracle10g
Hari
Hari

Reputation: 397

ROWNUM in CURSOR of oracle sql query is not working

Please look at the below oracle function,

DROP FUNCTION get_mapper_requests;

CREATE OR REPLACE FUNCTION get_mapper_requests(p_status IN NUMBER, p_rownum IN NUMBER)
RETURN  SYS_REFCURSOR
AS

CUR_MED_MAPPER_STATUS SYS_REFCURSOR;

BEGIN

dbms_output.put_line('Status Value:'||p_status);

dbms_output.put_line('Row Count:'||p_rownum);

OPEN CUR_MED_MAPPER_STATUS FOR SELECT cmd_id,status FROM CDFDBA.MED_COM_MAPPER_CMDS WHERE 
STATUS=p_status and ROWNUM < p_rownum;

RETURN CUR_MED_MAPPER_STATUS;

END;

The above function return all records even though if I pass any integer value to variable p_rownum. For ex if p_rownum=10, the function returns all records,

Calling block

DECLARE

 c SYS_REFCURSOR;

 v  VARCHAR2(1000);

 v1 VARCHAR2(1000);

BEGIN

 dbms_output.put_line('Hello');

 c := get_mapper_requests(0, 10);

 LOOP

    FETCH c INTO v,v1; 

    dbms_output.put_line('Value from cursor: '||v||' '||v1);

 END LOOP;

 CLOSE c;

END;
/

Please support.

Upvotes: 0

Views: 10370

Answers (2)

user1049838
user1049838

Reputation: 

declare
   cursor c is select * from <table_name> where rownum < 15;  -- SELECT ONLY 15 ROWS OF TABLE
   type t is table of c%rowtype index by pls_integer;
   tab t;
begin
   open c;
   loop
      fetch c bulk collect into tab limit 10;   -- FETCH cursor in chunks of 10
      dbms_output.put_line('X'); 
      exit when c%notfound;
   end loop;
end;

Upvotes: 0

Sodved
Sodved

Reputation: 8588

After you FETCH in the calling code I think you're missing

EXIT WHEN c%NOTFOUND;

Without that I'd assume you code just keeps calling DBMS_OUTPUT with the last fetched row until you get an error for filling the dbms output buffer.

Upvotes: 1

Related Questions