Thread: Oracle to PostgreSQL

Hi,

I have almost 1300 files from Oracle (no access to oracle server).  I
have to create the tables and data as follows.

-- start script

-- file name:  aa_asset_type.sql

CREATE TABLE AS_ASSET_TYPE
(
  MAIN_TYPE         CHAR(3)                     NOT NULL,
  SUB_TYPE          NUMBER(3)                   NOT NULL,
  DESCRIPTION       VARCHAR2(25)                DEFAULT ' '
       NOT NULL,
  SERVICE_SCHEDULE  VARCHAR2(8)                 DEFAULT ' '
       NOT NULL,
  PRODUCT_CODE      CHAR(4)                     DEFAULT ' '
       NOT NULL
);

GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_ASSET_TYPE TO PUBLIC;

INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'000', 0, ' ', ' ', '    ');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'001', 0, ' ', ' ', '    ');
INSERT INTO AS_ASSET_TYPE ( MAIN_TYPE, SUB_TYPE, DESCRIPTION, SERVICE_SCHEDULE,
PRODUCT_CODE ) VALUES (
'100', 0, 'BUILDINGS', ' ', '    ');

-- file name:  as_master.sql

CREATE TABLE AS_MASTER
(
  ASSET_NO             VARCHAR2(16)             DEFAULT ' '
       NOT NULL,
  FUND                 CHAR(1)                  NOT NULL,
  FINAL_ACCOUNT_NO     NUMBER(6)                NOT NULL,
  ITEM_NO              NUMBER(6)                NOT NULL,
  SUB_NO               NUMBER(6)                NOT NULL,
  TYPE                 NUMBER(6)                DEFAULT 0
       NOT NULL,
  SUB_TYPE             NUMBER(6)                DEFAULT 0
       NOT NULL,
  LOCATION             NUMBER(6)                DEFAULT 0
       NOT NULL,
  SUB_LOCATION         NUMBER(6)                DEFAULT 0
       NOT NULL,
  LEVEL_A              VARCHAR2(4)              DEFAULT ' '
       NOT NULL,
  LEVEL_B              VARCHAR2(4)              DEFAULT ' '
       NOT NULL,
  LEVEL_C              VARCHAR2(4)              DEFAULT ' '
       NOT NULL,
  LEVEL_D              VARCHAR2(4)              DEFAULT ' '
       NOT NULL,
  LEVEL_E              VARCHAR2(4)              DEFAULT ' '
       NOT NULL,
  OPENING_ACQ_VALUE    NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  OPENING_ACQ_QTY      NUMBER(12)               DEFAULT 0
       NOT NULL,
  WITHDRAWAL_VALUE     NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  WITHDRAWAL_QTY       NUMBER(12)               DEFAULT 0
       NOT NULL,
  PROFIT_LOSS_VALUE    NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  DEPREC_VALUE         NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  PROFIT_LOSS_YTD      NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  DEPREC_YTD           NUMBER(15,2)             DEFAULT 0
       NOT NULL,
  DEPREC_LAST_MONTH    NUMBER(6)                DEFAULT 0
       NOT NULL,
  DEPREC_LAST_YEAR     NUMBER(6)                DEFAULT 0
       NOT NULL,
  DEPREC_METHOD        CHAR(1)                  DEFAULT ' '
       NOT NULL,
  DEPREC_RATE_2        NUMBER(15,6)             DEFAULT 0
       NOT NULL,
  DEPREC_ROUND_FACTOR  NUMBER(6)                DEFAULT 0
       NOT NULL,
  DEPREC_FREQUENCY     CHAR(1)                  DEFAULT ' '
       NOT NULL,
  DEPREC_MINIMUM_VAL   NUMBER(12)               DEFAULT 0
       NOT NULL,
  DEPREC_TYPE          NUMBER(6)                DEFAULT 0
       NOT NULL,
  DEPREC_FOLIO_DR      VARCHAR2(8)              DEFAULT ' '
       NOT NULL,
  DEPREC_FOLIO_CR      VARCHAR2(8)              DEFAULT ' '
       NOT NULL,
  INSURANCE_VALUE      NUMBER(12)               DEFAULT 0
       NOT NULL,
  INSURANCE_CATEGORY   VARCHAR2(2)              DEFAULT ' '
       NOT NULL,
  INSURANCE_POLICY     VARCHAR2(10)             DEFAULT ' '
       NOT NULL,
  INSURANCE_CODE       CHAR(1)                  DEFAULT ' '
       NOT NULL,
  ASSET_FILE_NO        VARCHAR2(10)             DEFAULT ' '
       NOT NULL,
  PROPERTY_REF         VARCHAR2(20)             DEFAULT ' '
       NOT NULL,
  LAST_AUDIT_DATE      DATE                         NULL,
  LAST_AUDIT_PAGE      NUMBER(6)                DEFAULT 0
       NOT NULL,
  DESCRIPTION_1        VARCHAR2(40)             DEFAULT ' '
       NOT NULL,
  DESCRIPTION_2        VARCHAR2(40)             DEFAULT ' '
       NOT NULL
);


GRANT ALTER, DELETE, INDEX, INSERT, REFERENCES, SELECT, UPDATE ON
GENADMIN.AS_MASTER TO PUBLIC;

ALTER TABLE GENADMIN.AS_MASTER ADD (
  PRIMARY KEY
 (ASSET_NO));

INSERT INTO AS_MASTER ( ASSET_NO, FUND, FINAL_ACCOUNT_NO, ITEM_NO,
SUB_NO, TYPE, SUB_TYPE,
LOCATION, SUB_LOCATION, LEVEL_A, LEVEL_B, LEVEL_C, LEVEL_D, LEVEL_E,
OPENING_ACQ_VALUE,
OPENING_ACQ_QTY, WITHDRAWAL_VALUE, WITHDRAWAL_QTY, PROFIT_LOSS_VALUE,
DEPREC_VALUE, PROFIT_LOSS_YTD,
DEPREC_YTD, DEPREC_LAST_MONTH, DEPREC_LAST_YEAR, DEPREC_METHOD,
DEPREC_RATE_2, DEPREC_ROUND_FACTOR,
DEPREC_FREQUENCY, DEPREC_MINIMUM_VAL, DEPREC_TYPE, DEPREC_FOLIO_DR,
DEPREC_FOLIO_CR,
INSURANCE_VALUE, INSURANCE_CATEGORY, INSURANCE_POLICY, INSURANCE_CODE,
ASSET_FILE_NO, PROPERTY_REF,
LAST_AUDIT_DATE, LAST_AUDIT_PAGE, DESCRIPTION_1, DESCRIPTION_2 ) VALUES (
'1.001.0001.001', '1', 1, 1, 1, 0, 0, 0, 0, ' ', ' ', ' ', ' ', ' ',
2, 1, 2, 1, 0
, 0, 0, 0, 6, 2000, 'S', 0, 0, ' ', 0, 2, ' ', ' ', 0, ' ', ' ', ' ',
' ', ' ',  TO_Date( '11/05/1997 12:00:00 AM', 'MM/DD/YYYY HH:MI:SS
AM')
, 6896, 'DUMMY ASSET', ' ');


-- end script

Unfortunately When I execute the above files using \i I am getting errors like

ERROR:  syntax error at or near "("
LINE 4:   SUB_TYPE          NUMBER(3)                   NOT NULL,

Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
is your suggestion to create these tables in PostgreSQL?  Should I
pre-process using Sed/Awk to find/replace the field type first?  Any
recommendation is greatly appreciated :)

Thanks.

Greenhorn wrote:
> Unfortunately When I execute the above files using \i I am getting errors like
>
> ERROR:  syntax error at or near "("
> LINE 4:   SUB_TYPE          NUMBER(3)                   NOT NULL,
>
> Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
> is your suggestion to create these tables in PostgreSQL?  Should I
> pre-process using Sed/Awk to find/replace the field type first?  Any
> recommendation is greatly appreciated :)
>

change number to numeric, and varchar2 to char and you'll likely be good.

John R Pierce wrote on 16.03.2009 00:41:
> Greenhorn wrote:
>> Unfortunately When I execute the above files using \i I am getting
>> errors like
>>
>> ERROR:  syntax error at or near "("
>> LINE 4:   SUB_TYPE          NUMBER(3)                   NOT NULL,
>>
>> Obviously 'number' is not PostgreSQL data type so is 'varchar2'.  What
>> is your suggestion to create these tables in PostgreSQL?  Should I
>> pre-process using Sed/Awk to find/replace the field type first?  Any
>> recommendation is greatly appreciated :)
>>
>
> change number to numeric, and varchar2 to char and you'll likely be good.
>
>
>
My guess would have been that varchar is the equivalent to varchar2

char does a blank padding and is different in behaviour to varchar (as it is in
Oracle)

Thomas

Thomas Kellerer wrote:
>>
>> change number to numeric, and varchar2 to char and you'll likely be
>> good.
>>
>>
>>
> My guess would have been that varchar is the equivalent to varchar2
>
> char does a blank padding and is different in behaviour to varchar (as
> it is in Oracle)


geez, yeah, what you said!    BTDT, and had the t-shirt.    i think ill
have another beer now.

On Mon, Mar 16, 2009 at 12:35 AM, Greenhorn <user.postgresql@gmail.com> wrote:
> Hi,
>
> I have almost 1300 files from Oracle (no access to oracle server).  I
> have to create the tables and data as follows.
(...) snip (...)
> Any recommendation is greatly appreciated :)

Try here: http://wiki.postgresql.org/wiki/Converting_from_other_Databases_to_PostgreSQL#Oracle