This discussion is archived
14 Replies Latest reply: Dec 5, 2012 9:56 AM by rp0428 RSS

ORA-00997: illegal use of LONG datatype during create table as

977981 Newbie
Currently Being Moderated
hello I need to create new table with structure like other one chosen by me. I've got table Countries(Country_id char, country_name varchar, region_id number) and while compiling :

create table countries_copy as (select * from all_tab_columns where owner in('HR') and table_name in ('COUNTRIES'));

i'm always getting error:
ORA-00997: illegal use of LONG datatype.

what i need to do?

P.S. sorry for my english.
  • 1. Re: ORA-00997: illegal use of LONG datatype during create table as
    Hoek Guru
    Currently Being Moderated
    Welcome to the forum.

    You make things more complicated than necessary, how about:
    create table countries_copy as select * from countries;
    add the appropriate schema name if needed.
  • 2. Re: ORA-00997: illegal use of LONG datatype during create table as
    Hoek Guru
    Currently Being Moderated
    In case I didn't explain well enough:

    Datadictionary view ALL_TAB_COLUMNS contains a column (DATA_DEFAULT) of LONG datatype:
    SQL> desc all_tab_columns
     Name                                      Null?    Type
     ----------------------------------------- -------- ------------------
     OWNER                                     NOT NULL VARCHAR2(30)
     TABLE_NAME                                NOT NULL VARCHAR2(30)
     COLUMN_NAME                               NOT NULL VARCHAR2(30)
     DATA_TYPE                                          VARCHAR2(106)
     DATA_TYPE_MOD                                      VARCHAR2(3)
     DATA_TYPE_OWNER                                    VARCHAR2(30)
     DATA_LENGTH                               NOT NULL NUMBER
     DATA_PRECISION                                     NUMBER
     DATA_SCALE                                         NUMBER
     NULLABLE                                           VARCHAR2(1)
     COLUMN_ID                                          NUMBER
     DEFAULT_LENGTH                                     NUMBER
     DATA_DEFAULT                                       LONG
     NUM_DISTINCT                                       NUMBER
     LOW_VALUE                                          RAW(32)
     HIGH_VALUE                                         RAW(32)
     DENSITY                                            NUMBER
     NUM_NULLS                                          NUMBER
     NUM_BUCKETS                                        NUMBER
     LAST_ANALYZED                                      DATE
     SAMPLE_SIZE                                        NUMBER
     CHARACTER_SET_NAME                                 VARCHAR2(44)
     CHAR_COL_DECL_LENGTH                               NUMBER
     GLOBAL_STATS                                       VARCHAR2(3)
     USER_STATS                                         VARCHAR2(3)
     AVG_COL_LEN                                        NUMBER
     CHAR_LENGTH                                        NUMBER
     CHAR_USED                                          VARCHAR2(1)
     V80_FMT_IMAGE                                      VARCHAR2(3)
     DATA_UPGRADED                                      VARCHAR2(3)
     HISTOGRAM                                          VARCHAR2(15)
    Since you're doing a CREATE TABLE ... AS SELECT * FROM ... you're hitting the error, since the definition of ALL_TAB_COLUMNS will be used and the LONG datatype is something you shouldn't use, since it is deprecated and only exists for backward compatibility with older database releases. Unfortunatly some DataDictionary views are also based on columns of LONG datatype.

    If you want to construct a query based on ALL_TAB_COLUMNS in order to generate en execute a CREATE TABLE script, then you'll need Dynamic SQL. However, creating objects/doing DDL through using Dynamic SQL is also not a recommended approach.

    You might want to have a look at DBMS_METADATA.GET_DDL:
    http://www.oracle-base.com/articles/9i/dbms_metadata.php
    http://docs.oracle.com/cd/E11882_01/server.112/e22490/metadata_api.htm#i1011012
  • 3. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    my job is to create table as above using execute immediate and get structure from all_tab_columns
  • 4. Re: ORA-00997: illegal use of LONG datatype during create table as
    BluShadow Guru Moderator
    Currently Being Moderated
    You want to create a copy of the all_tab_columns table? Why?
  • 5. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    no, no :) I've got table Countries(Country_id char, country_name varchar, region_id number).

    my job is to copy structure of that table and I need to do that using all_tab_column and execute immediate.
  • 6. Re: ORA-00997: illegal use of LONG datatype during create table as
    6363 Guru
    Currently Being Moderated
    bart_k_pl wrote:
    no, no :) I've got table Countries(Country_id char, country_name varchar, region_id number).

    my job is to copy structure of that table and I need to do that using all_tab_column and execute immediate.
    No, no.

    You do not need to use all_tab_column or execute immediate to copy the table.
  • 7. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    Yes I know that, but how otherwise I can copy table strucure using execute immediate and other view instead of all_tab_collums?
  • 8. Re: ORA-00997: illegal use of LONG datatype during create table as
    6363 Guru
    Currently Being Moderated
    bart_k_pl wrote:
    Yes I know that, but how otherwise I can copy table strucure using execute immediate and other view instead of all_tab_collums?
    You don't need any view, and you don't need execute immediate.
    SQL> desc countries
     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------------------
     COUNTRY_ID                                         VARCHAR2(10)
     COUNTRY_NAME                                       VARCHAR2(30)
     REGION_ID                                          NUMBER
    
    SQL> select * from countries;
    
    COUNTRY_ID COUNTRY_NAME                    REGION_ID
    ---------- ------------------------------ ----------
    FR         France                                  6
    
    SQL> create table country_copy as select * from countries where 0 = 1;
    
    Table created.
    
    SQL> desc country_copy
     Name                                      Null?    Type
     ----------------------------------------- -------- ---------------------------
     COUNTRY_ID                                         VARCHAR2(10)
     COUNTRY_NAME                                       VARCHAR2(30)
     REGION_ID                                          NUMBER
    
    SQL> select * from country_copy;
    
    no rows selected
  • 9. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    Yes i did that and it works well but i need to do that dynamicly because my promoter made me do this that way

    Edited by: bart_k_pl on 2012-12-05 08:14
  • 10. Re: ORA-00997: illegal use of LONG datatype during create table as
    6363 Guru
    Currently Being Moderated
    bart_k_pl wrote:
    Yes i did that and it works well but i need to do that dynamicly because my promoter made me do this that way
    You do this live on stage?

    If your promoter wants you to do something simple in the most complex, unreliable way possible, you should ask them how to do to it.
  • 11. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    so, back to question : how can I copy that structure using execute immediate?
  • 12. Re: ORA-00997: illegal use of LONG datatype during create table as
    6363 Guru
    Currently Being Moderated
    bart_k_pl wrote:

    so, back to question : how can I copy that structure using execute immediate?
    Back to the answer, since it is very unlikely anyone except your promoter would do something that stupid, you should ask them.
  • 13. Re: ORA-00997: illegal use of LONG datatype during create table as
    977981 Newbie
    Currently Being Moderated
    i has to be sth connected to Conditional Functional Dependencies
  • 14. Re: ORA-00997: illegal use of LONG datatype during create table as
    rp0428 Guru
    Currently Being Moderated
    >
    so, back to question : how can I copy that structure using execute immediate?
    >
    The same way 3360 did when he used standard SQL
    EXECUTE IMMEDIATE 'create table country_copy as select * from countries where 0 = 1';

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points