Content-Type: multipart/alternative;
 boundary="------------HPvGbKgIJyrkWir0zOgcUqQN"
Message-ID: <7fb9d1c0-77ce-fa7b-a835-d0977ae8aa92@gmail.com>
Date: Sun, 12 Dec 2021 01:53:32 -0500
MIME-Version: 1.0
User-Agent: Mozilla/5.0 (X11; Linux x86_64; rv:91.0) Gecko/20100101
 Thunderbird/91.3.0
Subject: Re: package conversion error from oracle to postgresql database
 !!!!!!!!!
Content-Language: en-US
To: pgsql-admin@lists.postgresql.org
References: 
 <CAEz7P_tS=-_fFo4mm35-2o7W721j+APYQpn_mbnC2f1STiXJ6Q@mail.gmail.com>
From: Mladen Gogala <gogala.mladen@gmail.com>
In-Reply-To: 
 <CAEz7P_tS=-_fFo4mm35-2o7W721j+APYQpn_mbnC2f1STiXJ6Q@mail.gmail.com>
Archived-At: 
 <https://www.postgresql.org/message-id/7fb9d1c0-77ce-fa7b-a835-d0977ae8aa92%40gmail.com>
Precedence: bulk

This is a multi-part message in MIME format.
--------------HPvGbKgIJyrkWir0zOgcUqQN
Content-Type: text/plain; charset=UTF-8; format=flowed
Content-Transfer-Encoding: 8bit

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

--------------HPvGbKgIJyrkWir0zOgcUqQN
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: 8bit

<html>
  <head>
    <meta http-equiv="Content-Type" content="text/html; charset=UTF-8">
  </head>
  <body>
    <div class="moz-cite-prefix">On 12/12/21 00:46, dbatoCloud Solution
      wrote:<br>
    </div>
    <blockquote type="cite"
cite="mid:CAEz7P_tS=-_fFo4mm35-2o7W721j+APYQpn_mbnC2f1STiXJ6Q@mail.gmail.com">
      <meta http-equiv="content-type" content="text/html; charset=UTF-8">
      <div dir="ltr">
        <div class="gmail_default"
          style="font-family:georgia,serif;font-size:small;color:#073763"><span
            style="color:rgb(12,52,61);font-family:Arial">Hi All,</span></div>
        <div class="gmail_default"
          style="font-family:georgia,serif;font-size:small;color:#073763">While
          migrating oracle to PostgreSQL database using ora2pg tool. I
          have got this below error for the package. Could someone
          please help with this? </div>
        <div class="gmail_default"
          style="font-family:georgia,serif;font-size:small;color:#073763">psql:./schema/packages/package.sql:??:
          ERROR:  schema "utl_file" does not exist<br>
          LINE 75:    file_type utl_file.file_type :=
          utl_file.fopen(file_dir, ...<br>
                                ^<br>
          ERROR: an error occurs when importing file
          ./schema/packages/package.sql.<br>
          [ybos@doliordv265 comsod_code]$<br>
        </div>
        <div>
          <div dir="ltr" class="gmail_signature"
            data-smartmail="gmail_signature">
            <div dir="ltr"><br>
            </div>
          </div>
        </div>
      </div>
    </blockquote>
    <p>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.</p>
    <p>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.<br>
    </p>
    <pre class="moz-signature" cols="72">-- 
Mladen Gogala
Database Consultant
Tel: (347) 321-1217
<a class="moz-txt-link-freetext" href="https://dbwhisperer.wordpress.com">https://dbwhisperer.wordpress.com</a>
</pre>
  </body>
</html>
--------------HPvGbKgIJyrkWir0zOgcUqQN--