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.
