Re: 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(dot)develop(at)gmail(dot)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(dot)develop(at)gmail(dot)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(dot)develop(at)gmail(dot)com> wrote in message
news:44FCA0A7(dot)5080100(at)gmail(dot)com(dot)(dot)(dot)
> 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(dot)develop(at)gmail(dot)com> wrote in message
> news:44FCA0A7(dot)5080100(at)gmail(dot)com(dot)(dot)(dot)
>> 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(dot)develop(at)gmail(dot)com> wrote in message
>> news:44FCA0A7(dot)5080100(at)gmail(dot)com(dot)(dot)(dot)
>>> 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(at)postgresql(dot)org
> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Johnson
> Sent: 05 September 2006 20:36
> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
>> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Johnson
>> Sent: 05 September 2006 20:36
>> To: pgsql-general(at)postgresql(dot)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(at)postgresql(dot)org
>>> [mailto:pgsql-general-owner(at)postgresql(dot)org] On Behalf Of Ron Johnson
>>> Sent: 05 September 2006 20:36
>>> To: pgsql-general(at)postgresql(dot)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 |--:.
'\/ <^\ /^> | ` ( < \\
_\ >-__-< /_ ,-\ ,-~~->. \ `:.___,/
(___\ /___) (____/ (____) `---'