Thread: 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
datato 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
> Sr. DBA
> DOE/OSTI
> Oak Ridge, TN

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