Обсуждение: Difficulties to do a migration from Oracle8i to PostgreSQL 7.3.3
Hello, I've a friend of mine that would like to move an Oracle8i Database (running on a Tru64 Unix server) to PostgreSQL (because the organization where he works have to cut down their costs). So, he tried to do a dump from the Oracle database, makes a few search/replaces in the dump files and finally, he tried to add this database inside PostgreSQL 7.3.3. The database structure, the datas and the sequences where OK without any problems. He had to do a little work to add all the constraints but he's stucked on inserting PL/SQL packages... (Because there isn't the possibility to do PL/PgSQL packages currently). He explains me that they have several frontend that uses that DB via the PL/SQL packages (dos, Gtk, Windows VisualC++, WAP (mobile phones), and Jini Java applications and he don't have the time and the knowledge to modify all theses applications in a very short time. (He is only DBA, not developper). Thus, he would like to make the PostgreSQL server to appears like an Oracle Server but I don't see anything about this in PostgreSQL documentation. As far as I know, PostgreSQL don't use the same architecture that Oracle (there isn't any Listener). So the trial to move from Oracle8i to PostgreSQL 7.3.3 will be probably delayed until the possibility to do that (PL/PGSQL packages), he will try to ask the developpers to add ODBC in their apps. Do you have an idea to do theses things ? Since I've never do that kind of migration, I don't have more ideas to help him... Is there some people that have do that kind of migration ? Is there a FAQ about migrating from Oracle to PostgreSQL ? Thanks really much in advance for him ! :-) ------------------------------------- Bruno BAGUETTE - pgsql-ml@baguette.net
On Sun, Jun 29, 2003 at 11:40:41AM +0200, Bruno BAGUETTE wrote: > stucked on inserting PL/SQL packages... (Because there isn't the > possibility to do PL/PgSQL packages currently). Sorry, I don't really understand this. PL/pgSQL is the PostgreSQL version of PL/SQL. > He explains me that they have several frontend that uses that DB via the > PL/SQL packages (dos, Gtk, Windows VisualC++, WAP (mobile phones), and > Jini Java applications and he don't have the time and the knowledge to > modify all theses applications in a very short time. (He is only DBA, > not developper). You mean all these apps do is execute a PL/SQL procedure in Oracle? And they cannot change the syntax in the apps? But they can implement the logic in PostgreSQL as they do now in Oracle? > Thus, he would like to make the PostgreSQL server to appears like an > Oracle Server but I don't see anything about this in PostgreSQL > documentation. As far as I know, PostgreSQL don't use the same > architecture that Oracle (there isn't any Listener). Now this is something different IMO. PostgreSQL postmaster does the listening, but why do the apps need the very same appearance? > So the trial to move from Oracle8i to PostgreSQL 7.3.3 will be probably > delayed until the possibility to do that (PL/PGSQL packages), he will > try to ask the developpers to add ODBC in their apps. How do they connect now? Native Oracle OCI? Michael -- Michael Meskes Email: Michael at Fam-Meskes dot De ICQ: 179140304, AIM: michaelmeskes, Jabber: meskes@jabber.org Go SF 49ers! Go Rhein Fire! Use Debian GNU/Linux! Use PostgreSQL!
On Sunday 29 June 2003 15:10, Bruno BAGUETTE wrote: > The database structure, the datas and the sequences where OK without any > problems. He had to do a little work to add all the constraints but he's > stucked on inserting PL/SQL packages... (Because there isn't the > possibility to do PL/PgSQL packages currently). > > He explains me that they have several frontend that uses that DB via the > PL/SQL packages (dos, Gtk, Windows VisualC++, WAP (mobile phones), and > Jini Java applications and he don't have the time and the knowledge to > modify all theses applications in a very short time. (He is only DBA, > not developper). As far as I understand, Oracle packages allow to create group of procedures which can be accessed from a object type '.' notation. A quick google search yielded URLs like http://www.vbip.com/books/1861003927/chapter_3927_12.asp which seem to confirm my assumption. I suggest that you/your friend give schemas a try. The yw ill probably appear as packages as far as method of access is concerned. Of course they are two altogether different things but just consider following for a demo. --------- test=# create schema s1; CREATE SCHEMA test=# create schema s2; CREATE SCHEMA test=# create function s1.test1() returns integer as ' test'# declare test'# begin test'# return 1; test'# end; test'# ' language plpgsql; CREATE FUNCTION test=# create function s2.test1() returns integer as ' test'# declare test'# begin test'# return 0; test'# end; test'# ' language plpgsql; CREATE FUNCTION test=# select s1.test1(); test1 ------- 1 (1 row) test=# select s2.test1(); test1 ------- 0 (1 row) test=# --------- This was a CVS installation compiled sometime last week. Of course there will be difficulties and some corner cases might prove to be trickier but this can take care of lot ofthings for you. I also strongly suggest that he try 7.4 as it will be entering in beta by 15th of July. It has lots of enhancements over previous releases and the group would get some additional field testing. Every feedback counts in open source community.. > Thus, he would like to make the PostgreSQL server to appears like an > Oracle Server but I don't see anything about this in PostgreSQL > documentation. As far as I know, PostgreSQL don't use the same > architecture that Oracle (there isn't any Listener). No there is no listener. Unix uses inetd for that purposes but anyway..:-) Oracle always thinks that it can offer better functionality than OS itself. Postgresql does not think so. The debate of which is better is entirely off-topic here..:-) Postgresql server offers connectivity to all the database in cluster once it is up. Unlike oracle it is not possible to start/stop individual databases. > So the trial to move from Oracle8i to PostgreSQL 7.3.3 will be probably > delayed until the possibility to do that (PL/PGSQL packages), he will > try to ask the developpers to add ODBC in their apps. Try the solution above. See how it works. If it works reasonably successfully, that could help a lot of people who would like to move off oracle. HTH Shridhar