Method 4 Dynamic SQL, using DBMS_SQL where the fields in the select clause are unknown

Below are some of the functions of the DBMS_SQL package that are used for dynamic sql.

PARSE - Every sql statement must be parsed for syntax. This is done using the PARSE function.  Once this done it is associated with the program cursor and processed further.
DESCRIBE_COLUMNS - This will get the datatype of each column in the SELECT list and store in an array as per the position of the column in the select list from left to right.
DEFINE_COLUMN - The columns are identified by their position in the select list from left to right. For the list of columns got using DESCRIBLE_COLUMNS, the DEFINE_COLUMN procedure must be called to define the variables to receive the SELECT values based on the datatypes, similar to the INTO clause of a static query.
EXECUTE - to run the sql statement.
FETCH_ROWS - This retrieves the rows one row at a time till all rows have been fetched. For multiple records this can be executed  in a loop.
COLUMN_VALUE - These are placeholders for the columns defined and fetched above.

CLOSE_CURSOR - This closes the cursor once all rows have been fetched. If the cursor is not closed, the memory used by the cursor is not released even though the cursor is no longer being used.

Below is an example of executing a query dynamically where the number/datatypes of select list variables is not known. If the where criteria are also know at run time only then the query can be formed at run time(see commented p_query below).

CREATE OR REPLACE PROCEDURE PR_DYNAM_METHOD_4( p_query in varchar2 )
AUTHID CURRENT_USER
is
    l_theCursor     integer default dbms_sql.open_cursor;
    l_columnValue   varchar2(4000);
    l_status        integer;
    l_descTbl       dbms_sql.desc_tab;
    l_colCnt        number;
 
-- p_emp_no    NUMBER := 101;
--    p_query     varchar2(1000) := 'SELECT EMPLOYEE_NAME, EMPLOYEE_DOB, EMPLOYEE_DESIG
--                                       FROM EMP_MASTER WHERE EMPLOYEE_ID = ' || p_emp_no;
 
    namevar   VARCHAR2(50);
    numvar    NUMBER;
    datevar   DATE;
begin

/* Sample sql statement as input parameter */
  --SQL_STMT := 'SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY, EMAIL, PHONE_NUMBER FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 100';

    dbms_sql.parse(  l_theCursor,  p_query, dbms_sql.native );
    dbms_sql.describe_columns
    ( l_theCursor, l_colCnt, l_descTbl );

    for i in 1 .. l_colCnt loop
        IF l_descTbl(i).col_type = 2 THEN   --NUMERIC DATATYPE
          DBMS_SQL.DEFINE_COLUMN(l_theCursor, i, numvar);
        ELSIF l_descTbl(i).col_type = 12 THEN  --DATE DATATYPE
          DBMS_SQL.DEFINE_COLUMN(l_theCursor, i, datevar);
      -- statements
        ELSE                                 -- STRING DATATYPE
          DBMS_SQL.DEFINE_COLUMN(l_theCursor, i, namevar, 50);
        END IF;
    end loop;

    l_status := dbms_sql.execute(l_theCursor);

    while ( dbms_sql.fetch_rows(l_theCursor) > 0 ) loop
        for i in 1 .. l_colCnt loop
           
        IF (l_descTbl(i).col_type = 1) THEN
            DBMS_SQL.COLUMN_VALUE(l_theCursor, i, namevar);
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              namevar );
        ELSIF (l_descTbl(i).col_type = 2) THEN
            DBMS_SQL.COLUMN_VALUE(l_theCursor, i, numvar);
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              numvar );
        ELSIF (l_descTbl(i).col_type = 12) THEN
            DBMS_SQL.COLUMN_VALUE(l_theCursor, i, datevar);
            dbms_output.put_line
            ( rpad( l_descTbl(i).col_name, 30 )
              || ': ' ||
              datevar );
        END IF;
        end loop;
        dbms_output.put_line( '-----------------' );
    end loop;
exception
    WHEN OTHERS
  THEN
    RAISE_APPLICATION_ERROR(-20001,'Oracle Error ::' || SQLERRM);
end;

If you want to use bind variables for creating the dynamic sql then you need to open the cursor using NATIVE sql REF CURSOR and then DBMS_SQL.TO_CURSOR_NUMBER to convert the opened cursor variable from REF CURSOR to a DBMS_SQL recognised numeric Cursor Id. 

Below is an example of the same.

CREATE OR REPLACE PROCEDURE pr_dynam_method_4_alternate (
  p_sql_stmt VARCHAR2,
  p_emp_no   NUMBER
)
IS
  TYPE curtype IS REF CURSOR;
  cur   curtype;

  curid     NUMBER;
  desctab   DBMS_SQL.DESC_TAB;

  colcnt    NUMBER;

  namevar   VARCHAR2(50);
  numvar    NUMBER;
  datevar   DATE;


BEGIN

/* Sample sql statement as input parameter */
  --SQL_STMT := 'SELECT FIRST_NAME, LAST_NAME, JOB_ID, SALARY, EMAIL, PHONE_NUMBER FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = :c';

  -- Open REF CURSOR variable:
  OPEN cur FOR p_sql_stmt USING p_emp_no;

  -- Switch from native dynamic SQL to DBMS_SQL package:
  curid := DBMS_SQL.TO_CURSOR_NUMBER(cur);
  DBMS_SQL.DESCRIBE_COLUMNS(curid, colcnt, desctab);

  -- Define columns:
  FOR i IN 1 .. colcnt
  LOOP
    IF desctab(i).col_type = 2 THEN                 --NUMERIC DATATYPE
      DBMS_SQL.DEFINE_COLUMN(curid, i, numvar);
    ELSIF desctab(i).col_type = 12 THEN             --DATE DATATYPE
      DBMS_SQL.DEFINE_COLUMN(curid, i, datevar);
      -- statements
    ELSE
      DBMS_SQL.DEFINE_COLUMN(curid, i, namevar, 50);  -- STRING DATATYPE
    END IF;
  END LOOP;

  -- Fetch rows with DBMS_SQL package:
  WHILE DBMS_SQL.FETCH_ROWS(curid) > 0 LOOP
    FOR i IN 1 .. colcnt LOOP
      IF (desctab(i).col_type = 1) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, namevar);
        dbms_output.put_line
            ( rpad( desctab(i).col_name, 30 )
              || ': ' ||
              namevar );
      ELSIF (desctab(i).col_type = 2) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, numvar);
        dbms_output.put_line
            ( rpad( desctab(i).col_name, 30 )
              || ': ' ||
              numvar );
      ELSIF (desctab(i).col_type = 12) THEN
        DBMS_SQL.COLUMN_VALUE(curid, i, datevar);
        dbms_output.put_line
            ( rpad( desctab(i).col_name, 30 )
              || ': ' ||
              datevar );
        -- statements
      END IF;
     
    END LOOP;
  END LOOP;

  DBMS_SQL.CLOSE_CURSOR(curid);

EXCEPTION
  WHEN OTHERS
  THEN
    RAISE_APPLICATION_ERROR(-20001,'Oracle Error ::' || SQLERRM);
END;

The above examples are only for String, Number and Date datatypes in the select list. The functions can be extented for other datatypes also by adding them in the 'If' conditions of the DEFINE_COLUMN and COLUMN_VALUE functions. Below is the list of values for the other datatypes in dbms_sql.desc_tab

Datatype                             Number
VARCHAR2                             1
NVARCHAR2                            1
NUMBER                               2
INTEGER                              2
LONG                                 8
ROWID                                11
DATE                                 12
RAW                                  23
LONG RAW                             24
CHAR                                 96
NCHAR                                96
MLSLABEL                             106
CLOB                                 112
NCLOB                                112
BLOB                                 113
BFILE                                114
Object type                          121
Nested table Type                    122
Variable array                       123

References - 

https://docs.oracle.com/cloud/latest/db112/LNPLS/dynamic.htm#BHCBJGEH -http://www.toadworld.com/platforms/oracle/w/wiki/3328.dbms-sql-describe-columns 

Quote For the Day 

"The Way To Get Started Is To Quit Talking And Begin Doing." -Walt Disney