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.