[GENERAL] 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_VALUENUMBER(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_VALUENUMBER(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_MONTHNUMBER(6)DEFAULT 0 NOT NULL, DEPREC_LAST_YEAR NUMBER(6)DEFAULT 0 NOT NULL, DEPREC_METHODCHAR(1) DEFAULT ' ' NOT NULL, DEPREC_RATE_2NUMBER(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_NOVARCHAR2(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_1VARCHAR2(40) DEFAULT ' ' NOT NULL, DESCRIPTION_2VARCHAR2(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, DE
[GENERAL] "oracle to postgresql" conversion
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
Thanks!
---(end of broadcast)---
TIP 4: Have you searched our list archives?
http://archives.postgresql.org/
Re: [GENERAL] Oracle to PostgreSQL
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.
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to PostgreSQL
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
--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] Oracle to PostgreSQL
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. -- Sent via pgsql-general mailing list (pgsql-general@postgresql.org) To make changes to your subscription: http://www.postgresql.org/mailpref/pgsql-general
Re: [GENERAL] "oracle to postgresql" conversion
On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
I believe rem translates to '-- ' (the extra space is important)
set translates to '\set'
I do not know what the setting 'sqlbl' does in Oracle.
I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."
The various data types translate to (likely) more standards compliant
names, which you can peruse at
http://www.postgresql.org/docs/8.2/static/datatype.html
(Replace 8.2 with your major version of PostgreSQL.)
Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes. Thus, you could just as easily write
"EXP_ID" VARCHAR2(16) NOT NULL,
as
exp_id VARCHAR(16) NOT NULL
which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case. (Makes for easier reading
later.)
Kevin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
Re: [GENERAL] "oracle to postgresql" conversion
On Thursday 08 March 2007 11:40:21 am Kevin Hunter wrote: > I do not know what the setting 'sqlbl' does in Oracle. SET SQLBLANKLINES ON makes sqlplus not choke on empty lines in your input. Don't get me started. jan -- -- Jan de Visser [EMAIL PROTECTED] Baruk Khazad! Khazad ai-menu! -- ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "oracle to postgresql" conversion
Kevin Hunter wrote:
On 06 Mar 2007 at 9:01a -0800, g.c[ altudela ] wrote:
I'm a newbie in Oracle and postgreSQL,
i'm need to translate the following script (in Oracle) to postgreSQL :
rem Autorisation des lignes vides :
set sqlbl on
rem Initialisation du timer :
set timing on
rem Creation de la table :
I would say you can remove these. rem is just a comment Which can be
changed to -- . The two set lines are session settings that you won't miss.
You may want to consider starting with your CREATE DATABASE command and
make sure you have a suitable ENCODING setting.
Start by adding -
CREATE SCHEMA "LEPAPE";
You may want to add "LEPAPE" to your search path so you don't need to
have it before everything.
http://www.postgresql.org/docs/8.2/interactive/ddl-schemas.html#DDL-SCHEMAS-PATH
can explain that further.
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
Change VARCHAR2 to VARCHAR
Change NUMBER to NUMERIC
Change CHECK(measure to CHECK("MEASURE"
VARCHAR2 is an Oracle type that replaces VARCHAR. Not sure why but my
first guess would be it works with multibyte character sets which is why
I suggest checking your ENCODING before you start.
NUMBER is Oracle's version of NUMERIC - Oracle will use both but
probably only Oracle will use NUMBER.
The CHECK change is to do with identifiers which I point you to later.
You may want to bookmark http://www.ss64.com/orasyntax/datatypes.html
and http://www.postgresql.org/docs/8.2/interactive/datatype.html
as references for future scripts that you may stumble on.
I believe rem translates to '-- ' (the extra space is important)
set translates to '\set'
I do not know what the setting 'sqlbl' does in Oracle.
I'm not entirely sure about the owner bit, specified by "LEPAPE"."..."
The various data types translate to (likely) more standards compliant
names, which you can peruse at
http://www.postgresql.org/docs/8.2/static/datatype.html
(Replace 8.2 with your major version of PostgreSQL.)
Someone may correct me, but I believe that Postgres is not case
sensitive in (terms of column and constraint names) unless you create
them with quotes. Thus, you could just as easily write
"EXP_ID" VARCHAR2(16) NOT NULL,
as
exp_id VARCHAR(16) NOT NULL
I would say remove all the double quotes, but this will depend on the
other scripts that have selects and such - it may be easier to leave
them as is than to update all the scripts you have. If you are typing
from a book then that won't matter.
I am guessing that this is an Oracle tutorial that you have and you want
to use postgresql to work through it. This would be a good time to get
familiar with naming conventions, start by reading -
http://www.postgresql.org/docs/8.2/interactive/sql-syntax-lexical.html#SQL-SYNTAX-IDENTIFIERS
This should help you to understand why part of the above table
definition should be changed to CHECK("MEASURE" and you may have some
similar fixes throughout your scripts.
which would be my personal preference as I like to capitalize SQL
keywords and leave everything as lower case. (Makes for easier reading
later.)
Kevin
---(end of broadcast)---
TIP 9: In versions below 8.0, the planner will ignore your desire to
choose an index scan if your joining column's datatypes do not
match
--
Shane Ambler
[EMAIL PROTECTED]
Get Sheeky @ http://Sheeky.Biz
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] "oracle to postgresql" conversion
On Tue, 2007-03-06 at 11:01, [EMAIL PROTECTED] wrote:
> I'm a newbie in Oracle and postgreSQL,
> i'm need to translate the following script (in Oracle) to postgreSQL :
>
> rem Autorisation des lignes vides :
> set sqlbl on
You don't need this, postgresql doesn't choke on extra lines.
> rem Initialisation du timer :
> set timing on
-- Change this to \timing if you're gonna use psql to run the script:
\timing
> rem Creation de la table :
becomes
-- Creation de la table :
Now, we need to use real SQL 99 types here, or specific postgresql
types. And don't quote unless you need to. PostgreSQL folds to lower
case, not upper case, so if you quote upper case here, you'll always
have to quote in the future. Better to just not quote, in my humble
opinion.
So, "LEPAPE" will become lepape
VARCHAR2(16) will become varchar(16)
NUMBER will become either
decimal or numeric
NOT NULL is still NOT NULL
and the check constraint will look the same too. again unless you
require upper case, leave the SYS_C009967 lower case, and better yet,
give it a useful name, like lepape_measure_check
CREATE TABLE "LEPAPE"."EXPERIENCE"(
"EXP_ID" VARCHAR2(16) NOT NULL,
"MEASURE" VARCHAR2(10) NOT NULL,
"THRESHOLD" NUMBER NOT NULL,
"NB_NODES" NUMBER(3) NOT NULL,
"TOTAL_TIME" VARCHAR2(10) NOT NULL,
"SC_ID" NUMBER(6) NOT NULL,
"GRANULARITY" VARCHAR2(10) NOT NULL,
CONSTRAINT "SYS_C009967" CHECK(measure in ('age', 'num','order')))
---(end of broadcast)---
TIP 3: Have you checked our extensive FAQ?
http://www.postgresql.org/docs/faq
Re: [GENERAL] "oracle to postgresql" conversion
Hi, On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote: > NUMBER is Oracle's version of NUMERIC - Oracle will use both but > probably only Oracle will use NUMBER. Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT? /me looks at Oracle docs again. -- Devrim GÜNDÜZ PostgreSQL Replication, Consulting, Custom Development, 24x7 support Managed Services, Shared and Dedicated Hosting Co-Authors: plPHP, ODBCng - http://www.commandprompt.com/ signature.asc Description: This is a digitally signed message part
Re: [GENERAL] "oracle to postgresql" conversion
On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote: > Hi, > > On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote: > > NUMBER is Oracle's version of NUMERIC - Oracle will use both but > > probably only Oracle will use NUMBER. > > Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT? Not sure. It let me assign a precision to it, so I figured it wasn't int based. In fact, it accepts precision up to 38, just like numeric, and it accepts non-decimal portions, i.e.: number(20,4); ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly
Re: [GENERAL] "oracle to postgresql" conversion
All of Oracle's (non-float) number types are variable size numbers with an ordinal and a mantissa. This makes Oracle number very efficient for smaller values as compared to fixed size integers, but less efficient with larger values. NUMBER has a maximum precision of 38 digits with a scale of -84 to +127. NUMBER consumes between 1 and 22 bytes on disk. It is typical to specify a NUMBER with (p, s). In the absence of definition, precision of 38 and scale indeterminate will be assumed. The exception to this are IEEE floating point number types which are a fixed size regardless of value. Summary: Oracle has no fixed length equivlents to tinyint, smallint, int or bigint from other databases and can either store these values more or less efficiently than those databases with fixed length integer types. -Original Message- From: [EMAIL PROTECTED] [mailto:[EMAIL PROTECTED] On Behalf Of Scott Marlowe Sent: Friday, March 09, 2007 1:58 PM To: Devrim GÜNDÜZ Cc: Shane Ambler; Kevin Hunter; [EMAIL PROTECTED]; PostgreSQL General List Subject: Re: [GENERAL] "oracle to postgresql" conversion On Thu, 2007-03-08 at 16:05, Devrim GÜNDÜZ wrote: > Hi, > > On Fri, 2007-03-09 at 05:21 +1030, Shane Ambler wrote: > > NUMBER is Oracle's version of NUMERIC - Oracle will use both but > > probably only Oracle will use NUMBER. > > Really? I thought Oracle's NUMBER ~ PostgreSQL's (BIG)INT? Not sure. It let me assign a precision to it, so I figured it wasn't int based. In fact, it accepts precision up to 38, just like numeric, and it accepts non-decimal portions, i.e.: number(20,4); ---(end of broadcast)--- TIP 1: if posting/reading through Usenet, please send an appropriate subscribe-nomail command to [EMAIL PROTECTED] so that your message can get through to the mailing list cleanly ---(end of broadcast)--- TIP 2: Don't 'kill -9' the postmaster
Re: [GENERAL] "oracle to postgresql" conversion
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 On 03/09/07 14:53, Chris Fischer wrote: > All of Oracle's (non-float) number types are variable size > numbers with an ordinal and a mantissa. This makes Oracle number > very efficient for smaller values as compared to fixed size > integers, but less efficient with larger values. NUMBER has a > maximum precision of 38 digits with a scale of -84 to +127. > NUMBER consumes between 1 and 22 bytes on disk. It is typical to > specify a NUMBER with (p, s). In the absence of definition, > precision of 38 and scale indeterminate will be assumed. > > The exception to this are IEEE floating point number types which > are a fixed size regardless of value. > > Summary: Oracle has no fixed length equivlents to tinyint, > smallint, int or bigint from other databases and can either store > these values more or less efficiently than those databases with > fixed length integer types. Wow Didn't believe you (Oracle couldn't be *that* lame, could it?), so I Googled. According to Table 12-1 of this web page, Oracle will silently truncate your numbers. There are no scalar data types http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96524/c13datyp.htm -BEGIN PGP SIGNATURE- Version: GnuPG v1.4.6 (GNU/Linux) iD8DBQFF8dDjS9HxQb37XmcRArCMAKDAFuUM2V804Zjdurr6eemqPyHHOwCg1oGk 8RxOTImJVBUqdBhHK6tezkA= =ibbT -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: explain analyze is your friend
Re: [GENERAL] Oracle to PostgreSQL migration
-BEGIN PGP SIGNED MESSAGE- Hash: SHA1 I think that's been part of pgadmin 2 On Monday 08 December 2003 10:49 pm, Randy Harris wrote: > I've been scouring the PostgreSQL web site and have found several > references to a Database Migration Wizard. I was unable, however, to find > any details. Would someone be kind enough to explain what and where it is? > I need to migrate an Oracle db to PostgreSQL. > > > > > > ---(end of broadcast)--- > TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED] - -- UC - -- Open Source Solutions 4U, LLC 2570 Fleetwood Drive Phone: +1 650 872 2425 San Bruno, CA 94066 Cell: +1 650 302 2405 United States Fax:+1 650 872 2417 -BEGIN PGP SIGNATURE- Version: GnuPG v1.2.1 (GNU/Linux) iD8DBQE/1nP4jqGXBvRToM4RAtNDAJwOOpIgk1da1DMmC2DvcNONZHk77ACgu2b0 QBvZnGi+n3yvBFPDnu542Zk= =2xqU -END PGP SIGNATURE- ---(end of broadcast)--- TIP 6: Have you searched our list archives? http://archives.postgresql.org
