Re: Difficulties to do a migration from Oracle8i to PostgreSQL 7.3.3

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