Posted By

mprabhuram on 08/30/10


Tagged

Oracle PLSQL


Versions (?)

Who likes this?

2 people have marked this snippet as a favorite

hchattaway
Tyster


Dynamic SQL - Stored procedure


 / Published in: SQL
 

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

other links : http://tinman.cs.gsu.edu/~raj/880/su98/pldsql/node6.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;

Report this snippet  

You need to login to post a comment.