create or replace
PROCEDURE         profiling
AS
-- DECLARATIONS
C1_CUR                  INTEGER;
ROW_COUNT               INTEGER;
VAL1                    NUMBER;

ERR_CODE                NUMBER;
ERR_MSG                 VARCHAR2(200);


BEGIN

  dbms_output.enable(buffer_size => NULL);
   
  -- 1. CREATING THE CURSOR
  C1_CUR := DBMS_SQL.OPEN_CURSOR;
   
  -- 2. PREPARE & PARSE THE QUERY
  DBMS_SQL.PARSE(C1_CUR,'SELECT ACCT_NBR FROM VERITY_BK_M WHERE ROWNUM < 10',DBMS_SQL.NATIVE);
    
  -- 3. DEFINE AND BIND THE OUTPUT COLUMN
  DBMS_SQL.DEFINE_COLUMN(C1_CUR, 1,VAL1);
    
  -- 4. EXECUTE THE QUERY
  ROW_COUNT := DBMS_SQL.EXECUTE(C1_CUR);
  
  
  -- 5. FETCH & LOOP THROUGH THE RESULTING RECORDS  
  LOOP
    IF DBMS_SQL.FETCH_ROWS(C1_CUR) = 0 THEN
      EXIT;
    ELSE
      -- 6. GET THE VALUE FROM THE CURSOR INTO A VARIABLE
      DBMS_SQL.COLUMN_VALUE (C1_CUR, 1, VAL1);
      DBMS_OUTPUT.PUT_LINE(VAL1);
      
    END IF;
  END LOOP;   
          
  EXCEPTION
      WHEN OTHERS THEN
          ERR_CODE  := SQLCODE;
          ERR_MSG   := SQLERRM;
            
          DBMS_OUTPUT.PUT_LINE(ERR_CODE);
          DBMS_OUTPUT.PUT_LINE(ERR_MSG);                       
       
END;

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

other links :
http://tinman.cs.gsu.edu/~raj/880/su98/pldsql/node6.html

Dynamic SQL - Stored procedure

Oracle

SQL