How to handle nested tables of Oracle

[RejoOommen]

Hi Darold, During a direct import of data from Oracle to Postgres got the below error. While checked at the Oracle end found that this is a nested table. Is there any way we can handle this in the tool.

Error: [2018-11-27 09:35:04] Retrieving partitions information... DBD::Pg::db do failed: ERROR: relation "dec_tbl" does not exist at /usr/local/share/perl5/Ora2Pg.pm line 5595. [2018-11-27 09:37:26] FATAL: ERROR: relation "dec_tbl" does not exist

[darold]

Please give me an Oracle DDL to reproduce the issue I will check what's going wrong with this.

[RejoOommen]

Am unable to retrieve the ddl for this object from oracle. Let me check again and get back. Thanks.

[darold]

Ok, I have reproduced the issue. The problem I think is just that you have not created the table in PostgreSQL, at least perhaps the import of this table failed because you have not imported the type before. You should have imported something like that for example:

CREATE TYPE user_typ AS (user_typ varchar(30)[]);
CREATE TABLE nested_table (
        id bigint,
        col1 user_typ
) ;

[darold]

I found a bug in data export of such tables, commit 9a60e4f might solve it.

[RejoOommen]

Hi Darold, The import was successful also the type and table was created in postgres. it ran into error post that as well. I tried with the latest commit as well, sorry to say that it did not help. Getting the same error. Have given the DDL below

ORACLE TABLE DDL BELOW:

CREATE TABLE “XYZ”.“CONFIG_TABLE”
  (    “CNFG_KEY” VARCHAR2(20) NOT NULL ENABLE,
    “CNFG_TYPE” VARCHAR2(3),
    “CNFG_STR_VAL” “XYZ”.“STR_ARRAY” ,
    “CNFG_DEC_VAL” “XYZ”.“DEC_ARRAY” ,
    “CNFG_INT_VAL” “XYZ”.“INT_ARRAY” ,
    “CRT_USER” VARCHAR2(15),
    “UPDT_USER” VARCHAR2(15),
    “CRT_TS” TIMESTAMP (6),
    “UPDT_TS” TIMESTAMP (6),
     CONSTRAINT “CONFIG_TABLE_PK” PRIMARY KEY (“CNFG_KEY”)
 USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS

 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE “DAT01"  ENABLE
  ) SEGMENT CREATION IMMEDIATE
 PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255
NOCOMPRESS LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE “DAT01”
NESTED TABLE “CNFG_STR_VAL” STORE AS “STR_TBL”
(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING

 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE “DAT01" ) RETURN AS VALUE
NESTED TABLE “CNFG_DEC_VAL” STORE AS “DEC_TBL”
(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE “DAT01” ) RETURN AS VALUE
NESTED TABLE “CNFG_INT_VAL” STORE AS “INT_TBL”
(PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 LOGGING
 STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645

 PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1
 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
 TABLESPACE “DAT01" ) RETURN AS VALUE

ORACLE TYPE SYNTAX

CREATE OR REPLACE EDITIONABLE TYPE “XYZ”.“DEC_ARRAY” AS TABLE OF DECIMAL(9,2)