In the oracle ALL_TAB_COLUMNS table, there is a column data_default. I am selecting this column where DEFAULT_LENGTH is not null.
But am not able to get the value as the DATA_DEFAULT is LONG data type.
Can you please let me know how can I view the values with a select statement in sqlplus?
CREATE TABLE ABC (A VARCHAR2(10) DEFAULT 'N');
select data_default from all_tab_columns a where table_name='ABC' and DEFAULT_LENGTH is not null.
Actually am trying to generate a alter table command.
I create a XYZ table as below
create table xyz as select * from abc where 1=0;
I need to copy all the default values from ABC to XYZ.
Like this I need to do for many tables and many columns.
For this am trying to execute the below statement.
select 'alter table XYZ modify (' || COLUMN_NAME || ' DEFAULT '|| data_default || ' );'
from ALL_TAB_COLUMNS where TABLE_NAME = 'ABC' and DEFAULT_LENGTH is not null;
Can you please help?