Substitutes for some Oracle packages

On Thu, Sep 17, 2009 at 03:53:36PM -0400, Arnold, Sandra wrote:

We are in the process of migrating from Oracle to PostgreSQL. One of
the things that we are needing to find out is what to use in place of
Oracle supplied functionality such as "DBMS_OUTPUT" and "UTL_FILE".

For those of us who use PG and not Oracle a description of the
functionality you need would help, the artifacts of your current
implementation are less helpful. That said:

plpgsql can RAISE NOTICE, which looks similar to DBMS_OUTPUT

most "untrusted" scripting languages (i.e. plperl or plpython) can
touch the filesystem, which is what UTL_FILE seems to be about

--
Sam http://samason.me.uk/

On Thu, Sep 17, 2009 at 1:53 PM, Arnold, Sandra <ArnoldS@osti.gov> wrote:

We are in the process of migrating from Oracle to PostgreSQL.  One of the
things that we are needing to find out is what to use in place of Oracle
supplied functionality such as "DBMS_OUTPUT" and "UTL_FILE".  We are
currently using this type of functionality in Stored Procedures and
packages.  What are the options in PostgreSQL for replacing these two
packages in a stored procedure/function?

Not being that familiar with all the packages oracle comes with, what
do those packages do?

DBMS_OUTPUT is used to either display output or write output to a file. UTL_FILE is used to open a file and then write data to a file. Most of the time we use these two packages to create log files from PL/SQL stored procedures/packages.

-----Original Message-----
From: pgsql-general-owner@postgresql.org [mailto:pgsql-general-owner@postgresql.org] On Behalf Of Sam Mason
Sent: Thursday, September 17, 2009 4:04 PM
To: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Substitutes for some Oracle packages

On Thu, Sep 17, 2009 at 03:53:36PM -0400, Arnold, Sandra wrote:

We are in the process of migrating from Oracle to PostgreSQL. One of
the things that we are needing to find out is what to use in place of
Oracle supplied functionality such as "DBMS_OUTPUT" and "UTL_FILE".

For those of us who use PG and not Oracle a description of the
functionality you need would help, the artifacts of your current
implementation are less helpful. That said:

plpgsql can RAISE NOTICE, which looks similar to DBMS_OUTPUT

most "untrusted" scripting languages (i.e. plperl or plpython) can
touch the filesystem, which is what UTL_FILE seems to be about

--
Sam http://samason.me.uk/

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Hello,

2009/9/17 Arnold, Sandra <ArnoldS@osti.gov>:

We are in the process of migrating from Oracle to PostgreSQL.  One of the
things that we are needing to find out is what to use in place of Oracle
supplied functionality such as "DBMS_OUTPUT" and "UTL_FILE".  We are
currently using this type of functionality in Stored Procedures and
packages.  What are the options in PostgreSQL for replacing these two
packages in a stored procedure/function?
Thanks,

-
for typical use you can use RAISE NOTICE without DBMS_OUTPUT,
UTL_FILE isn't supported by core.

Migration process could be simplified by using Orafce package:

http://pgfoundry.org/projects/orafce/
http://orafce.projects.postgresql.org/

regards
Pavel Stehule

Sandra Arnold wrote:

We are in the process of migrating from Oracle to PostgreSQL.
One of the things that we are needing to find out is what to
use in place of Oracle supplied functionality such as
"DBMS_OUTPUT" and "UTL_FILE". We are currently using this
type of functionality in Stored Procedures and packages.
What are the options in PostgreSQL for replacing these two
packages in a stored procedure/function?

RAISE NOTICE has been mentioned.

Apart from the possibility to write file I/O functions in Perl,
there are some functions that are already included in PostgreSQL:
pg_read_file and pg_stat_file.
The contrib module "adminpack" provides pg_file_write,
pg_file_rename, pg_file_unlink.

Together these are enough for simple file system access,
although they don't provide as much as UTL_FILE.

Yours,
Laurenz Albe

Hi,

You may also go for EnterpriseDB product(PPAS) which has oracle
compatibility on postgres database.

Abhi