SELCT 'TYPE' from table definition? 1

[wolves]

I would like to display the TYPE field in a table definition onto a report.
How exactly can I query on this field?
Currently, I am selecting the field from the dba_columns table, concatinating the data_type field with the data_length field with a '(' ')' around the data_length field to get this result 'VARCHAR2(6)', which is exactly what I need, but looking for an alternative way of doing this. I thought there would be an easier way to pull this information.
Is there an easier way?

thnx in advance

 Name                            Null?    Type
 ------------------------------- -------- ----
 Name                                     VARCHAR2(6)
 LAST_NAME                                VARCHAR2(1)
 ADDR                                     VARCHAR2(72)
 
[\code]

 

[SantaMufasa]

Wolves,

Your method is as good as any solution. But be careful, however about using "DATA_LENGTH" in all cases. For a NUMBER column, DATA_LENGTH will give you an inaccurate result -- for NUMBER, you should use DATA_PRECISION concatenated with a comma and DATA_SCALE.

Cheers,

Mufasa
(aka Dave of Sandy, Utah, USA @ 17:17 (30Jul04) UTC (aka "GMT" and "Zulu"), 10:17 (30Jul04) Mountain Time)

 

[wolves]

I was afraid of that.
Now how do I tell my query to select DATA LENGTH for the varchar2 fields, and DATA PRECISION for the number fields?
Will play with it, see what happens.
thx again

 

[SantaMufasa]

Wolves,

Here is an excerpt of my SQL code that I use in my script that is similar to your script:

SELECT ...
col.data_type ||
  decode(substr(data_type,1,4),
    'DATE',null,
    'LONG',null,
    'VARC','('||ltrim(to_char(data_length,'9999'))||')',
    'CHAR','('||ltrim(to_char(data_length,'999'))||')',
    'NUMB','('||ltrim(to_char(data_precision,'99'))||
           decode(data_scale,
             0,null,
             ','||ltrim(to_char(data_scale,'999')))||')'
          ,null)
  b, ...

I'm confident it will do what you want. Please let me know if there is any problem with it.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:28 (30Jul04) UTC (aka "GMT" and "Zulu"), 11:28 (30Jul04) Mountain Time)

 

[wolves]

Santa, I like your idea. I am trying it now.
As I am not an Oracle person, I do not understand what the error is when I try your suggestion:

buffer overflow. Use SET command to reduce ARRAYSIZE or increase MAXDATA.
Input truncated to 85 characters

??

 

[SantaMufasa]

Wolves,

Not a problem. To resolve the first error, just issue this SQL*Plus command before you run your query:

set arraysize 1

The second error occurs because SQL*Plus expects the last character of your script to be a carriage-return character. To resolve the problem, edit your script and place your cursor behind the very last character in your script, hit [Enter] (to insert a carriage return), save your script, and re-execute to confirm that it resolved the problem.

Let us know your results,

Mufasa
(aka Dave of Sandy, Utah, USA @ 19:25 (30Jul04) UTC (aka "GMT" and "Zulu"), 12:25 (30Jul04) Mountain Time)

 

[wolves]

you 'da man. That code worked like a charm Santa.
Thank you very, very much.

wolves