Link to home
Start Free TrialLog in
Avatar of GouthamAnand
GouthamAnand

asked on

ALL_TAB_COLUMNS

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,
Avatar of Wasim Akram Shaik
Wasim Akram Shaik
Flag of India image

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
Avatar of slightwv (䄆 Netminder)
slightwv (䄆 Netminder)

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 ..
Avatar of GouthamAnand

ASKER

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.
>>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

ASKER CERTIFIED SOLUTION
Avatar of Wasim Akram Shaik
Wasim Akram Shaik
Flag of India image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
>>I managed to find a workaround.

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

;)
Your suggestion uses DBMS_METADATA to retrieve the create statement.  I'm not doing that at all.
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.
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.
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.
Thank you.