[Oracle] Issue in importing data of tables having reserved keyword of datatype as table names in Oracle

[priyanshi-yb]

If there is a table having a name as a reserved keyword of datatype in Oracle. There is an issue in dumping the incorrect data dump for that table and importing that exported data in target might fail with syntax errors.

Workaround: Modify the data to correct syntax as per the datatype exported for that table.

Example

An example schema on the source database is as follows:

create table "number"(id int PRIMARY KEY);
insert into "number" values(0);
insert into "number" values(1);
insert into "number" values(2);
insert into "number" values(3);
insert into "number" values(4);

The exported schema and data are as follows:

create table "number"(
	id numeric(38) NOT NULL,
	PRIMARY KEY (id)
);

COPY number (id) FROM STDIN;
f
t
2
3
4
\.

Error when import the data is as follows:

ERROR: invalid input syntax for type numeric: "t" (SQLSTATE 22P02), COPY number, line 1, column id: "t" 

Workaround for the example is to modify the data to change the f/t to 0/1 respectively:

COPY number (id) FROM STDIN;
0
1
2
3
4
\.