Oracle : how to fetch data from dynamic query?

Question

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.

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 ?

~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~ Thank Thinkjet for the reference. I have solved the problem, to help the others, here is the piece of code I used:

        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) > 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;
         /

Answer 1

You can do that with DBMS_SQL package.

Update To get more detailed reference about DBMS_SQL go here.

Answer 2

<PRE>
DECLARE
   RUN_S         CLOB;
   IGNORE        NUMBER;
   SOURCE_CURSOR NUMBER;
   PWFIELD_COUNT NUMBER DEFAULT 0;
   L_DESCTBL     DBMS_SQL.DESC_TAB2;
   Z_NUMBER      NUMBER;
BEGIN
   RUN_S         := ' SELECT 1      AS VAL1,
                             2      AS VAL2,
                             CURSOR (SELECT 11 AS VAL11,
                                            12 AS VAL12
                                       FROM DUAL) AS CUR1,
                             CURSOR (SELECT 11 AS VAL11,
                                            12 AS VAL12
                                       FROM DUAL) AS CUR2
                        FROM DUAL';
   SOURCE_CURSOR := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(SOURCE_CURSOR, RUN_S, DBMS_SQL.NATIVE);
   DBMS_SQL.DESCRIBE_COLUMNS2(SOURCE_CURSOR, PWFIELD_COUNT, L_DESCTBL); -- get record structure
   FOR I IN 1 .. PWFIELD_COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Type:' || L_DESCTBL(I).COL_TYPE);
      IF L_DESCTBL(I).COL_TYPE = 2 THEN
         DBMS_SQL.DEFINE_COLUMN(SOURCE_CURSOR, I, Z_NUMBER);
      END IF;
      NULL;
  END LOOP;
   IGNORE := DBMS_SQL.EXECUTE(SOURCE_CURSOR);
   LOOP
      IF DBMS_SQL.FETCH_ROWS(SOURCE_CURSOR) > 0 THEN
         FOR I IN 1 .. PWFIELD_COUNT LOOP
            IF L_DESCTBL(I).COL_TYPE IN (2) THEN
               DBMS_SQL.COLUMN_VALUE(SOURCE_CURSOR, I, Z_NUMBER);
               DBMS_OUTPUT.PUT_LINE('Col ' || I || ' Value:' || Z_NUMBER);
            END IF;
         END LOOP;
      ELSE
         EXIT;
      END IF;
   END LOOP;
END;
</PRE>

Answer 3

If you are building your string within PL/SQL, you can run it with EXECUTE IMMEDIATE. <- link. Use the BULK COLLECT INTO and output the collection.