0

I have an Oracle Enterprise Edition as a source and taken the dump using the oracle data pump(expdp) and the dump is imported using (impdp) to an Express Edition of Oracle. The import was working perfectly without any error if the target database is Express Edition ( 11.2.0.2.0). But when i import the same data into the oracle Standard Edition then i'm getting the following error.

  1. ORA-31693: Table data object "SchemaName"."Table_Name" failed to load/unload and is being skipped due to error:
  2. ORA-02354: error in exporting/importing data

  3. ORA-26097: unsupported conversion for column SYS_NC0000500006$ (from type 58 to type 23)

The table has a column of custom type which is made up of table of number.

 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 APP_ID                                    NOT NULL  NUMBER(38)
 DATE                                      NOT NULL  DATE
 P_ID                                      NOT NULL  NUMBER(38)
 D_ID                                                VARCHAR2(200)
 I_ARRAY                                             NUMBER_ARRAY

The error was caused because of the column I_ARRAY. When i insert the query like following it works perfectly.

insert into PAS_IS_ALERT_DETAIL_LOG values(2, '17-FEB-14',131, 'duo_duo', NUMBER_ARRAY(16183, 16345));

Can some-one help me out to figure the issue. How does it's not working only with the Oracle Standard Edition of 11.2.0.1.0. Is changing the version of SE to other release like 11.1.0.1.0 will solve this issue?

Note : There is no way to change the dump file.

  • 58 is the code for ANYDATA, 23 is for RAW. I guess those user-defined types may be represented differently in different Oracle versions. Make sure the version (not edition) of the target database is the same as the source. – mustaccio Oct 15 '14 at 0:18
  • Is there any documentation to check this? I'm new to Oracle so not getting any idea about it. – Arung Oct 15 '14 at 5:51
  • Are you exporting/importing a single table, or the full database? I wonder if the custom type you're looking for needs to be imported first... – captjackvane Oct 16 '14 at 20:08
  • @captjackvane i'm doing import/export for a schema. I need to try what you mentioned as first. will update you. – Arung Oct 17 '14 at 7:15

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Browse other questions tagged or ask your own question.