Home » SQL & PL/SQL » SQL & PL/SQL » Setting data_precision with SQL
Setting data_precision with SQL [message #204056] Fri, 17 November 2006 09:03 Go to next message
collis
Messages: 11
Registered: October 2006
Junior Member
Hello all,

I need to add a column to an existing database with data_precision, low_value, and high_value the same as another column in that same table, and I don't really know how to go about doing this.

I've seen from another forum post how to select these things:

select data_type, data_length, data_precision, data_scale
from cols
where table_name = <table>
and column_name = <column>;


but I can't figure out how to translate this to a column creation statement.

Thanks in advance,
collis
Re: Setting data_precision with SQL [message #204059 is a reply to message #204056] Fri, 17 November 2006 09:15 Go to previous messageGo to next message
Cthulhu
Messages: 381
Registered: September 2006
Location: UK
Senior Member
Depends on the data type. For dates, it's just:

alter table xxx add (yyyy date);

For numbers:

alter table xxx add (yyyy number(precision, scale));

For varchar2:

alter table xxx add (yyyy varchar2(data_length));

You also need to look at whether it's null or not null (nullable on tab_cols) and if there are any other constraints e.g. check constraints.
Re: Setting data_precision with SQL [message #204060 is a reply to message #204056] Fri, 17 November 2006 09:20 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Well, that was slightly trickier than I thought it would be...
select 'alter table '||table_name
     ||' add ( <new_column_name> '
     ||data_type
     ||decode(data_type,'VARCHAR2','('||data_length||')'
                       ,'NUMBER',  nvl2(data_precision,'('||data_precision,null)
                                 ||decode(nvl(data_scale,0),0,null,','||data_scale)
                                 ||nvl2(data_precision,')',null)
             )
     ||')'
     ,column_name
     ,data_type
     ,data_length
     ,data_scale
     ,data_precision
from user_tab_columns;
Re: Setting data_precision with SQL [message #204062 is a reply to message #204060] Fri, 17 November 2006 09:32 Go to previous messageGo to next message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Or, you can use DBMS_METADATA to generate a DDL to create the table and then strip out the line that describes the column you want.
Re: Setting data_precision with SQL [message #204063 is a reply to message #204060] Fri, 17 November 2006 09:36 Go to previous messageGo to next message
collis
Messages: 11
Registered: October 2006
Junior Member
JRowbottom wrote on Fri, 17 November 2006 09:20
Well, that was slightly trickier than I thought it would be...
select 'alter table '||table_name
     ||' add ( <new_column_name> '
     ||data_type
     ||decode(data_type,'VARCHAR2','('||data_length||')'
                       ,'NUMBER',  nvl2(data_precision,'('||data_precision,null)
                                 ||decode(nvl(data_scale,0),0,null,','||data_scale)
                                 ||nvl2(data_precision,')',null)
             )
     ||')'
     ,column_name
     ,data_type
     ,data_length
     ,data_scale
     ,data_precision
from user_tab_columns;



Jesus murphy...
okay thanks
Re: Setting data_precision with SQL [message #204065 is a reply to message #204063] Fri, 17 November 2006 09:38 Go to previous message
JRowbottom
Messages: 5933
Registered: June 2006
Location: Sunny North Yorkshire, ho...
Senior Member
Actually, you can lose quite a few columns - they were there for debugging
select 'alter table '||table_name
     ||' add ( <new_column_name> '
     ||data_type
     ||decode(data_type,'VARCHAR2','('||data_length||')'
                       ,'NUMBER',  nvl2(data_precision,'('||data_precision,null)
                                 ||decode(nvl(data_scale,0),0,null,','||data_scale)
                                 ||nvl2(data_precision,')',null)
             )
     ||')'
from user_tab_columns;
Previous Topic: ORA-06503: PL/SQL: Function returned without value
Next Topic: Ref Cursor Problem ... Urgent
Goto Forum:
  


Current Time: Thu Dec 08 00:09:32 CST 2016

Total time taken to generate the page: 0.06566 seconds