A cross-platform developer asked me if there was an Oracle equivalent to a procedure for returning the columns with datatypes and precisions for any given query. Something like a DESCRIBE command for a table or view, or DBMS_METADATA.GET_DDL for a table, but a bit more streamlined. I came up with a good solution that didn’t rely on SQL*Plus or DBMS_METADATA but wasn’t as streamlined as I had hoped. Addendum: I came up with yet another good solution that is easy to use by developers but will require some object creation and maintenance by the DBA.
A Virtual Table’s Datatypes
Take a query like this:
select user_id,username,table_name,last_analyzed
from dba_users u
join dba_tables t
on u.username = t.owner
where username in ('HR','OE','BI')
order by username,table_name;The output of this query is as follows on my database:
For storage space calculations and other considerations, it’s important to know the resulting datatypes of the columns in the result.
Private Temporary Tables?
My first attempt was to use a CTAS method with a Private Temporary Table (PTT), which would avoid name collisions that a Global Temporary Table (GTT) might, and would avoid the possibility of allocating any storage. Once the PTT was created, I could query DBA_TAB_COLS to get the column names, the datatypes, and the precision of each column.
create private temporary table ora$ptt_ut as
select user_id,username,table_name,last_analyzed
from dba_users u
join dba_tables t
on u.username = t.owner
where 0=1;
Private TEMPORARY created.
desc ora$ptt_ut
ERROR:
ORA-04043: object ora$ptt_ut does not existThough I can store and retrieve rows from a PTT, I can’t see what columns are in the PTT no matter where I look.
select * from user_tab_cols where table_name='ORA$PTT_UT';
no rows selectedI can at least see what PTTs I currently have active, but that doesn’t help me in this case.
select * from user_private_temp_tables;
The metadata for the columns must be somewhere in memory, but there appears to be no user-facing API or data dictionary view to get that metadata.
Workaround (Compromise)
Until I can find a way to get the definitions of the PTT’s columns in session memory (I’m not expecting that any time soon), a somewhat acceptable alternative is to create an empty table, query the data dictionary, then drop the table.
create table ut_cols as
select user_id,username,table_name,last_analyzed
from dba_users u
join dba_tables t
on u.username = t.owner
where 0=1;
Table UT_COLS created.
select
column_id, column_name, data_type, data_length, data_precision, data_scale
from dba_tab_cols
where table_name = 'UT_COLS'
order by column_id;
This will require CREATE TABLE privileges, but no storage needs to be allocated except for a temporary update to the data dictionary. I’ll have to add some extra error handling in case the table does not get created or dropped successfully, but this solution does give the developers what they need.
Addendum: Using DBMS_SQL
An associate pointed out that there is another possible option: use DBMS_SQL with a couple of object types to parse and describe each column in any query. It will require some maintenance by the DBA for the object types and function but otherwise easy to use by a developer with SELECT privileges on the objects in the query.
CREATE OR REPLACE TYPE column_info_rec AS OBJECT (
column_id NUMBER,
column_name VARCHAR2(128),
data_type VARCHAR2(64),
data_length number,
data_precision number,
data_scale number
);
/
CREATE OR REPLACE TYPE column_info_tab IS TABLE OF column_info_rec;
/
CREATE OR REPLACE FUNCTION get_column_info(query_str IN VARCHAR2)
RETURN column_info_tab
IS
cur_id INTEGER;
col_cnt INTEGER;
rec_tab DBMS_SQL.DESC_TAB2;
result column_info_tab := column_info_tab();
col_info column_info_rec;
type_name VARCHAR2(100);
FUNCTION get_type_name(type_code IN NUMBER) RETURN VARCHAR2 IS
BEGIN
CASE type_code
WHEN 1 THEN RETURN 'VARCHAR2';
WHEN 2 THEN RETURN 'NUMBER';
WHEN 12 THEN RETURN 'DATE';
WHEN 96 THEN RETURN 'CHAR';
WHEN 100 THEN RETURN 'BINARY_FLOAT';
WHEN 101 THEN RETURN 'BINARY_DOUBLE';
WHEN 112 THEN RETURN 'CLOB';
WHEN 113 THEN RETURN 'BLOB';
WHEN 180 THEN RETURN 'TIMESTAMP';
WHEN 181 THEN RETURN 'TIMESTAMP WITH TIME ZONE';
WHEN 231 THEN RETURN 'TIMESTAMP WITH LOCAL TIME ZONE';
ELSE RETURN 'OTHER'; -- Default case for unknown types
END CASE;
END get_type_name;
BEGIN
cur_id := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE(cur_id, query_str, DBMS_SQL.NATIVE);
DBMS_SQL.DESCRIBE_COLUMNS2(cur_id, col_cnt, rec_tab);
FOR i IN 1 .. col_cnt LOOP
type_name := get_type_name(rec_tab(i).col_type);
col_info :=
column_info_rec(i,
rec_tab(i).col_name,
type_name,
rec_tab(i).col_max_len,
rec_tab(i).col_precision,
rec_tab(i).col_scale);
result.extend;
result(result.COUNT) := col_info;
END LOOP;
DBMS_SQL.CLOSE_CURSOR(cur_id);
RETURN result;
END;
/This query joins two tables in the HR schema:
select employee_id, last_name, email, hire_date, department_id, department_name
from hr.employees
join hr.departments
using(department_id);I can view the column details by using the TABLE function with that query as an argument to my function (the query constructed with concatenated strings for readability):
SELECT * FROM TABLE(
get_column_info('select employee_id, last_name, email, ' ||
'hire_date, department_id, department_name ' ||
'from hr.employees ' ||
'join hr.departments ' ||
'using(department_id)'));The output is the same as if I was using the CTAS method without having to actually create an empty table:
Using this method is more work for the DBA, but less work for the developers and likely runs faster since the query is only parsed and not materialized.
All Kinds of Exadata Stuff 