Explain the DBMS_SQL package for dynamic SQL

Native dynamic SQL (EXECUTE IMMEDIATE, OPEN FOR) is simpler and faster and should be the default. Reach for DBMS_SQL when the number or types of select-list columns or bind variables are not known until run time (generic data browsers, ETL), when you need DESCRIBE_COLUMNS to inspect a result, or to reuse one parsed cursor for many bind sets. The flow is OPEN_CURSOR, PARSE, BIND_VARIABLE, DEFINE_COLUMN, EXECUTE, FETCH_ROWS, COLUMN_VALUE, CLOSE_CURSOR. DBMS_SQL.TO_REFCURSOR / TO_CURSOR_NUMBER bridge between the two models.

DECLARE
  c        INTEGER;
  v_name   VARCHAR2(60);
  n        INTEGER;
BEGIN
  c := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(c,
    'SELECT last_name FROM employees WHERE department_id = :d',
    DBMS_SQL.NATIVE);
  DBMS_SQL.BIND_VARIABLE(c, ':d', 30);
  DBMS_SQL.DEFINE_COLUMN(c, 1, v_name, 60);
  n := DBMS_SQL.EXECUTE(c);
  WHILE DBMS_SQL.FETCH_ROWS(c) > 0 LOOP
    DBMS_SQL.COLUMN_VALUE(c, 1, v_name);
    DBMS_OUTPUT.PUT_LINE(v_name);
  END LOOP;
  DBMS_SQL.CLOSE_CURSOR(c);
END;
/