title: Oracle : how to fetch data from dynamic query?
body: <p>I have a program to generate dynamic query string based on input. This query may select from any tables or joined tables in my DB, and the column names and number of columns are unknown.</p>
  
  <p>Now with this query string as the only input, I want to fetch all data from the result and output them line by line, is there any way to do this ?</p>
  
  <p>~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
  Thank Thinkjet for the reference. I have solved the problem, to help the others, here is the piece of code I used:</p>
  
  <pre><code>        DECLARE
             v_curid    NUMBER;
             v_desctab  DBMS_SQL.DESC_TAB;
             v_colcnt   NUMBER;
             v_name_var  VARCHAR2(10000);
             v_num_var   NUMBER;
             v_date_var  DATE;
             v_row_num    NUMBER;
              p_sql_stmt VARCHAR2(1000);
          BEGIN
              v_curid := DBMS_SQL.OPEN_CURSOR;
              p_sql_stmt :='SELECT * FROM emp';
              DBMS_SQL.PARSE(v_curid, p_sql_stmt, DBMS_SQL.NATIVE);
             DBMS_SQL.DESCRIBE_COLUMNS(v_curid, v_colcnt, v_desctab);
  
             -- Define columns:
             FOR i IN 1 .. v_colcnt LOOP
              IF v_desctab(i).col_type = 2 THEN
                  DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_num_var);
                  ELSIF v_desctab(i).col_type = 12 THEN
                  DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_date_var);
                  ELSE
                  DBMS_SQL.DEFINE_COLUMN(v_curid, i, v_name_var, 50);
                  END IF;
              END LOOP;
              v_row_num := dbms_sql.execute(v_curid);
              -- Fetch rows with DBMS_SQL package:
              WHILE DBMS_SQL.FETCH_ROWS(v_curid) &gt; 0 LOOP
                  FOR i IN 1 .. v_colcnt LOOP
                  IF (v_desctab(i).col_type = 1) THEN
                          DBMS_SQL.COLUMN_VALUE(v_curid, i, v_name_var);
                  ELSIF (v_desctab(i).col_type = 2) THEN
                          DBMS_SQL.COLUMN_VALUE(v_curid, i, v_num_var);
                  ELSIF (v_desctab(i).col_type = 12) THEN
                          DBMS_SQL.COLUMN_VALUE(v_curid, i, v_date_var);
                  END IF;
              END LOOP;
              END LOOP;
              DBMS_SQL.CLOSE_CURSOR(v_curid);
           END;
           /
  </code></pre>