0

I am trying to print column name along with data type but in DLL format. But no idea where to begin

Expected output :

Name varchar2(255)
Age  varchar2(266)
creation date

I am using the SQL query like following below

select column_name , data_type from all_tab_cols where table_name='EMP';

The above query gives me column name and data type but how to achieve the parenthesis ( and )

zud31507
  • 3
  • 2

1 Answers1

0

You can use dbms_metadata, or you can assemble the DDL on your own. This will give you the column list portion of a table DDL in most situations:

   SELECT column_name||' '||data_type||CASE WHEN (data_type LIKE '%CHAR%') THEN '('||char_length||')'
                                            WHEN (data_type LIKE '%RAW%') THEN '('||data_length||')'
                                            WHEN (data_type = 'NUMBER') THEN '('||NVL(TO_CHAR(data_precision),'*')||DECODE(data_scale,NULL,NULL,','||data_scale)||')'
                                    END||','
     FROM all_tab_cols 
    WHERE table_name='EMP'
Paul W
  • 5,507
  • 2
  • 2
  • 13
  • Does it going to work for all data type and what if number as precision like `number(10,2)` – zud31507 May 26 '23 at 14:06
  • Yes, it handles the number precision and scale. It should take care of most datatypes. I can't think of one that it wouldn't handle. Dates, timestamps, intervals, floats, binary doubles, LOBs, XMLTYPE, etc... don't have to add anything after the data_type. – Paul W May 26 '23 at 16:07