Re: package conversion error from oracle to postgresql database !!!!!!!!!

On 12/12/21 00:46, dbatoCloud Solution wrote:
> Hi All,
> While migrating oracle to PostgreSQL database using ora2pg tool. I
> have got this below error for the package. Could someone please help
> with this?
> psql:./schema/packages/package.sql:??: ERROR:  schema "utl_file" does
> not exist
> LINE 75:    file_type utl_file.file_type := utl_file.fopen(file_dir, ...
>                       ^
> ERROR: an error occurs when importing file ./schema/packages/package.sql.
> [ybos(at)doliordv265 comsod_code]$
>
It is not possible to make one to one conversion from Oracle to Postgres
because those two databases are different and have different design
principles and different principles of operation. If you want to use
file system interface from Postgres, you can use any untrusted version
of the supported scripted languages like Perl or Python and implement
your file operations. The "untrusted" versions of the scripting
languages are those with the access to file system, which is what you
need. In other words, some assembly required. You can, as suggested, use
orafce, but orafce does not turn Postgres into a free version of Oracle.
IThe orafce extension only goes so far. You will have to rethink the
application and rewrite it to use Postgres features. Not even the best
ORM will help you out with that.

BTW, EnterpriseDB has by far the largest collection of Oracle features 
made available on top of the Postgres kernel. EnterpriseDB can natively
execute PL/SQL and it has UTL_FILE package. However, EnterpriseDB isn't
free. It's much cheaper than Oracle, but is not free. On the other hand,
DB2 can also natively execute PL/SQL but is also not free. And DB2 isn't
Postgres either. It is however a rock solid database, much cheaper than
Oracle, but not free. In the end, you always get what you pay for.
Replacing Oracle by Postgres is possible but not easy. If you want an
easy replacement, you will have to pay for it.

--
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
https://dbwhisperer.wordpress.com