I am migrating a database and am trying to retrieve table structure information into a single line item for machine processing. For technical reasons, existing migration tools can not be used and it must be processed in this manner.
I have run the following query with success on multiple tables:
SELECT LISTAGG(column_name || ',' || data_type || ',' || data_length, ',')
WITHIN GROUP (ORDER BY column_id)
WHERE table_name = 'MyTableName'
ColumnName1, VARCHAR2, 20, ColumnName2, NUMBER, 22, ColumnName3, CHAR, 3, ...
SELECT * FROM MyTableName
You need to decide whether to use
data_precision based on the
data_type, which you can do with a case expression:
select listagg(column_name ||','|| data_type ||','|| case when data_type in ('VARCHAR2', 'NVARCHAR2', 'CHAR', 'RAW') then to_char(data_length) when data_type = 'NUMBER' and (data_precision is not null or data_scale is not null) then data_precision || case when data_scale > 0 then '.' || data_scale end end, ',') within group (order by column_id) from all_tab_columns where table_name = 'MYTABLENAME' and owner = user -- if it is always current user, use user_tab_columns instead /
If I create that table as:
create table mytablename (col1 varchar2(20), col2 number(2), col3 char(3), col4 date, col5 timestamp(3), col6 clob, col7 number(5,2));
then that query produces:
In this example I've represented a number as precision.scale, but you may not have scales to worry about, or may want to handle them differently - depends how the result will be used. And I've included an empty field for the data types with no size, e.g. CLOB and DATE.
Also note that timestamps (and intervals) include the precision in the data type itself, so the
timestamp(3) is coming directly from that column's
data_type. Timestamps with time zones and intervals also include spaces in the data type name.
So this is a starting point, and you can extend it to other data types you need to handle in specific ways, or (say) split the timestamp precision out into a separate comma-separated field.