Retrieving Table Structure with Dynamic SQL

Question

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)
FROM all_tab_cols
WHERE table_name = 'MyTableName'

For many of the tables it works fine and returns exactly what I would expect:

ColumnName1, VARCHAR2, 20, ColumnName2, NUMBER, 22, ColumnName3, CHAR, 3, ...

However, sometimes it is not quite right. For example, I know from the DB design document that ColumnName2 is supposed to be a number of length 2, not 22. Why does that return incorrectly?

And even more puzzling, sometimes it does not work at all and returns nothing. I was thinking it was the CHAR data type that was causing problems, but some of my tables that have CHAR worked alright. It does seem to pretty consistently give me problems if they are INTEGER, SHORTINT, or DATE types though. What is the best way to fix this problem?

I also know the table exists, because when I run a simple

SELECT * FROM MyTableName

it returns all of the records in the table.

UPDATE

I tried replacing the data_length with data_precision and for numbers it returned the correct answer, but now I have nothing for VARCHAR2. How do I need to restructure my query to get me data_precision if it is a number and instead give me data_length if it is anything else?

Also, I still have several tables that will not let me view their structures. I am still not very familiar with schemas, but I understand sometimes a table may exist under a different one. But why would the data be returned with a SELECT *, but the structure information here would not when I am looking at all_tab_col?

Answer 1

You need to decide whether to use data_length or 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:

COL1,VARCHAR2,20,COL2,NUMBER,2,COL3,CHAR,3,COL4,DATE,,COL5,TIMESTAMP(3),,COL6,CLOB,,COL7,NUMBER,5.2

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.