Advertisement
Guest User

.NET Oracle SP call errors ORA-00604 and ORA-01001 when pass

a guest
Feb 6th, 2012
196
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
PL/SQL 0.87 KB | None | 0 0
  1. CREATE OR REPLACE PROCEDURE BLAH_SEARCH
  2.   (start_index NUMBER,
  3.    num_rows NUMBER,
  4.    total_num_rows OUT NUMBER,
  5.    results OUT sys_refcursor)
  6.  
  7. AS
  8.   sql_stmt VARCHAR2(4000);
  9.   results1 sys_refcursor;
  10. BEGIN
  11.   sql_stmt := '
  12.  WITH
  13.    filter_set AS (
  14.      SELECT
  15.          ROWNUM ri,
  16.          e.*
  17.        FROM blah e
  18.        WHERE 1=1 /*some conditions*/
  19.      ORDER BY /*order*/
  20.    ),
  21.    filter_set_r AS (
  22.      SELECT fs.*
  23.        FROM filter_set fs
  24.        WHERE ri BETWEEN :start_row AND :end_row
  25.    )
  26.  SELECT
  27.      (SELECT COUNT(*) FROM filter_set) total_num_rows,
  28.      CURSOR(
  29.        SELECT *
  30.          FROM filter_set_r fs
  31.          ORDER BY ri ASC
  32.          ) results_cur
  33.    FROM dual
  34.  ';
  35.  
  36.   OPEN results1 FOR sql_stmt
  37.     USING site_id, start_index, start_index + num_rows - 1;
  38.   FETCH results1 INTO total_num_rows, results;
  39. END;
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement