RE: ora2pg error : DBD::Oracle::db prepare failed: ORA-28110

Thank you all very much for your help !!!

Regards,

George

-----Original Message-----
From: Gilles Darold <gilles(at)darold(dot)net>
Sent: Friday, June 12, 2020 1:50 AM
To: Adrian Klaver <adrian(dot)klaver(at)aklaver(dot)com>; George Dimopoulos <gdimopoulos(at)techtrend(dot)us>
Cc: pgsql-general <pgsql-general(at)postgresql(dot)org>
Subject: Re: ora2pg error : DBD::Oracle::db prepare failed: ORA-28110

Le 12/06/2020 à 00:40, Adrian Klaver a écrit :
> On 6/11/20 1:30 PM, George Dimopoulos wrote:
> Please reply to list also.
> Ccing list.
>
>> Hi Adrian,
>>
>> Thank you very much for your help !!!
>> I did the query, and I found out that SELECT DISTINCT
>> c.SHAPE.SDO_GTYPE FROM NRM_CAVES.LOCATIONS c WHERE ROWNUM < 50000
>>                                                   * ERROR at line 1:
>> ORA-28110: policy function or package NRM_CAVES.UTIL_SECURITY has
>> error
>
> So to be clear the above error is being seen on the Oracle database.
>
>>
>> This policy has been applied to few tables so I did exclude them
>> (with directive TYPE TABLE).
>> I was hopping that I will be able to migrate the other tables that do
>> not depend on this policy.
>> That means that developers have to correct this in Oracle side before
>> I can do the migration and succeed.
>> Is this correct?
>
> Not sure.
>
Hi,

"ORA-28110: Policy function or package has error occurs when a policy function has some error such as a compilation problem. The solution is to recompile the package or the function after fixing the underlying problem."

If you set COMPILE_SCHEMA to 1 in your ora2pg.conf, Ora2pg will first recompile the all functions but if the policy function is not in the schema you are exporting it will not help. Actually this is an Oracle issue you may want to contact an Oracle DBA to see what can be done.

Best regards

--
Gilles Darold
http://www.darold.net/