Thread: Porting from ORACLE to PostgSQL
Hello
I need to porting many old ORACLE-oriented-SQL files and I have many
problem with this code. Sometimes the code use some types not supported
in PosgSQL like "number" or "varchar2", there fore, can I write some
type of declaration (like in c : #define alias_name name) in order to
make PosgSQL recognize these types? Or there are other solution that you
recommend to use ?
Thank you, Gustavo
--
||\ // \
| \\ // |
I'm thinking. \ \\ l\\l_ // |
_ _ | \\/ `/ `.| |
/~\\ \ //~\ | Y | | || Y |
| \\ \ // | | \| | |\ / |
[ || || ] \ | o|o | > /
] Y || || Y [ \___\_--_ /_/__/
| \_|l,------.l|_/ | /.-\(____) /--.\
| >' `< | `--(______)----'
\ (/~`--____--'~\) / U// U / \
`-_>-__________-<_-' / \ / /|
/(_#(__)#_)\ ( .) / / ]
\___/__\___/ `.`' / [
/__`--'__\ |`-' |
/\(__,>-~~ __) | |__
/\//\\( `--~~ ) _l |--:.
'\/ <^\ /^> | ` ( < \\
_\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/
(___\ /___) (____/ (____) `---'
gustavo halperin wrote:
> Hello
>
> I need to porting many old ORACLE-oriented-SQL files and I have many
> problem with this code. Sometimes the code use some types not supported
> in PosgSQL like "number" or "varchar2", there fore, can I write some
> type of declaration (like in c : #define alias_name name) in order to
> make PosgSQL recognize these types? Or there are other solution that you
> recommend to use ?
>
Look at the orafce project on www.pgfoundry.org. They have ALOT of
Oracle types (over 100).
Sincerely,
Joshua D. Drake
> Thank you, Gustavo
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
> I need to porting many old ORACLE-oriented-SQL files and I have many > problem with this code. Sometimes the code use some types not supported > in PosgSQL like "number" or "varchar2", there fore, can I write some > type of declaration (like in c : #define alias_name name) in order to > make PosgSQL recognize these types? Or there are other solution that you > recommend to use ? You might look into the contrib package "ora2pg". There's also a more ambitious project called "protopg". That's in pre-alpha state, though. A development snapshots for the *very* *adventurous* can be obtained here: http://protopg.projects.postgresql.org/nightlies/ Bye, Chris. -- Chris Mair http://www.1006.org
Chris Mair wrote: >> I need to porting many old ORACLE-oriented-SQL files and I have many >> problem with this code. Sometimes the code use some types not supported >> in PosgSQL like "number" or "varchar2", there fore, can I write some >> type of declaration (like in c : #define alias_name name) in order to >> make PosgSQL recognize these types? Or there are other solution that you >> recommend to use ? >> > > You might look into the contrib package "ora2pg". > > There's also a more ambitious project called "protopg". > That's in pre-alpha state, though. A development snapshots > for the *very* *adventurous* can be obtained here: > http://protopg.projects.postgresql.org/nightlies/ > > Bye, Chris. > Thank you, but nothing work. 1. The orafce from www.pgfoundry.org doesn't pass the make step, is looking for some files that doesn't exist, see below: orafce : make Makefile:16: ../../src/Makefile.global: No such file or directory Makefile:17: /contrib/contrib-global.mk: No such file or directory make: *** No rule to make target `/contrib/contrib-global.mk'. Stop. 2. ora2pg, looks like something that extract DB schemes from a running oracle DB, but I have not ORACLE running. I just have old ORACLE/SQL sources that I need parser to SQL or PostgSQL. 3. About the ambitious project "protopg", I success to compile this projects but the parser fall. The parse have a problem with a word "CONSTRAINT", see below: protopg-20060905 : ./ora_parser < tes.sql -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 -- -- BEGIN OF UNRECOGNIZED STATEMENT CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) , template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT pk_cmt PRIMARY KEY ( item_id ) ) ; -- END OF UNRECOGNIZED STATEMENT I will try debug it, or maybe send a mail to the developer/contacts. Any way, thank very much, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ // | _ _ | \\/ `/ `.| | /~\\ \ //~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ || || ] \ | o|o | > / ] Y || || Y [ \___\_--_ /_/__/ | \_|l,------.l|_/ | /.-\(____) /--.\ | >' `< | `--(______)----' \ (/~`--____--'~\) / U// U / \ `-_>-__________-<_-' / \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/ `.`' / [ /__`--'__\ |`-' | /\(__,>-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ <^\ /^> | ` ( < \\ _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ (___\ /___) (____/ (____) `---'
On 9/5/06, gustavo halperin <ggh.develop@gmail.com> wrote: > Chris Mair wrote: > >> I need to porting many old ORACLE-oriented-SQL files and I have many > >> problem with this code. Sometimes the code use some types not supported > >> in PosgSQL like "number" or "varchar2", there fore, can I write some > >> type of declaration (like in c : #define alias_name name) in order to > >> make PosgSQL recognize these types? Or there are other solution that you > >> recommend to use ? don't the enterprisedb people specialize in oracle porting? merlin
>> Bye, Chris.
> Thank you, but nothing work.
> 1. The orafce from www.pgfoundry.org doesn't pass the make step, is
> looking for some files that doesn't exist, see below:
> orafce : make
> Makefile:16: ../../src/Makefile.global: No such file or directory
> Makefile:17: /contrib/contrib-global.mk: No such file or directory
> make: *** No rule to make target `/contrib/contrib-global.mk'. Stop.
It is designed to be built from the postgresql source tree. If you have
a postgresql source tree put the orafce directory in contrib and try again.
Sincerely,
Joshua D. Drake
>
> 2. ora2pg, looks like something that extract DB schemes from a running
> oracle DB, but I have not ORACLE running. I just have old ORACLE/SQL
> sources that I need parser to SQL or PostgSQL.
>
> 3. About the ambitious project "protopg", I success to compile this
> projects but the parser fall. The parse have a problem with a word
> "CONSTRAINT", see below:
> protopg-20060905 : ./ora_parser < tes.sql
> -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 --
>
> -- BEGIN OF UNRECOGNIZED STATEMENT
> CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) ,
> template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT
> pk_cmt PRIMARY KEY ( item_id ) ) ;
> -- END OF UNRECOGNIZED STATEMENT
>
> I will try debug it, or maybe send a mail to the developer/contacts.
>
> Any way, thank very much, Gustavo
>
--
=== The PostgreSQL Company: Command Prompt, Inc. ===
Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240
Providing the most comprehensive PostgreSQL solutions since 1997
http://www.commandprompt.com/
> 3. About the ambitious project "protopg", I success to compile this > projects but the parser fall. The parse have a problem with a word > "CONSTRAINT", see below: > protopg-20060905 : ./ora_parser < tes.sql > -- syntax error, unexpected "CONSTRAINT", expecting ORA_ID on line 6 > -- > > -- BEGIN OF UNRECOGNIZED STATEMENT > CREATE TABLE v5templates . cmt_items ( item_id NUMBER ( 7 ) , > template_id NUMBER ( 7 ) , page_title VARCHAR2 ( 100 ) , CONSTRAINT > pk_cmt PRIMARY KEY ( item_id ) ) ; > -- END OF UNRECOGNIZED STATEMENT > > I will try debug it, or maybe send a mail to the developer/contacts. That would be me ;) Protopg knows only a small part of Oracle Syntax. It's origins are a academic background were we had one sample DB and were looking how far we got in translating that one. If you throw random Oracle DBs at it, it will likely stumble over some pieces. Nonetheless it might be partially helpful. If you like it, subscribe to http://pgfoundry.org/mailman/listinfo/protopg-developers If nothing else, when we put out some prerelease, it would be cool if you could help us testing it with your test case :) Bye, Chris. -- Chris Mair http://www.1006.org
gustavo halperin <ggh.develop@gmail.com> writes:
> 1. The orafce from www.pgfoundry.org doesn't pass the make step, is
> looking for some files that doesn't exist, see below:
> orafce : make
> Makefile:16: ../../src/Makefile.global: No such file or directory
> Makefile:17: /contrib/contrib-global.mk: No such file or directory
> make: *** No rule to make target `/contrib/contrib-global.mk'. Stop.
It looks like it's expecting to be built inside the contrib directory of
a configured Postgres source tree. If you have a version that knows
about pgxs you can build it outside the source tree with
make USE_PGXS=1
regards, tom lane
Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper for postgreSQL.... "gustavo halperin" <ggh.develop@gmail.com> wrote in message news:44FCA0A7.5080100@gmail.com... > Hello > > I need to porting many old ORACLE-oriented-SQL files and I have many > problem with this code. Sometimes the code use some types not supported in > PosgSQL like "number" or "varchar2", there fore, can I write some type of > declaration (like in c : #define alias_name name) in order to make PosgSQL > recognize these types? Or there are other solution that you recommend to > use ? > > Thank you, Gustavo > > -- > ||\ // \ > | \\ // | I'm thinking. > \ \\ l\\l_ // | > _ _ | \\/ `/ `.| | > /~\\ \ //~\ | Y | | || Y | > | \\ \ // | | \| | |\ / | > [ || || ] \ | o|o | > / > ] Y || || Y [ \___\_--_ /_/__/ > | \_|l,------.l|_/ | /.-\(____) /--.\ > | >' `< | `--(______)----' > \ (/~`--____--'~\) / U// U / \ > `-_>-__________-<_-' / \ / /| > /(_#(__)#_)\ ( .) / / ] > \___/__\___/ `.`' / [ > /__`--'__\ |`-' | > /\(__,>-~~ __) | |__ > /\//\\( `--~~ ) _l |--:. > '\/ <^\ /^> | ` ( < \\ > _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ > (___\ /___) (____/ (____) `---' > > > ---------------------------(end of broadcast)--------------------------- > TIP 4: Have you searched our list archives? > > http://archives.postgresql.org >
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Eliminating 21 line .signatures would be nice, too. codeWarrior wrote: > Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper > for postgreSQL.... > > > > > "gustavo halperin" <ggh.develop@gmail.com> wrote in message > news:44FCA0A7.5080100@gmail.com... >> Hello >> >> I need to porting many old ORACLE-oriented-SQL files and I have many >> problem with this code. Sometimes the code use some types not supported in >> PosgSQL like "number" or "varchar2", there fore, can I write some type of >> declaration (like in c : #define alias_name name) in order to make PosgSQL >> recognize these types? Or there are other solution that you recommend to >> use ? >> >> Thank you, Gustavo - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/aQWS9HxQb37XmcRArJVAKCNjKPIGC0dZKCZNgZBTz9wdYl7nQCfYZFU etqDdW8rRm+gXShS1iF3Nq8= =g+pL -----END PGP SIGNATURE-----
On Mon, 2006-09-04 at 16:54, gustavo halperin wrote: > Hello > > I need to porting many old ORACLE-oriented-SQL files and I have many > problem with this code. Sometimes the code use some types not supported > in PosgSQL like "number" or "varchar2", there fore, can I write some > type of declaration (like in c : #define alias_name name) in order to > make PosgSQL recognize these types? Or there are other solution that you > recommend to use ? If you just need a simple solution, look up domains: http://www.postgresql.org/docs/8.1/static/sql-createdomain.html specifically: create domain varchar2 as varchar; Note that you won't have a precision argument on the varchar2, but you can put one on the varchar you use during domain creation: This works: create domain varchar2 as varchar(200); create table test (info varchar); This doesn't: create domain varchar2 as varchar; create table test (info varchar(200)); But once the tables are created, insert statements and such should work normally.
Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Eliminating 21 line .signatures would be nice, too. How about your 16 lines ;) Sincerely, Joshua D. Drake > > codeWarrior wrote: >> Perhaps you should look at EnterpriseDB -- It's an Oracle-compliant wrapper >> for postgreSQL.... >> >> >> >> >> "gustavo halperin" <ggh.develop@gmail.com> wrote in message >> news:44FCA0A7.5080100@gmail.com... >>> Hello >>> >>> I need to porting many old ORACLE-oriented-SQL files and I have many >>> problem with this code. Sometimes the code use some types not supported in >>> PosgSQL like "number" or "varchar2", there fore, can I write some type of >>> declaration (like in c : #define alias_name name) in order to make PosgSQL >>> recognize these types? Or there are other solution that you recommend to >>> use ? >>> >>> Thank you, Gustavo > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Is "common sense" really valid? > For example, it is "common sense" to white-power racists that > whites are superior to blacks, and that those with brown skins > are mud people. > However, that "common sense" is obviously wrong. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.5 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFE/aQWS9HxQb37XmcRArJVAKCNjKPIGC0dZKCZNgZBTz9wdYl7nQCfYZFU > etqDdW8rRm+gXShS1iF3Nq8= > =g+pL > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > -- === The PostgreSQL Company: Command Prompt, Inc. === Sales/Support: +1.503.667.4564 || 24x7/Emergency: +1.800.492.2240 Providing the most comprehensive PostgreSQL solutions since 1997 http://www.commandprompt.com/
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Joshua D. Drake wrote: > Ron Johnson wrote: > Eliminating 21 line .signatures would be nice, too. > >> How about your 16 lines ;) ??? It's 8 lines. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/dGeS9HxQb37XmcRAswpAKDq0Zy5sF5nI+Ojl8zkOcDWf3VY8ACeOK6h BJJadV19Ni9bwYPy+Hsq2fc= =x6qe -----END PGP SIGNATURE-----
> -----Original Message----- > From: pgsql-general-owner@postgresql.org > [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson > Sent: 05 September 2006 20:36 > To: pgsql-general@postgresql.org > Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) > > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Joshua D. Drake wrote: > > Ron Johnson wrote: > > Eliminating 21 line .signatures would be nice, too. > > > >> How about your 16 lines ;) > > ??? It's 8 lines. I count 16, including PGP fluff. Not that I care much :-) Regards, Dave.
-----BEGIN PGP SIGNED MESSAGE----- Hash: SHA1 Dave Page wrote: > > >> -----Original Message----- >> From: pgsql-general-owner@postgresql.org >> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson >> Sent: 05 September 2006 20:36 >> To: pgsql-general@postgresql.org >> Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) >> >> -----BEGIN PGP SIGNED MESSAGE----- >> Hash: SHA1 >> >> Joshua D. Drake wrote: >>> Ron Johnson wrote: >>> Eliminating 21 line .signatures would be nice, too. >>> >>>> How about your 16 lines ;) >> ??? It's 8 lines. > > I count 16, including PGP fluff. Not that I care much :-) Oh, ok. Tbird hides the PGP wrapper. - -- Ron Johnson, Jr. Jefferson LA USA Is "common sense" really valid? For example, it is "common sense" to white-power racists that whites are superior to blacks, and that those with brown skins are mud people. However, that "common sense" is obviously wrong. -----BEGIN PGP SIGNATURE----- Version: GnuPG v1.4.5 (GNU/Linux) Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org iD8DBQFE/diSS9HxQb37XmcRAgP7AJwIg9zzju3s3JJSz/jq165U7hFUCgCfXVKK HNUAqY5J2urMEI6uGl5cr3Y= =CTJ2 -----END PGP SIGNATURE-----
Ron Johnson wrote: > -----BEGIN PGP SIGNED MESSAGE----- > Hash: SHA1 > > Dave Page wrote: > >> >> >> >>> -----Original Message----- >>> From: pgsql-general-owner@postgresql.org >>> [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Ron Johnson >>> Sent: 05 September 2006 20:36 >>> To: pgsql-general@postgresql.org >>> Subject: [OT] sig sizes (was Re: [GENERAL] Porting from ...) >>> >>> -----BEGIN PGP SIGNED MESSAGE----- >>> Hash: SHA1 >>> >>> Joshua D. Drake wrote: >>> >>>> Ron Johnson wrote: >>>> Eliminating 21 line .signatures would be nice, too. >>>> >>>> >>>>> How about your 16 lines ;) >>>>> >>> ??? It's 8 lines. >>> >> I count 16, including PGP fluff. Not that I care much :-) >> > > Oh, ok. Tbird hides the PGP wrapper. > > - -- > Ron Johnson, Jr. > Jefferson LA USA > > Is "common sense" really valid? > For example, it is "common sense" to white-power racists that > whites are superior to blacks, and that those with brown skins > are mud people. > However, that "common sense" is obviously wrong. > -----BEGIN PGP SIGNATURE----- > Version: GnuPG v1.4.5 (GNU/Linux) > Comment: Using GnuPG with Mozilla - http://enigmail.mozdev.org > > iD8DBQFE/diSS9HxQb37XmcRAgP7AJwIg9zzju3s3JJSz/jq165U7hFUCgCfXVKK > HNUAqY5J2urMEI6uGl5cr3Y= > =CTJ2 > -----END PGP SIGNATURE----- > > ---------------------------(end of broadcast)--------------------------- > TIP 6: explain analyze is your friend > > Are you serious about the length of my sign, I just think that is a funny sign, but if it cause some trouble to the server y will cat my sign from my mails and say sorry for the problem caused. Regards, Gustavo -- ||\ // \ | \\ // | I'm thinking. \ \\ l\\l_ // | _ _ | \\/ `/ `.| | /~\\ \ //~\ | Y | | || Y | | \\ \ // | | \| | |\ / | [ || || ] \ | o|o | > / ] Y || || Y [ \___\_--_ /_/__/ | \_|l,------.l|_/ | /.-\(____) /--.\ | >' `< | `--(______)----' \ (/~`--____--'~\) / U// U / \ `-_>-__________-<_-' / \ / /| /(_#(__)#_)\ ( .) / / ] \___/__\___/ `.`' / [ /__`--'__\ |`-' | /\(__,>-~~ __) | |__ /\//\\( `--~~ ) _l |--:. '\/ <^\ /^> | ` ( < \\ _\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/ (___\ /___) (____/ (____) `---'