Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- 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;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement