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,
Oracle Database

Avatar of undefined
Last Comment
GouthamAnand

8/22/2022 - Mon
Wasim Akram Shaik

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

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

select dbms_metadata.get_ddl('TABLE','ABC') from dual;
Wasim Akram Shaik

** Edit comment got denied because of another post, was about to post the same , to generate ddl ..
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
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.
slightwv (䄆 Netminder)

>>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
Wasim Akram Shaik

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
SOLUTION
Log in to continue reading
Log In
Sign up - Free for 7 days
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
johnsone

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.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
slightwv (䄆 Netminder)

>>I managed to find a workaround.

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

;)
johnsone

Your suggestion uses DBMS_METADATA to retrieve the create statement.  I'm not doing that at all.
slightwv (䄆 Netminder)

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.
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
johnsone

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

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

ASKER
Thank you.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.