ALL_TAB_COLUMNS

GouthamAnand
GouthamAnand used Ask the Experts™
on
Hi,

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?

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
select data_default from all_tab_columns a where table_name='ABC' and  DEFAULT_LENGTH is not null

this will work fine..

what are you expecting?, i can see the output ..

ALL_TAB_COLUMNS data dictionary by default contains data types of columns and their default data length

even for long there will be some default length..

http://docs.oracle.com/cd/B28359_01/server.111/b28320/statviews_2091.htm,

but oracle recommends to use LOB instead of LONG, LONG is still supported and is there for backward compatibility
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Why not just generate the DDL for ABC and pre-create XYZ?

select dbms_metadata.get_ddl('TABLE','ABC') from dual;
** Edit comment got denied because of another post, was about to post the same , to generate ddl ..
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Author

Commented:
Hi,

I am trying to execute the below statement, to generate the alter table statements for XYZ table based on the ABC table. But getting the error.

SQL> select 'alter table XYZ modify (' || COLUMN_NAME ||  ' DEFAULT '||  data_de
fault || ' );'  from ALL_TAB_COLUMNS where TABLE_NAME = 'ABC' and  DEFAULT_LENGT
H is not null;
select 'alter table XYZ modify (' || COLUMN_NAME ||  ' DEFAULT '||  data_default
 || ' );'  from ALL_TAB_COLUMNS where TABLE_NAME = 'ABC' and  DEFAULT_LENGTH is
not null
                                                                    *
ERROR at line 1:
ORA-00932: inconsistent datatypes: expected NUMBER got LONG

Can you please let me know how to make it execute?

Regarding the  dbms_metadata.get_ddl('TABLE','ABC'), I already have the XYZ tables created(many tables).
Now I need to copy the default values from the original tables which  are missing on the created tables(like XYZ ).

This is one question.

Next question is in future if am partitioning a table using the existing tables.

I am using CTAS(create table XYZ as select * from ABC where 1=0). and doing the dbms_redefinition to do online partitioning.
But dbms_redefinition.copy_table_dependents, not copying the default values. So this problem came.
Can you please suggest for both cases, how to copy the default values (best way to do).
ie for the tables which are already done partitioning using dbms_redefinition.
and for the case  which needs to be done next set of tables?

Thanks.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>Can you please let me know how to make it execute?

Sorry.  Cannot help with that SQL.  LONGs are a pain to work with.  I would really look at alternatives.

You might look at creating a temp table that converts the LONG to a CLOB using the TO_LOB function.  Then you can query from the temp table.

Another alternative: export with no rows.  Then you can run am import and capture the DDL without actually importing anything.

>>Now I need to copy the default values from the original tables

The default columns work for me with dbms_metadata.

SQL> create table tab1(col1 char(1) default 'N');

Table created.

SQL> select dbms_metadata.get_ddl('TABLE','TAB1') from dual;

  CREATE TABLE "SCOTT"."TAB1"
   (    "COL1" CHAR(1) DEFAULT 'N'
   ) SEGMENT CREATION DEFERRED
  PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
  TABLESPACE "USERS"

SQL>

Open in new window

I cannot help you with sql, but with pl/sql you can do it in a similar fashion, you would get what you want.

set serveroutput on;

declare
cursor c1 is
select * from all_Tab_columns where table_name='ABC' and default_length is not null;
v_long long;
var_sql varchar2(4000);
v_varchar2 varchar2(4000);
begin
for l1 in c1
loop
v_long:=l1.data_default;
v_varchar2 := substr(v_long,1,4000);
 var_sql:='alter table XYZ modify ('|| l1.COLUMN_NAME || ' DEFAULT '||v_varchar2||' );';
 dbms_output.put_line(var_sql);
end loop;
end;

EDIT : you can modify  this a bit to suit more number of tables

by default you would get data_default values upto 4000 characters only, however you don't need more than that in your case
-->Next question is in future if am partitioning a table using the existing tables.I am using CTAS(create table XYZ as select * from ABC where 1=0)


The CTAS operation only copies column constraints such as NULL, NOT NULL from the creator table to the created table, it does not copy DEFAULT VALUEs of the columns

you may have to use alter table command to do this task
johnsoneSenior Oracle DBA

Commented:
Unfortunately since this is a dictionary table, you cannot change the column type.

I managed to find a workaround.  Since this is a one time thing, it should work for you.

You only need a few columns, so just copy the ones you need:

CREATE TABLE all_tab_columns_conv AS 
  SELECT owner, 
         table_name, 
         column_name, 
         default_length, 
         To_lob(data_default) data_default 
  FROM   all_tab_columns; 

SELECT 'alter table XYZ modify (' 
       || column_name 
       || ' DEFAULT ' 
       || Substr(data_default, 1, 4000) 
       || ' );' 
FROM   all_tab_columns_conv 
WHERE  able_name = 'ABC' 
       AND default_length IS NOT NULL; 

Open in new window


Then you can drop the ALL_TAB_COLUMNS_CONV table.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
>>I managed to find a workaround.

Cool, you found the same solution that I suggested( http:#a40037008 )?

;)
johnsoneSenior Oracle DBA

Commented:
Your suggestion uses DBMS_METADATA to retrieve the create statement.  I'm not doing that at all.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
Read again?

"You might look at creating a temp table that converts the LONG to a CLOB using the TO_LOB function.  Then you can query from the temp table."

I just didn't post the SQL because I didn't think that was a worthy way to go.
johnsoneSenior Oracle DBA

Commented:
OK, I apologize.  I read it twice and didn't see that.

For getting the information the way the asker wants, I don't see another way other than the temporary table.
Most Valuable Expert 2012
Distinguished Expert 2018

Commented:
lol.  I was just jerking your chain!  ;)

>>I don't see another way other than the temporary table.

Going down the started path, I agree.  I just think it is the wrong path.

If dbms_metadata doesn't work, I would probably go with the export/import approach to generate all the DDL.

Scripts that generate scripts to clone/copy are typically more problems than they are worth.  Especially when alternatives exist.

Author

Commented:
Thank you.

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial