DBAsupport.com Forums - Powered by vBulletin
Results 1 to 5 of 5

Thread: IMP-00058, ORA-00904, Invalid column name

Hybrid View

  1. #1
    Join Date
    Jan 2001
    Posts
    3,134

    Angry

    OS= Win NT
    Oracle ver= 8.1.6.1.0

    Howdy boys and girls. I have a weird error when trying to do an import from the same database. I exported the data and changed the table column names, not the types but the names and now I am getting this error. The .DMP file came from the same tables.
    Basically I added a few columns and changed the QTY to QY on some column names. I did this by dropping and re-creating the table.
    Suggestions??

    BEFORE
    Name Null? Type
    ------------------------------- -------- ----
    MARKET_ID NOT NULL NUMBER(10)
    PQS_SEG_START_DATE DATE
    PQ_ID NOT NULL VARCHAR2(10)
    PQS_ID NOT NULL VARCHAR2(8)
    PROD_GRP_TYPE_CD NOT NULL CHAR(4)
    PQS_SEG_ID NOT NULL CHAR(2)
    MKT_SEG_CD NOT NULL CHAR(3)
    BASIS_TYPE_CD NOT NULL CHAR(1)
    PERIOD_TYPE_CD NOT NULL CHAR(3)
    PRICE_DRV_MIN_QTY NOT NULL NUMBER(18,4)
    PROD_ID NOT NULL NUMBER(10)
    MKT_SEG_EFF_DATE NOT NULL DATE
    PROD_OFFER_EFF_DATE NOT NULL DATE
    ZONE NOT NULL CHAR(3)
    BILLING_OPTION_TYPE_CD NOT NULL CHAR(2)
    ACCESSORY_IR NOT NULL CHAR(1)
    PROD_CD CHAR(4)
    PROD_ATTR_1_ID NUMBER(10)
    PROD_ATTR_2_ID NUMBER(10)
    PROD_ATTR_3_ID NUMBER(10)
    PROD_ATTR_4_ID NUMBER(10)
    PROD_ATTR_5_ID NUMBER(10)
    PROD_ATTR_6_ID NUMBER(10)
    PRICE_DRV_MAX_QTY NUMBER(18,4)
    MEASURE_UNIT_CD CHAR(2)
    CURRENCY_CD CHAR(3)
    AMOUNT NUMBER(15,2)
    PRICE_TYPE_CD CHAR(1)
    PRICE_MEASURE_UNIT_CD CHAR(2)
    AMOUNT_FLOOR NUMBER(15,2)
    AMOUNT_CEILING NUMBER(15,2)
    PG_PLAN_ID VARCHAR2(40)
    AMOUNT_APPROVED NUMBER(15,2)
    AMOUNT_REQ NUMBER(15,2)
    AMOUNT_EQUIV NUMBER(15,2)
    PQS_TIER_ID NUMBER(9)
    REIMB_MTH_TYP_CD CHAR(1)
    ACT_ROLLUP_IR CHAR(1)
    ROLLUP_PRD_QY NUMBER(5)
    WK_AVG_INCL_CURR_WK_IR CHAR(1)
    USE_HIST_IR CHAR(1)
    TIER_TYP_CD CHAR(1)
    PG_PLAN_TYPE_CD CHAR(1)
    AMOUNT_REC NUMBER(15,2)
    AMOUNT_MAX NUMBER(15,2)
    AMOUNT_1 NUMBER(18,4)
    AMOUNT_2 NUMBER(18,4)
    AMOUNT_3 NUMBER(18,4)
    PRICE_PLAN_ID NUMBER(10)
    AMOUNT_EQUIV_APPROVED NUMBER(15,2)
    LNE_NR NOT NULL VARCHAR2(10)
    NCV_MIN_RPP_HIN_CD NOT NULL CHAR(2)


    AFTER...

    Name Null? Type
    ------------------------------- -------- ----
    MARKET_ID NOT NULL NUMBER(10)
    PQS_SEG_START_DATE DATE
    PQ_ID NOT NULL VARCHAR2(10)
    PQS_ID NOT NULL VARCHAR2(8)
    PROD_GRP_TYPE_CD NOT NULL CHAR(4)
    PQS_SEG_ID NOT NULL CHAR(2)
    MKT_SEG_CD NOT NULL CHAR(3)
    BASIS_TYPE_CD NOT NULL CHAR(1)
    PERIOD_TYPE_CD NOT NULL CHAR(3)
    PRICE_DRV_MIN_QY NOT NULL NUMBER(18,4)
    PROD_ID NOT NULL NUMBER(10)
    MKT_SEG_EFF_DATE NOT NULL DATE
    PROD_OFFER_EFF_DATE NOT NULL DATE
    ZONE NOT NULL CHAR(3)
    BILLING_OPTION_TYPE_CD NOT NULL CHAR(2)
    ACCESSORY_IR NOT NULL CHAR(1)
    PROD_CD CHAR(4)
    PROD_ATTR_1_ID NUMBER(10)
    PROD_ATTR_2_ID NUMBER(10)
    PROD_ATTR_3_ID NUMBER(10)
    PROD_ATTR_4_ID NUMBER(10)
    PROD_ATTR_5_ID NUMBER(10)
    PROD_ATTR_6_ID NUMBER(10)
    PRICE_DRV_MAX_QY NUMBER(18,4)
    MEASURE_UNIT_CD CHAR(2)
    CURRENCY_CD CHAR(3)
    AMOUNT NUMBER(15,2)
    PRICE_TYPE_CD CHAR(1)
    PRICE_MEASURE_UNIT_CD CHAR(2)
    AMOUNT_FLOOR NUMBER(15,2)
    AMOUNT_CEILING NUMBER(15,2)
    PG_PLAN_ID VARCHAR2(40)
    AMOUNT_APPROVED NUMBER(15,2)
    AMOUNT_REQ NUMBER(15,2)
    AMOUNT_EQUIV NUMBER(15,2)
    PQS_TIER_ID NUMBER(9)
    REIMB_MTH_TYP_CD CHAR(1)
    ACT_ROLLUP_IR CHAR(1)
    ROLLUP_PRD_QY NUMBER(5)
    WK_AVG_INCL_CURR_WK_IR CHAR(1)
    USE_HIST_IR CHAR(1)
    TIER_TYP_CD CHAR(1)
    PG_PLAN_TYPE_CD CHAR(1)
    AMOUNT_REC NUMBER(15,2)
    AMOUNT_MAX NUMBER(15,2)
    AMOUNT_1 NUMBER(18,4)
    AMOUNT_2 NUMBER(18,4)
    AMOUNT_3 NUMBER(18,4)
    PRICE_PLAN_ID NUMBER(10)
    AMOUNT_EQUIV_APPROVED NUMBER(15,2)
    LNE_NR NOT NULL VARCHAR2(10)
    NCV_MIN_RPP_HIN_CD NOT NULL CHAR(2)
    NCV_MAX_APV_DCT_PR NUMBER(15,2)
    NCV_MAX_APE_DCT_PR NUMBER(15,2)




    ORA-00904 invalid column name
    Sorry the table is so long, is this a known bug?

    Thanks in advance again,

    MH
    I remember when this place was cool.

  2. #2
    Join Date
    Oct 2001
    Location
    Madrid, Spain
    Posts
    763
    You can't do that. The column name must be the same. You shall do:

    1. Recreate the original table
    2. Import this table
    3. Create the new table
    4. Do: insert into new_table(column1,column2 ...) select cloumn1,clomn2 ... from old_table
    5. Drop the old table
    6. Rename new_table to old_table

    Hope that helps

    Angel

  3. #3
    Join Date
    Dec 2001
    Posts
    221
    what exactly r u trying to do. u've taken the export and then changed your table structure. so you r trying to import the tables after dropping from the database or just importing the data into existing table.

    ??????
    Santosh Jadhav
    8i OCP DBA

  4. #4
    Join Date
    Jan 2001
    Posts
    3,134
    Originally posted by aarroyob
    You can't do that. The column name must be the same. You shall do:

    1. Recreate the original table
    2. Import this table
    3. Create the new table
    4. Do: insert into new_table(column1,column2 ...) select cloumn1,clomn2 ... from old_table
    5. Drop the old table
    6. Rename new_table to old_table

    Hope that helps

    Angel
    Thanks guys!!

    That is exactly what I though arroyob but I was told that you can change the column names as long as the datatypes are the same, I disgreed.

    Basically we needed to add two columns and re-name some of the columns that had the name BLAH_BLAH_QTY
    to BLAH_BLAH_QY to conform to business standards. I just found out that this was incorrect and that I will NOT have to re-name the columns, thanks god. All I need to do now is ALTER ADD, much easier.

    Thanks again guys.

    MH
    I remember when this place was cool.

  5. #5
    Join Date
    Oct 2000
    Location
    Halifax, Nova Scotia
    Posts
    197
    You can add columns to a table and import back into them but you can not rename a column and import into it again.

    The reason for this is when you do an export it exports the data in the form insert into table_name ( all column names) values (data). When the import tries to do these inserts it finds a problem with the column name because they do not match. Another issue is you can not do an import into a table that has had a not null column added to it with no default value. The column needs to be populated by the inserts
    Don't be afraid to try something new. Amateurs built the Ark, professionals built the Titanic

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width