This week only. Pastebin PRO Accounts Christmas Special! Don't miss out!Want more features on Pastebin? Sign Up, it's FREE!
Guest

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

By: a guest on Feb 6th, 2012  |  syntax: PL/SQL  |  size: 0.87 KB  |  views: 110  |  expires: Never
download  |  raw  |  embed  |  report abuse  |  print
Text below is selected. Please press Ctrl+C to copy to your clipboard. (⌘+C on Mac)
  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;
clone this paste RAW Paste Data