CREATE OR REPLACE PROCEDURE BLAH_SEARCH
(start_index NUMBER,
num_rows NUMBER,
total_num_rows OUT NUMBER,
results OUT sys_refcursor)
AS
sql_stmt VARCHAR2(4000);
results1 sys_refcursor;
BEGIN
sql_stmt := '
WITH
filter_set AS (
SELECT
ROWNUM ri,
e.*
FROM blah e
WHERE 1=1 /*some conditions*/
ORDER BY /*order*/
),
filter_set_r AS (
SELECT fs.*
FROM filter_set fs
WHERE ri BETWEEN :start_row AND :end_row
)
SELECT
(SELECT COUNT(*) FROM filter_set) total_num_rows,
CURSOR(
SELECT *
FROM filter_set_r fs
ORDER BY ri ASC
) results_cur
FROM dual
';
OPEN results1 FOR sql_stmt
USING site_id, start_index, start_index + num_rows - 1;
FETCH results1 INTO total_num_rows, results;
END;