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@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