14 Replies Latest reply: Dec 5, 2012 11:56 AM by rp0428 RSS

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

    977981
      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
          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
            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
              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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  i has to be sth connected to Conditional Functional Dependencies
                                  • 14. Re: ORA-00997: illegal use of LONG datatype during create table as
                                    rp0428
                                    >
                                    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';