Oracle package: DBMS_SQL

This package provides functions to execute dynamic SQL.
cursor_id := DBMS_SQL.OPEN_CURSOR;                                                                        

Creates new cursor and returns its ID.

DBMS_SQL.PARSE(cursor_id, SQL_or_PL/SQL_statement, DBMS_SQL.NATIVE or 1);

Parses statement to the cursor. Last parameter should be equal to DBMS_SQL.NATIVE or 1 always. If statement contain DDL it is run when parsed.

DBMS_SQL.DEFINE_COLUMN(cursor_id, position, variable_for_data_type[, length]);                

Ties column by position in SELECT list with data type. Only for statements with SELECT clause.

number_of_row := DBMS_EXECUTE(cursor_id);                                                                 

No comments. Result has sense in the case of INSERT, UPDATE or DELETE clauses only in statement because it returns number of affected rows. Otherwise, there are no sense.

result := DBMS_SQL.FETCH_ROWS(cursor_id);                                                                

It fetches row from the cursor into a buffer. Returns number of fetched rows (1 for DEFINE_COLUMN clause and may be more for DEFINE_ARRAY clause). Returns zero when there are no rows.

DBMS_SQL.COLUMN_VALUE(cursor_id, position, variable_for_out);                              

It retrieves result of previous function from buffer and transfers to variable_for_out. Position means number of column in SELECT list.

DBMS_SQL.CLOSE_CURSOR(cursor_id);                                                                        

No comments.

Another functions:
DBMS_SQL.IS_OPEN(cursor_id) - no comments, returns boolean.
DBMS_SQL.BIND_VARIABLE(cursor_id, ':var_name_in_dynamic_statement', var_name_in_procedure)
DBMS_SQL.BIND_ARRAY(cursor_id, ':array_name_in_dynamic_statement', array_name_in_procedure)

DBMS_SQL has some types for bind array: DBMS_SQL.NUMBER_TABLE, DBMS_SQL.VARCHAR2_TABLE, DBMS_SQL.DATE_TABLE etc.

DBMS_SQL.DEFINE_ARRAY(cursor_id, position, array_name, number_of_items, start_index);

*********

SET SERVEROUTPUT ON
DECLARE
   cursor_id NUMBER;
   result_ex NUMBER;
   idn department.id%type;
   deptname department.name%type;
BEGIN
   cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor_id, 'SELECT id, name FROM department', DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_COLUMN(cursor_id, 2, deptname, 20);
   result_ex := DBMS_SQL.EXECUTE(cursor_id); --No sense, should be ignored.
   WHILE ( DBMS_SQL.FETCH_ROWS(cursor_id) > 0 ) LOOP
      DBMS_SQL.COLUMN_VALUE(cursor_id, 2, deptname);
      DBMS_OUTPUT.PUT_LINE('result_ex: '||result_ex||'deptname: '||deptname);
   END LOOP;
   DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;

SET SERVEROUTPUT ON
DECLARE
   cursor_id NUMBER;
   result_ex NUMBER;
   deptname DBMS_SQL.VARCHAR2_TABLE;
BEGIN
   cursor_id := DBMS_SQL.OPEN_CURSOR;
   DBMS_SQL.PARSE(cursor_id, 'SELECT id, name FROM department', DBMS_SQL.NATIVE);
   DBMS_SQL.DEFINE_ARRAY(cursor_id, 2, deptname, 8, 0);
   result_ex := DBMS_SQL.EXECUTE(cursor_id); --No sense
   LOOP
      result_ex := DBMS_SQL.FETCH_ROWS(cursor_id);
      DBMS_SQL.COLUMN_VALUE(cursor_id, 2, deptname);
      EXIT WHEN result_ex < 8;
   END LOOP;
   -- For cycle to output deptname(i)
   DBMS_SQL.CLOSE_CURSOR(cursor_id);
END;