-
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.
-
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
-
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
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|