CODEWITHSUNDEEP
oracle-databasepostgresqldata-migrationora2pg
Shariq
Shariq

Reputation: 25

Ora2Pg fails to generate sql script with export type containing COPY in conf file

I am using ora2pg to migrate schema and data from oracle to postgres database.

The conf file has following modification:

  1. Provided ORACLE_HOME and DSN
  2. Set Export type as: TYPE, TABLE, PACKAGE, COPY, VIEW, GRANT, SEQUENCE, TRIGGER, FUNCTION, PROCEDURE, TABLESPACE, TYPE, PARTITION, MVIEW, QUERY (Removed FDW)

When I run the tool I get the following error:

DBD::Oracle::db prepare failed: ORA-01741: illegal zero-length identifier (DBD E RROR: OCIStmtPrepare) [for Statement "SELECT FROM ""."GT_LAYER_ENTITIES" a"] at D:/Dwimperl/perl/site/lib/Ora2Pg.pm line 8808. FATAL: ORA-01741: illegal zero-length identifier (DBD ERROR: OCIStmtPrepare) Aborting export...

The user that I am providing has DBA priviledges.

Please let me know if you have any solution for this.

Thanks

Upvotes: 1

Views: 6555

Answers (2)

firstpostcommenter
firstpostcommenter

Reputation: 2931

Sadly, it is not possible to export multiple stuff at the same time using ora2pg

Only one type of export can be perform at the same time so the TYPE directive must be unique. If you have more than one only the last found in the file will be registered.

enter image description here

Upvotes: 0

el fuser
el fuser

Reputation: 630

You do install the correct version of DBD-Oracle. You can prove with DBD-Oracle-1.74.
My advice, you don't use the configuration file for extract tables, view ect.
But first create a new project: 

ora2pg --project_base /home/user/ --init_project project_name

cd project_name/

ora2pg -c ora2pg.conf -t TABLE -o tables.sql -b schema/tables/

ora2pg -c ora2pg.conf -t SEQUENCE -o sequence.sql -b schema/sequences/

ora2pg -c ora2pg.conf -t TRIGGER -o trigger.sql -b schema/triggers/

ora2pg -c ora2pg.conf -t PROCEDURE -o procedure.sql -b schema/procedures

ora2pg -c ora2pg.conf -t VIEW -o view.sql -b schema/views/

ora2pg -c ora2pg.conf -t GRANT -o grant.sql -b schema/grants/

and after import on db postgres tables, sequence, etc... Finally, import the data on db.

ora2pg -c ora2pg.conf -t INSERT -o data.sql -b data/

It's most important that you firt create a psql database and user for your import.

Upvotes: 1

Related Questions