Devart Forums

Posted: **Wed 21 Sep 2011 15:33**

I am trying to load (copy) data from Oracle to Postgres. I am using UniDAC.
My Table in Oracle looks like this...

SQL> desc countries
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------

 COUNTRY_ID                              NOT NULL NUMBER
 COUNTRY_ISO_CODE                  NOT NULL CHAR(2)
 COUNTRY_NAME                         NOT NULL VARCHAR2(40)
 COUNTRY_SUBREGION                NOT NULL VARCHAR2(30)
 COUNTRY_SUBREGION_ID           NOT NULL NUMBER
 COUNTRY_REGION                      NOT NULL VARCHAR2(20)
 COUNTRY_REGION_ID                 NOT NULL NUMBER
 COUNTRY_TOTAL                         NOT NULL VARCHAR2(11)
 COUNTRY_TOTAL_ID                    NOT NULL NUMBER
 COUNTRY_NAME_HIST                                 VARCHAR2(40)

This is common text and number datatypes.

I am copying this into a Postgres Table which looks nearly identical...

Code: Select all

CREATE TABLE COUNTRIES (
		COUNTRY_ID BIGINT PRIMARY KEY,
		COUNTRY_ISO_CODE CHAR (2),
		COUNTRY_NAME VARCHAR (40),
		COUNTRY_SUBREGION VARCHAR (30),
		COUNTRY_SUBREGION_ID BIGINT,
		COUNTRY_REGION VARCHAR (20), 
		COUNTRY_REGION_ID BIGINT,
		COUNTRY_TOTAL VARCHAR (11),
		COUNTRY_TOTAL_ID BIGINT,
		COUNTRY_NAME_HIST VARCHAR (40) 
)

DISTRIBUTED BY (COUNTRY_ID)
;

I am using a dataset to copy the data via UniLoader

Code: Select all

 OracleDS4Loader.Open;
// I Know this returns 23 rows....
 GPTable4Loader.Active := True;

 UniLoader1.LoadFromDataSet(OracleDS4Loader);

I get the error (on LoadFromDataSet)
.. raised exception class EPgError with message 'BINARY' is not supported.

The issue is this table has NO BINARY data.....
The error makes no sense.

Can anyone explain why this isn't working?
Thanks
GS

Posted: **Thu 22 Sep 2011 09:17**

Hello,

I cannot reproduce the problem.
Please try to execute the following code, and if the error persists, send us a script to create and fill Oracle table or a backup:

Code: Select all

var
  OracleUniConnection, PgUniConnection: TUniConnection;
  OracleUniTable: TUniTable;
  UniLoader: TUniLoader;
begin
  OracleUniConnection := TUniConnection.Create(nil);
  OracleUniConnection.ProviderName := 'Oracle';
  OracleUniConnection.Server := 'SERVER';
  OracleUniConnection.Username := 'user_name';
  OracleUniConnection.Password := 'password';
  OracleUniConnection.Connect;

  OracleUniTable := TUniTable.Create(nil);
  OracleUniTable.Connection := OracleUniConnection;
  OracleUniTable.TableName := 'COUNTRIES';
  OracleUniTable.Open;

  PgUniConnection := TUniConnection.Create(nil);
  PgUniConnection.ProviderName := 'PostgreSQL';
  PgUniConnection.Server := 'server';
  PgUniConnection.Port := 5432;
  PgUniConnection.Database := 'database';
  PgUniConnection.Username := 'user_name';
  PgUniConnection.Password := 'user_name';
  PgUniConnection.Connect;

  UniLoader := TUniLoader.Create(nil);
  UniLoader.Connection := PgUniConnection;
  UniLoader.TableName := 'COUNTRIES';
  UniLoader.LoadFromDataSet(OracleUniTable);

P.S. UniDAC: 4.0.1, Oracle: 10.2, PostgreSQL: 9.0, Delphi 7

Posted: **Thu 22 Sep 2011 17:23**

Test case sent via separate email. I am able to reproduce the issue.
Thank you

Posted: **Fri 23 Sep 2011 08:10**

Hello,

I've imported your data to our Oracle 11.2 server and executed the code you specified. And all data from the Oracle table was imported to PostgreSQL correctly.
Please specify your version of UniDAC. I checked this situation with the latest UniDAC version (4.0.1), Oracle 11 R2, POstgreSQl 9.0, Delphi 2010.

Devart Forums

Odd Error with UniLoader

Odd Error with UniLoader

Test case sent via seperate email