migrate to pg

[jjpetrucelli]

im looking for an sql script that will help migrate from ora to pg. tables, triggers, keys, and data...

I call on the great santa mufasa (it is that time of year )

 

[SantaMufasa]

JJ,

First, as you know, my Santa's-helper elves (also known as "Pixies") are most helpful with questions like this (in case I am feeding the reindeer or some other seasonal activity). A list of "Most Valuable Pixies (MVPs)" appears to the right of every thread. I believe your specific request for my help may have caused the other Elves to wait for me to respond. In any case, I'm happy to help.

But first, let's clarify your need.

I'm looking for an sql script that will help migrate from ora to pg.

I am not familiar with "pg". How similar is "CREATE TABLE..." syntax in "pg" to Oracle SQL's syntax? My suggested resolution for you depends, to a large degree, upon the similarity of the two syntaxes.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 23:21 (06Dec04) UTC (aka "GMT" and "Zulu"),
@ 16:21 (06Dec04) Mountain Time

 

[jjpetrucelli]

oh i apologize, its postgresql, and its quite similar to oracle. sorry for the lack of clarity. to further explain i am looking at an sql statement rather than a program or perl script due to connection barriers to the ora db.

 

[SantaMufasa]

JJ,

You can generate the DDL for your TABLES, INDEXES, VIEWS, and many other objects via the DBMS_METADATA.GET_DDL function. For example, you can generate all the DDL for a schema's TABLES and INDEXES by spooling the results of these queries:

SELECT DBMS_METADATA.GET_DDL('TABLE',u.table_name)
          FROM USER_TABLES u;

SELECT DBMS_METADATA.GET_DDL('INDEX',u.index_name)
	  FROM USER_INDEXES u;

Additionally, you can create your own tailored (for "PG") scripts to simulate the above functions and to generate the "INSERT INTO..." statements for all your tables' data by using SQL-writing-SQL scripts.

This may be enough to resolve your need and get you started, but if you need additional guidance, Santa is always here for you.

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 00:55 (07Dec04) UTC (aka "GMT" and "Zulu"),
@ 17:55 (06Dec04) Mountain Time

 

[jjpetrucelli]

can you elaborate on 'sql-writing-sql scripts'? are you saying to just manually create the queries and concat 'insert into' in the appropriate places? or something else? I have attempted to do the manual way and ran into some difficulties dealing with line breaks and quotes in the values ...

 

[jjpetrucelli]

how can i account for line breaks and quote/double quotes in the value? I believe i somehow need to escape them but im not sure how...?

 

[SantaMufasa]

JJ,

I'm writing a generic script for you that deals with line breaks and single quotes. (Double quotes are not an issue in Oracle; are then an issue in "pg"?) But it's going to take me a few hours to write a fully tested code set. What is your time criticality on my delivering this code set to you?

Mufasa
(aka Dave of Sandy, Utah, USA)

http://www.dasages.com

@ 21:05 (07Dec04) UTC (aka "GMT" and "Zulu"),
@ 14:05 (07Dec04) Mountain Time

 

[jjpetrucelli]

wow, i really dont know what to say. please dont go through too much trouble for me, when and what ever you can back to me is more than fine. i surely do appreciate your help