How to Describe a Query at Run Time with DBMS_SQL.DESCRIBE_COLUMNS

EXECUTE IMMEDIATE and OPEN FOR need to know the columns of a query when the code is written. When the query arrives as text at run time, with columns nobody knows in advance, DBMS_SQL can parse it and describe its columns: names, types, lengths, and nullability. That makes generic code possible, such as a procedure that prints or exports any query.

Code for This Guide

The main examples are in the examples/pkg-dbms-sql folder of the Oracle Database 26ai code repository on GitHub, each with its output. They use NIMBUS, the sample schema of a fictional airline, which you can install with the scripts in the setup/nimbus folder.

They come from Oracle Database 26ai SQL and PL/SQL Book.

Syntax

c := dbms_sql.open_cursor;
dbms_sql.parse(c, sql_text, dbms_sql.native);
dbms_sql.describe_columns3(c, column_count_out, desc_tab3_out);
-- v_cols(i).col_name, col_type, col_max_len, col_null_ok, col_type_name, ...

DESCRIBE_COLUMNS2 returns DESC_TAB2, which allows long column names; DESCRIBE_COLUMNS3 returns DESC_TAB3, which also gives the type name of object and collection columns.

Print Any Query

PRINT_QUERY parses a query, describes it to print a heading, defines every column as text, and prints the rows. It is called with two queries of different shape.

Example:

create or replace procedure print_query (p_sql varchar2) is
  c      integer := dbms_sql.open_cursor;
  v_cols dbms_sql.desc_tab3;
  v_n    integer;
  v_val  varchar2(4000);
  v_line varchar2(4000);
begin
  dbms_sql.parse(c, p_sql, dbms_sql.native);
  dbms_sql.describe_columns3(c, v_n, v_cols);   -- the query's columns, at run time
  for i in 1 .. v_n loop
    dbms_sql.define_column(c, i, v_val, 4000);
    v_line := v_line || rpad(v_cols(i).col_name, 18);
  end loop;
  dbms_output.put_line(v_line);
  v_n := dbms_sql.execute(c);
  while dbms_sql.fetch_rows(c) > 0 loop
    v_line := null;
    for i in 1 .. v_cols.count loop
      dbms_sql.column_value(c, i, v_val);
      v_line := v_line || rpad(nvl(v_val, ' '), 18);
    end loop;
    dbms_output.put_line(v_line);
  end loop;
  dbms_sql.close_cursor(c);
end;
/
begin
  print_query('select type_code, model, range_km from aircraft_types
               where manufacturer = ''Boeing''');
  print_query('select region, count(*) as countries from countries group by region');
end;
/

Output:

Procedure PRINT_QUERY compiled

TYPE_CODE         MODEL             RANGE_KM
B789              787-9 Dreamliner  14010
B77W              777-300ER         13650
REGION            COUNTRIES
Middle East       1
Oceania           2
South America     1
North America     2
Europe            6
Asia              5
Africa            2

PL/SQL procedure successfully completed.

The same procedure prints three aircraft columns and two region columns, since it learns the columns from DESCRIBE_COLUMNS3 rather than from its code. PRINT_QUERY stays in the schema; drop it when you are done.

Column Types and Nullability

Example:

declare
  c       integer := dbms_sql.open_cursor;
  v_tab   dbms_sql.desc_tab2;
  v_n     integer;
begin
  dbms_sql.parse(c, 'select * from flights', dbms_sql.native);
  dbms_sql.describe_columns2(c, v_n, v_tab);
  for i in 1 .. v_n loop
    dbms_output.put_line(rpad(v_tab(i).col_name, 22) || 'type '
                         || rpad(v_tab(i).col_type, 5)
                         || 'max length ' || v_tab(i).col_max_len
                         || case when v_tab(i).col_null_ok then '' else '  not null' end);
  end loop;
  dbms_sql.close_cursor(c);
end;
/

Output:

FLIGHT_ID             type 2    max length 22  not null
FLIGHT_NO             type 1    max length 6  not null
ROUTE_ID              type 2    max length 22  not null
TAIL_NUMBER           type 1    max length 8  not null
SCHEDULED_DEPARTURE   type 181  max length 13  not null
SCHEDULED_ARRIVAL     type 181  max length 13  not null
ACTUAL_DEPARTURE      type 181  max length 13
ACTUAL_ARRIVAL        type 181  max length 13
STATUS                type 1    max length 10  not null

PL/SQL procedure successfully completed.

The type codes are Oracle's internal ones: 1 for VARCHAR2, 2 for NUMBER, and 181 for TIMESTAMP WITH TIME ZONE. COL_NULL_OK is false for the NOT NULL columns.

Names of Expressions

Example:

declare
  c      integer := dbms_sql.open_cursor;
  v_cols dbms_sql.desc_tab3;
  v_n    integer;
begin
  dbms_sql.parse(c, 'select route_id, round(distance_km / 1.609) as miles, distance_km * 2
                     from routes', dbms_sql.native);
  dbms_sql.describe_columns3(c, v_n, v_cols);
  for i in 1 .. v_n loop
    dbms_output.put_line(i || ': ' || v_cols(i).col_name);
  end loop;
  dbms_sql.close_cursor(c);
end;
/

Output:

1: ROUTE_ID
2: MILES
3: DISTANCE_KM*2

PL/SQL procedure successfully completed.

An aliased expression gets its alias, MILES, and one without an alias gets its text, DISTANCE_KM*2. Alias expressions in queries that generic code will read.

Things to Know

• Describing needs only a parse, not an execute, so it is cheap.
• Always close DBMS_SQL cursors, including in exception handlers, or they stay open for the session.
• Defining every column as VARCHAR2 is the simplest generic approach; Oracle converts each value to text.

Related Guides

• Oracle Dynamic SQL Example to Insert a Record Using DBMS_SQL
• How to Describe Procedures with DBMS_DESCRIBE

Conclusion

DBMS_SQL.DESCRIBE_COLUMNS3 reports the columns of a parsed query at run time. Use it to write procedures that handle queries of any shape, defining and reading each column from the description.