Converting an Oracle View like:
CREATE or REPLACE VIEW dave_test_view AS
SELECT
'1' as one,
'field_from' as two
FROM dual
;

Results in an invalid translation:
-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4
-- Copyright 2000-2013 Gilles DAROLD. All rights reserved.
-- DATASOURCE: dbi:Oracle:host=hitsdev;sid=HITSDEV

SET client_encoding TO 'LATIN1';

SET search_path = hits;CREATE OR REPLACE VIEW dave_test_view (one, two) AS SELECT
'1' as one,
FROM' as two
FROM dual ;

Notice it has replaced 'field_from' with just FROM'

Not sure if this has anything to do with the use of quotes or lowercase-conversion, but from what I can tell the application seems to skip all tables that contain the dollar sign ($). For instance when exporting tables there is no create statement for the table ProcedureResult$Operation, but it did try to create a foreign key referring to it. This obvoiously failed when running the script in postgresql.

Version in use: 9.2

And a great big thanks for the quick responses on the other issues I've posted this far.

When I make direct data table copy from oracle to postgresql with parallel (jobs = 4 copies = 4) and set TRUNCATE_TABLE = 1, some truncate sentence fail with:

DBD::Pg::db do failed: no connection to the server at /usr/lib/perl5/site_perl/5.8.8/Ora2Pg.pm

Hi,
I am a beginner.I am trying to migrate from my oracle to pg.But when I keep TYPE INSERT or TYPE COPY in the conf file , I am getting an error Handles cannot be shared between threads driver may need a clone method ,but if I keep TYPE TABLE , it is working fine.Please help me

Shouldn't EXCLUDE option be taken into account when restoring table indexes?

I am using ora2pg in combination with hibernate reverse engineering.

So, my table list can be computed from hibernate configuration files.

I did not find a way to specify the list of tables to export as a command line parameters.

Something like --tables that would have the same effect than the TABLES keywork in ora2pg.conf and would override it when specified.

An alternative would be to allow the execution of shell commands in ora2pg.conf, in a way similar to bash scripts.

I mean, something like :

TABLES grep match-table hibernate.reveng.xml | awk '{print $3;}' | sed -e "s/match-table=\"//g" -e "s/\"\/>//g"

(not optimal way to extract tablenames, just an example).

This is clearly a low priority request, but it would be nice. :-)

ora2pg is generating this insert which fails:

INSERT INTO pp$provider_services (id,enabled,ext_service_id,priority,transform,provider_id,service_id,src,cdat,dealer_id) VALUES (30563049,'t',E'739556817',1,'t',25,499,E'var params;^M
var preparedParams;^M
preparedParams.put("phoneNumber", params.get("account"));^M
preparedParams.put("params[\''type\'']", "7");','2013-03-22 20:44:42',27249468);

Source text in Oracle for the SRC column is

var params;
var preparedParams;
preparedParams.put("phoneNumber", params.get("account"));
preparedParams.put("params[\'type\']", "7");

Proper escaping is:

=> select E'params[\\\'type\\\']';
 ?column?
------------------
 params[\'type\']
(1 row)

So basically for some reason ora2pg thinks the backslash should not be escaped at all, while the single quote should be doubled, which is wrong for E'' notation.

After export:

COMMENT ON COLUMN some_table.col1 IS E'What''s that?';

should be

COMMENT ON COLUMN some_table.col1 IS E'What''s that?';

I have some indexes with ampersands in their names. I had to modify the output.sql from :

CREATE UNIQUE INDEX item_uom_conv&item_no&ix3 ON [...]

to :

CREATE UNIQUE INDEX "item_uom_conv&item_no&ix3" ON [...]

to make it work with psql (9.2.2).

It gives

ALTER TABLE bin$4adnia7iyt3gqab/aqeikg==$0 ADD CONSTRAINT bin$4adnia6/yt3gqab/aqeikg==$0 CHECK ("STATE" IS NOT NULL);

Ora2pg generates:

CREATE UNIQUE INDEX idxname
    ON sometable (case col1 when 'abc' then user_id else null end);

Which fails on syntax error, due to:

expression
An expression based on one or more columns of the table. The expression usually must be written with surrounding parentheses, as shown in the syntax. However, the parentheses can be omitted if the expression has the form of a function call.

If I add ( ) around the expression it works.

Clarification: it works for ((case ...)).

When running

ora2pg -o report.html -t SHOW_REPORT --estimate_cost --cost_unit_value 10 --dump_as_html

against a UTF-8 database (with german umlauts) it starts with this error

2/14 objects types (14.3%) inspecting object FUNCTION    
Malformed UTF-8 character (unexpected non-continuation byte 0xdf, immediately after start byte 0xf6) in pattern match (m//) at /usr/local/share/perl/5.14.2/Ora2Pg/PLSQL.pm line 540.

followed by errors in lines: 542, 544, 546, 548, 550, 552, 554, 556, 558, 560, 562, 564, 566, 568, 570, 572, 574, 576, 578, 580, 582, 585, 587, 589, 591, 593, 595, 597 where line 540 matches

my $n = () = $str =~ m/\bFROM[\t\s]*\(/igs;

My perl version is 5.14.2.

As for version 11, ora2pg can handle SQL files and translate procedures, functions or packages inside it.

We often meet the case when we have Oracle SQL files corresponding to either DDL or specific data processing, which have to be ported to PostgreSQL.
These files usually contain classic SQL DDL or queries, anonymous pl/sql blocks, functions and procedures.

As ora2pg can already handle procedures, functions and packages, it should theoritically be able to convert those SQL files into a PostgreSQL dialect, standard SQL or Pl/PgSQL.

Would it be difficult to add this feature to the command line options ?

This is probably more of a support request than an issue; we are retrieving data from an Oracle db that defines unique indices with names that overlap with table names. Is there a way to handle this? I've seen keep_pkey_names, would something similiar apply to other indices?

Hi, I'm getting unquoted "cmax" column name in output.sql even though USE_RESERVED_WORDS is set to 1 and

ORA_RESERVED_WORDS  audit,comment,cmax,cmin,oid,tableoid,xmin,xmax,ctid

ora2pg 12.1

Hi,

We're migrating from one hosting provider to another and at the same time migrating from Oracle to Postgresql.

I'm running the ora2pg script in the receiving end so I do the conversion at the new and bigger cpu's.

We've setup a SSH tunnel from the new to the old provider like described here: http://osdir.com/ml/lang.perl.modules.dbi.general/2008-01/msg00073.html

But I'm not satisfied with the throughput. It's only running at 2/3 the speed of doing a local oracle to local postgresql conversion.

And at the start of every new table it has a long pause and almost a stand still when migration a CLOB to a TEXT.

Both providers are connected to the same 100's / Mb if not Gb's backbone so the connection should be good enough..

I been advised not to use the SSH tunnel as it might be part of the network latency but I don't want to transfer our data through the internet unencrypted.

Oracle has the possibility to SSL encrypt the database connection: http://docs.oracle.com/html/B10812_06/chapter5.htm#BIICGDGC

I'm waiting for the old hosting provider to set this up but in the meantime I wanted to find out how to setup ora2pg for this..

Look at the source code of Ora2Pg.pm it doesn't seem to be possible?

And I not sure if DBD is up to it either even if ora2pg was patched?

Unless DBD/DBI::Oracle automatically use SSL if connected to an SSL port. Do you know if that is true?

Hi,

I have installed the latest Version of ora2pg (12.1) under Debian Wheezy with Oracle Thin Client 10.2.0.3 (newer version doesnt support Oracle 8i) Perl DBD:Oracle 1.70. When trying to connect to the database with this ora2pg version (even just -t SHOW_TABLE) i get the following error

FATAL
Aborting export...

and nothing else (even with debug enabled). So I've installed ora2pg 11.4 and it seems to work just fine. So I guess I'm going to stick with that version for the time being ;-)
BTW: Thanks for the awesome work

Hi
when we use the user connection to scan the oracle schema

we have this error.
DBD::Oracle::db prepare failed: ORA-00942: table or view does not exist (DBD ERROR: error possibly near <> indicator at char 33 in 'SELECT sum(bytes)/1024/1024 FROM <>DBA_DATA_FILES') [for Statement "SELECT sum(bytes)/1024/1024 FROM DBA_DATA_FILES"] at /usr/local/share/perl5/Ora2Pg.pm line 6780.

to avoid this error you can use this request .

SELECT sum(bytes)/1024/1024 FROM user_segments;

pierre

At the end of the export I get:

Can't call method "disconnect" on unblessed reference at /home/oracle/perl/lib/perl5/site_perl/Ora2Pg.pm line 2985.

I found an error of translation on the triiger..

Oracle Trigger

create or replace 
trigger "SIBMIG"."VAL_PRO_DELETE" BEFORE
DELETE ON "VALIDATEUR_PROJET" 

BEGIN
declare
v_tem_sel_ok number(1) := 0;
begin
-- code ....
end;
END;

Translate into :

​CREATE OR REPLACE FUNCTION trigger_fct_val_pro_delete () RETURNS trigger AS $BODY$
BEGIN

declare
v_tem_sel_ok numeric(1) := 0;
begin
​--- code 
end;
RETURN NEW;
END
$BODY$
 LANGUAGE 'plpgsql';

​​CREATE TRIGGER val_pro_delete
    BEFORE STATEMENT DELETE ON validateur_projet FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_fct_val_pro_delete();

The good syntaxe is :

​​CREATE TRIGGER val_pro_delete
    BEFORE DELETE ON validateur_projet FOR EACH STATEMENT
    EXECUTE PROCEDURE trigger_fct_val_pro_delete();

Best Regards
Pierre

Scripts generated by ora2pg always on contain a "ON_ERROR_STOP on" line.

It can be useful to continue even if some errors are encountered.

Although one can easily automatically remove such lines with sed, it would be great to be able to remove those lines through configuration.

I can't use the postgresql 9.3 implementation of materialized views as I need to have read access to the materialized views while they are refreshed. I have to create my own refresh procedure.

In ora2pg 11.4 materialized views where migrated as a table with data, a view and a stored procedures to refresh the table.

The sql part of the materialized view doesn't migrate to clean postgresql sql so I took the code, cleaned it and saved it to execute manually.. The table definition and data was migrated with TYPE TABLE SEQUENCE and TYPE COPY - perfect.

But with 12.0 and PG_SUPPORTS_MVIEW 0 the table for the materialized view is't migrated. If I migrate with TYPE TABLE SEQUENCE MVIEW the refresh views and calls to the stored procedure is produced. But not the definition of neither the table or the stored procedure.

I've tried adding the materialized views to VIEW_AS_TABLE without luck.

I would like to either have the 11.4 functionality of getting the materialized view table defintion and data when doing TYPE TABLE SEQUENCE and TYPE COPY migrations or to have a MVIEW_AS_TABLE variable.

Of course this is something that can be handled in a separate step after the migration is done, but at least for my use case it would be smooth if I could specify a list of columns of type smallinteger that should be converted to boolean.

A more generic approach could be to create an easy way to extend the application with custom perl-methods for special case data-conversion.

ERROR: invalid input syntax for type double precision: "1239,88" at C:/Perl64/site/lib/Ora2Pg.pm line 6218.

Export type INSERT, COPY works fine

Setting PRESERVE_CASE to 1 causes exported table comments looks like this:

COMMENT ON TABLE "SMART_TABLE"."" IS E'Some smartass comment';

Should be

COMMENT ON TABLE "SMART_TABLE" IS E'Some smartass comment';

Hi. I'm using ora2pg v10.1 to migrate an Oracle 10 database to PostgreSQL 9.2, and everything seems to work fine, but today I found that all the Oracle CHECK constraints that seem to have an auto generated name (like SYS_C00879224) are not being migrated. The CHECK constraints with user assigned names are migrated though.

So thanks in advance for your help, and best regards.

I have a mixture of field types in oracle representing booleans:

• 5 character varchar2's containing text True or False (capitalized).
• 1 digit numeric containing 1 or 0.
• 3 character varchar2's containing text ja or nej (danish for yes and no).

I would like cleanup this mess during migrating and make them all into booleans.

I made a long list of fields like
REPLACE_AS_BOOLEAN periode:disabled kundeaftale:saetadressevedbooking billede:disabled virksomhedlink:disabled v_facilitetdef:gruppevisning ..................

and configured the value mapping as:
BOOLEAN_VALUES True:False 1:0 ja:nej

The fields are converted to boolean fields but in the generated copy file all the fields has a t for true.

Is this misconfiguration or a bug in ora2pg?

With option CASE_SENSITIVE 0 ora2pg still gave the following output in schema export:

CREATE TABLE "queue" (
        "id" bigint NOT NULL,
        "priority" bigint NOT NULL,
        "queuedtime" bigint NOT NULL,
        "state" bigint,
        "task_id" bigint NOT NULL
);
ALTER TABLE "queue" ADD PRIMARY KEY ("id");
CREATE INDEX idx_queue_pri ON "QUEUE" ("priority");
CREATE INDEX idx_queue_queuedtime ON "QUEUE" ("queuedtime");

I would expect it to either convert names to lowercase in create index statements or not use double quotes which disables case-insensitive match.

When replicating a subset of tables, the table total displayed in the progress bar is wrong. It displays the total number of tables, and not the subset size.

In my case, the orignal oracle database contains 216 tables. I replicate only 99, but
"dumped 77 of 216 tables " is for example displayed instead of "dumped 77 of 99 tables".

Dear all, since version 11.4 (at least, same "bug" (?) in 12.0), ora2pg tries to create foreign keys on tables that are not selected for export.

I have this kind of conf file :

SKIP check
USER_GRANTS 1
EXPORT_SCHEMA 0
DROP_FKEY 1

TABLES table1 table2 table3

in the generated scripts, I find queries such as :

ALTER TABLE table1 ADD CONSTRAINT fk_table1_table4_toto FOREIGN KEY (table4id) REFERENCES table4 (id) ON DELETE NO ACTION NOT DEFERRABLE INITIALLY IMMEDIATE;

Do I miss something in configuration or is this a regression ?

I have a problem of coherency when replicating an oracle database in a postgresql database.

Some new records are inserted in the database while replication is performed, as this is a live, production, database. Often, the replication fails because "table a" was replicated at t1 and "table b" was replicated at t2 and an integrit constraint is invalid.

I can use a workaround : filter out the

\set ON_ERROR_STOP ON
BEGIN;
COMMIT;

lines in the insertion script, so that insertion keeps on excepted for the newest incoherent value. However, the database would not be always be coherent (which is not a problem to me in my current use of ora2pg) and the insertion would be slow like hell.

Would it be possible to have a mode where all data table export is performed in a single transaction ?

I already tried to use

TRANSACTION committed

but with no success.

Since 11.4 the foreign key strategy has changed..

I use
TYPE COPY
and
FILE_PER_TABLE 1

In 11.4 I get an output.sql where:

• the foreign key constraints of all tables are dropped.
• Then the individual table files a included.
• And finally all of the constraints are recreated.

In commit 5edc4e5 (which you suggested earlier today :)) I get an output.sql where the individual table files a included (but no drop constraints). Within each table file the constraints of the table are dropped and recreated.

But as the tables are exported alfabetically and not in a foreign key conforming order this results in foreign key constraints errors as the earlier tables depends on the latter.

Is there a workaround or new setting that I missed? Otherwise I find this a regression..

Exporting 35G of data using COPY leads to someone closing the Oracle connection -- whether it's ora2pg, Oracle itself or something isn't clear, ora2pg just reports that it's trying to operate on a closed connection and fails. It happens between 20 and 50 minutes of work.

As soon as I switch export to INSERT, ora2pg completes without a single error. And the rows per second is the same for both cases.

I'm migrating some data to Postgres, where the database is created using UTF8. The Oracle server says its encoding is WE8MSWIN1252, which I think breaks down to WIN1252, which I see available on Postgres as well.

Anyway, the initial Oracle (WE8MSWIN1252) to Postgres (UTF8) migration resulted in some text fields having characters converted to question marks.

I tried backing up that Postgres database and restoring it to another Postgres database where the encoding was set as WIN1251. This didn't fix the question marks. I'm thinking then, then encoding issues occurred during the ora2pg data dump phase.

I see in the ora2pg config file that there are some settings for specifying the encoding, and they're all set to UTF8. Should these be set to what the Oracle server is (WIN1251) or what the desired output should be (UTF8)?

I'm also ok with not using UTF8, I just need whatever configuration will result in not having that loss of text value. No question marks.

Any ideas?

Hi,

I am trying ora2pg (11.1) to import data from Oracle 11x tables and ran into error for tables that have data in the LONG RAW type columns.

DBD::Oracle::db prepare failed: ORA-00997: illegal use of LONG datatype (DBD ERROR: error possibly near <> indicator at char 395 in 'SELECT "VERSION_SEQ_KEY","VERSION_NUMBER","DESCRIPTION","ISACTIVE","ICON","AUTHOR_NAME","AUTHOR_COMPANY","AUTHOR_EMAIL",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"CREATED_BY",to_char("UPDATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"UPDATED_BY","MAX_NUMBER","MIN_NUMBER","PARENT_KEY","NAME","RELEASE_NOTES","DTS_SEQ_KEY","DDSVERSION_SEQ_KEY","TEARDOWN_TIME","SETUP_TIME",utl_raw.cast_to_varchar2(<>"DTSVO"),"SOURCE" FROM TABLE_FOO a') [for Statement "SELECT "VERSION_SEQ_KEY","VERSION_NUMBER","DESCRIPTION","ISACTIVE","ICON","AUTHOR_NAME","AUTHOR_COMPANY","AUTHOR_EMAIL",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"CREATED_BY",to_char("UPDATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"UPDATED_BY","MAX_NUMBER","MIN_NUMBER","PARENT_KEY","NAME","RELEASE_NOTES","DTS_SEQ_KEY","DDSVERSION_SEQ_KEY","TEARDOWN_TIME","SETUP_TIME",utl_raw.cast_to_varchar2("DTSVO"),"SOURCE" FROM TABLE_FOO a"] at /usr/local/share/perl5/Ora2Pg.pm line 5983.
FATAL: ORA-00997: illegal use of LONG datatype (DBD ERROR: error possibly near <> indicator at char 395 in 'SELECT "VERSION_SEQ_KEY","VERSION_NUMBER","DESCRIPTION","ISACTIVE","ICON","AUTHOR_NAME","AUTHOR_COMPANY","AUTHOR_EMAIL",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"CREATED_BY",to_char("UPDATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),"UPDATED_BY","MAX_NUMBER","MIN_NUMBER","PARENT_KEY","NAME","RELEASE_NOTES","DTS_SEQ_KEY","DDSVERSION_SEQ_KEY","TEARDOWN_TIME","SETUP_TIME",utl_raw.cast_to_varchar2(<>"DTSVO"),"SOURCE" FROM TABLE_FOO a')
Aborting export...

On 10.1, the import works. However, I saw that there was some bug related to this type that was fixed in 11.1 and therefore I was trying it again with 11.1.

When exporting a date field from oracle to timestamp in postgres the time information goes missing and only the date gets exported.
In my case the date in oracle is by default formated as follows

1999-12-31T23:59:59+01:00

The "T" inside the format might be the culprit.
I found out that ora2pg sets NLS_TIMESTAMP_FORMAT before exporting, because it expects timestamps to be in that format.
It might help to also set NLS_DATE_FORMAT explicitly doing something like

ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'

before export.
Haven't tested it yet though.

I'm trying to import from Postgres into Oracle and am receiving an error due to a Trigger's code. In Oracle, the trigger looks like this ...

create or replace
TRIGGER "FLM_ADMIN".bifer_eSearchCust_id_pk
  before insert on eSearchCust
  for each row
begin
  select eSearchCust_seq.nextval
  into :new.id
  from dual;
end;

When this gets moved over to Postgres, and then data is loaded in, this throws an error saying that the schema "new" does not exist.

Dumping data from table ESEARCHCUST...
Looking for data from ESEARCHCUST...
Fetching all data from ESEARCHCUST...
DEBUG: Preparing bulk of 10000 data for output
DEBUG: Creating output for 10000 tuples
DEBUG: Sending COPY bulk output directly to PostgreSQL backend
DBD::Pg::db pg_putcopyend failed: ERROR:  schema "new" does not exist
CONTEXT:  SQL statement "SELECT nextval('esearchcust_seq')
  into NEW.id"
PL/pgSQL function "trigger_fct_bifer_esearchcust_id_pk" line 3 at PERFORM
COPY esearchcust, line 1: "3582 Harris,Glen F   1904205 246179, 246179  222102" at /usr/local/share/perl/5.12.4/Ora2Pg.pm line 5735.
FATAL: ERROR:  schema "new" does not exist
CONTEXT:  SQL statement "SELECT nextval('esearchcust_seq')
  into NEW.id"
PL/pgSQL function "trigger_fct_bifer_esearchcust_id_pk" line 3 at PERFORM
COPY esearchcust, line 1: "3582 Harris,Glen F   1904205 246179, 246179  222102"
DBI::db=HASH(0x20cb440)->disconnect invalidates 1 active statement handle (either destroy statement handles or call finish on them before disconnecting) at /usr/local/share/perl/5.12.4/Ora2Pg.pm line 5565.
Aborting export...

The "new" is not a schema, but specified to insert that value into the newly created. Does this tool not properly adjust that or know what to do with that? I thought some kind of conversion would be done. Do I need to adjust these triggers manually?

I use ora2pg to replicate subsets of oracle databases.

On a subset export, ora2pg tries to create foreign keys on tables that are not in my table selection.

Configuration should allow to disable foreign key creation for tables that are not in the table selection.

Again single quotes.
Currently an oracle string like

abc''def

is escaped to

abc'''def

which is obviously wrong.
It seems the regular expression matching the single quotes only matches the first occuring single quote and not the immediatly following one.

I'm not sure what to do about it.

As per README installed Ora2Pg software. After Installation didn't get /usr/local/bin/ora2pg file.

When I tried to connect to oracle by using ora2pg by using the following command, getting below error:

/Ora2Pg/scripts> ./ora2pg -u username -w pass -o /home/ABC8477/Migration/test.sql
Trying to connect to database: dbi:Oracle:host=...t;sid=102
DBI connect('host=...;sid=102','username',...) failed: ORA-12541: TNS:no listener (DBD ERROR: OCIServerAttach) at /usr/lib/perl5/site_perl/5.10.0/Ora2Pg.pm line 907
Can't call method "begin_work" on unblessed reference at /usr/lib/perl5/site_perl/5.10.0/Ora2Pg.pm line 919.

How do I know OR Crosscheck Ora2Pg installation was successful?

Regards
Ravi

Hey, thanks for a great product I'm looking forward to using it for our migration. However, I've run into an issue I can't get past after a couple of days reading documentation and trying different combinations.

Yesterday, I pulled down the latest version of the code from github and reinstalled in case it was a known issue. But I still have the same problem.

I've configured it to use the default system account and password. Why that was never changed, I don't know, but it's on an isolated network.

I'm able to export all of my TABLE definitions, sequences, grants, type definitions and functions. But when it comes to exporting the data from the tables, I'm consistently running into the same error:

ora2pg -t COPY -o DRAD.sql -s "dbi:Oracle:host=localhost;sid=DRAD" -l DRAD.log

DBD::Oracle::db prepare failed: ORA-00936: missing expression (DBD ERROR: error possibly near <> indicator at char 8 in 'SELECT <>FROM ACCESS$ a') [for Statement "SELECT FROM ACCESS$ a"] at /usr/local/perl-5.16.3/lib/site_perl/5.16.3/Ora2Pg.pm line 5980.
Aborting export...

The same thing occurs with INSERT.

I don't really need the ACCESS$ information preserved as this app is always run as a single system user at the moment. I'll be adding acess rights to it after it's migrated to postgres.

I tried adding this line to the ora2pg.conf to prevent exporting the ACCESS$ table, but got the same results:
EXCLUDE ACCESS$

It looks like it's not building the query correctly and I had step through the debugger around it, but couldn't see what the problem is.

Thanks for your assistance,

Bruce

All views in my database are exported as tables - VIEW_AS_TABLE is commented

OS version: OS X 10.6.8

The install_all routine copies files using the "--force" option which was invalid on this Mac, perhaps all recent ones.

I had tested a table with a "raw" column.

SQL> create table tt (c raw(100));
Table created.
SQL> insert into tt (c) values (hextoraw ('ABBAFF'));
1 row created.
SQL> select dump(c), c from tt;

DUMP(C)

C

Typ=23 Len=3: 171,186,255
ABBAFF

The tool ora2pg results a table file and a copy file.

ora2pg -t TABLE -c x.conf -o TABLE.sql

-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4
SET client_encoding TO 'LATIN1';

\set ON_ERROR_STOP ON

CREATE TABLE tt (
c bytea
);

ora2pg -t COPY -c x.conf -o COPY.sql

-- Generated by Ora2Pg, the Oracle database Schema converter, version 11.4

SET client_encoding TO 'LATIN1';
\set ON_ERROR_STOP ON
TRUNCATE TABLE tt;
BEGIN;
COPY tt (c) FROM STDIN;
ABBAFF
.
COMMIT;

In the COPY statement I am missing the escapes of the binary output like this: \xABBAFF
If I import this without the escapes in a postgresql DB 9.x the result is a string "ABBAFF" and
not the hex bytes 0xAB 0xBA 0xFF.

select * from tt;

   c        

\x414242

With the escapes this works correct:
COPY tt (c) FROM STDIN;
\xABBAFF
.

select * from tt;

c     

\xabbaff
(1 row)

Hi ,
when I am converting from oracle to postgresql the xml data is not importing properly.I am getting error

DBD::Oracle::st fetchall_arrayref failed: ORA-31011: XML parsing failed
ORA-19202: Error occurred in XML processing
LPX-00217: invalid character 145 (U+0091)
Error at line 2 (DBD ERROR:

) [for Statement "SELECT "ID","USERID","
FF_ID",a."HISTORY".extract('/').getClobVal(),"CURENT_VAL","SHARE_PERMS","SHARE_WORKING_GROUP","CREATED_BY","MODIFIED_BY",to_char("CREATED_DATE", 'YYYY-MM-DD HH24:MI:SS'),to_char("MODIFIED_DATE", 'YYYY-MM-DD HH24:MI:SS') FROM KMUSER.KM_REL_FF_USR_DATA a"] at C:/Perl/site/lib/Ora2Pg.pm line 5705.
[> ] dumped 7 of 23298 rows (0.0%) table KM_REL_FF_USER_DATA

I am new to ora2pg.Please helpme out to resolve this.

Hi,

Dunno if it's a bug or a problem in the source database bu I get the following output when exporting INSERTs with ora2pg :

[oracle@oracle2 ~]$ ora2pg
[========================>] 28/28 tables (100.0%) end of scanning.
DBD::Oracle::db prepare failed: ORA-00936: expression absente (DBD ERROR: error possibly near <> indicator at char 8 in 'SELECT <>FROM VALDEFI.BIN$3FJEPNWUARNGQAEKBIUUYG==$0 a') [for Statement "SELECT FROM VALDEFI.BIN$3FJEPNWUARNGQAEKBIUUYG==$0 a"] at /usr/lib/perl5/site_perl/5.8.8/Ora2Pg.pm line 6067.
FATAL: ORA-00936: expression absente (DBD ERROR: error possibly near <> indicator at char 8 in 'SELECT <>FROM VALDEFI.BIN$3FJEPNWUARNGQAEKBIUUYG==$0 a')
Aborting export...

This software looks very promising, but a progress bar or any kind of estimation towards how many records remain to be exported would be a welcome improvement. I just want some general idea of whether there is a couple of hours or several days left.

As far as I can see the only way not to migrate unused columns is to drop them before migration?

I have a rather large table in Oracle 130+ million total 6+ GB where I have some unused columns and it takes a very long time to remove the unused columns before migration.

It would be nice if a could exclude some columns with a syntax similar to the REPLACE_COLS option.

On simple constraint is ok

ALTER TABLE "SOME_TABLE" ADD CONSTRAINT SOME_CONSTRAINT CHECK ("SCOLUMN" IS NOT NULL);

But when some condition is involved then it's missing quotation marks

ALTER TABLE "SOME_TABLE" ADD CONSTRAINT SOME_CONSTRAINT CHECK (SCOLUMN > 0);

Using ActiveState 5.16.3 or Strawberry Perl 5.16.3, sale issue occurs:

ERROR:
C:\scripts>ora2pg -c ora2pg_dist.conf
[========================>] 1/1 tables (100.0%) end of scanning.
DBD::Oracle::db STORE failed: handle 2 is owned by thread 333a8c not current thread 1337ffc (handles can't be shared between threads and your driver may need a CLONE method added) at C:/Perl/site/lib/Ora2Pg.pm line 6856.
DBD::Oracle::db STORE failed: handle 2 is owned by thread 333a8c not current thread 907dd3c (handles can't be shared between threads and your driver may need a CLONE method added) at C:/Perl/site/lib/Ora2Pg.pm line 6038.

Version 10.1 of Ora2Pg works just fine with same config file.