Advanced Search

Dynamic SQL - Stored procedure

/ Published in: SQL
Save to your folder(s)
other links :
http://tinman.cs.gsu.edu/~raj/880/su98/pldsql/node6.html

Expand | Embed | Plain Text

Copy this code and paste it in your HTML 
  1. CREATE OR REPLACE
  2. PROCEDURE         profiling
  3. AS
  4. -- DECLARATIONS
  5. C1_CUR                  INTEGER;
  6. ROW_COUNT               INTEGER;
  7. VAL1                    NUMBER;
  8.  
  9. ERR_CODE                NUMBER;
  10. ERR_MSG                 VARCHAR2(200);
  11.  
  12.  
  13. BEGIN
  14.  
  15.   dbms_output.enable(buffer_size => NULL);
  16.  
  17.   -- 1. CREATING THE CURSOR
  18.   C1_CUR := DBMS_SQL.OPEN_CURSOR;
  19.  
  20.   -- 2. PREPARE & PARSE THE QUERY
  21.   DBMS_SQL.PARSE(C1_CUR,'SELECT ACCT_NBR FROM VERITY_BK_M WHERE ROWNUM < 10',DBMS_SQL.NATIVE);
  22.  
  23.   -- 3. DEFINE AND BIND THE OUTPUT COLUMN
  24.   DBMS_SQL.DEFINE_COLUMN(C1_CUR, 1,VAL1);
  25.  
  26.   -- 4. EXECUTE THE QUERY
  27.   ROW_COUNT := DBMS_SQL.EXECUTE(C1_CUR);
  28.  
  29.  
  30.   -- 5. FETCH & LOOP THROUGH THE RESULTING RECORDS  
  31.   LOOP
  32.     IF DBMS_SQL.FETCH_ROWS(C1_CUR) = 0 THEN
  33.       EXIT;
  34.     ELSE
  35.       -- 6. GET THE VALUE FROM THE CURSOR INTO A VARIABLE
  36.       DBMS_SQL.COLUMN_VALUE (C1_CUR, 1, VAL1);
  37.       DBMS_OUTPUT.PUT_LINE(VAL1);
  38.  
  39.     END IF;
  40.   END LOOP;   
  41.  
  42.   EXCEPTION
  43.       WHEN OTHERS THEN
  44.           ERR_CODE  := SQLCODE;
  45.           ERR_MSG   := SQLERRM;
  46.  
  47.           DBMS_OUTPUT.PUT_LINE(ERR_CODE);
  48.           DBMS_OUTPUT.PUT_LINE(ERR_MSG);                       
  49.  
  50. END;

URL: http://docstore.mik.ua/orelly/oracle/bipack/ch02_05.htm

Report this snippet


Comments

RSS Icon Subscribe to comments

You need to login to post a comment.