First step is to create a procedure with out parameter datatype SYS_REFCURSOR
-- Procedure creation
CREATE OR REPLACE procedure cp_test1 (pgnum int ,pgrecords int ,pResultSet OUT SYS_REFCURSOR) is
Begin
Open pResultSet for
select *
from ( select a.*, rownum r
from ( select *
from test1
order by id ) a
where rownum < (pgnum * pgrecords)+1)
where r > ((pgnum-1) * pgrecords);
End;
After the procedure is created, please try below statements in SQL plus:
sql > set serveroutput on
sql > variable r1 refcursor
sql > exec cp_test1(1,20,:r1)
sql > print :r1
Subscribe to:
Post Comments (Atom)
No comments:
Post a Comment