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

On 6/11/20 12:32 PM, George Dimopoulos wrote:
> Hi Everyone,
> 
> I am new to migrating oracle 18c EX to postgres.
> 
> I do not know if this site is the correct site for my problem. If it is 
> not, can you please point me to correct site to ask the question?
> 
> I used ora2pg to start the migration but when I try to run “ ora2pg -t 
> SHOW_REPORT” I get the following error:
> 
> /DBD::Oracle::db prepare failed: ORA-28110: policy function or package 
> NRM_CAVES.UTIL_SECURITY has error (DBD ERROR: error possibly near <*> 
> indicator at char 49 in 'SELECT DISTINCT c.SHAPE.SDO_GTYPE FROM 
> NRM_CAVES.<*>LOCATIONS c WHERE ROWNUM < 50000') [for Statement "SELECT 
> DISTINCT c.SHAPE.SDO_GTYPE FROM NRM_CAVES.LOCATIONS c WHERE ROWNUM < 
> 50000"] at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 9408/.

So does:

SELECT DISTINCT c.SHAPE.SDO_GTYPE FROM NRM_CAVES.LOCATIONS c WHERE 
ROWNUM < 50000

run correctly in the database?

> 
> I have set COMPILE_SCHEMA     0
> 
> This error appears on every time no matter what I set TYPE, even I have 
> excluded all the tables where this policy applies.
> 
> Please help,
> 
> Best Regards,
> 
> George
> 
> George Dimopoulos
> 
> Oracle DBA and Weblogic  Admin
> 
> TechTrend Inc.
> 
> 8270 Willow Oaks Corporate Drive, Suite 625, Fairfax, VA 22031
> 
> Office: 703-521-9392 | Fax 703-663-8668
> 
> gdimopoulos@techtrend.us <mailto:gdimopoulos@techtrend.us>| 
> www.techtrend.us <http://www.techtrend.us/>
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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.

> 
> Anyway, I appreciate your help very much !!!
> 
> Best Regards,
> 
> George
> -----Original Message-----
> From: Adrian Klaver <adrian.klaver@aklaver.com>
> Sent: Thursday, June 11, 2020 4:03 PM
> To: George Dimopoulos <gdimopoulos@techtrend.us>; pgsql-general@postgresql.org
> Subject: Re: ora2pg error : DBD::Oracle::db prepare failed: ORA-28110
> 
> On 6/11/20 12:32 PM, George Dimopoulos wrote:
>> Hi Everyone,
>>
>> I am new to migrating oracle 18c EX to postgres.
>>
>> I do not know if this site is the correct site for my problem. If it
>> is not, can you please point me to correct site to ask the question?
>>
>> I used ora2pg to start the migration but when I try to run " ora2pg -t
>> SHOW_REPORT" I get the following error:
>>
>> /DBD::Oracle::db prepare failed: ORA-28110: policy function or package
>> NRM_CAVES.UTIL_SECURITY has error (DBD ERROR: error possibly near <*>
>> indicator at char 49 in 'SELECT DISTINCT c.SHAPE.SDO_GTYPE FROM
>> NRM_CAVES.<*>LOCATIONS c WHERE ROWNUM < 50000') [for Statement "SELECT
>> DISTINCT c.SHAPE.SDO_GTYPE FROM NRM_CAVES.LOCATIONS c WHERE ROWNUM <
>> 50000"] at C:/Strawberry/perl/site/lib/Ora2Pg.pm line 9408/.
> 
> So does:
> 
> SELECT DISTINCT c.SHAPE.SDO_GTYPE FROM NRM_CAVES.LOCATIONS c WHERE ROWNUM < 50000
> 
> run correctly in the database?
> 
>>
>> I have set COMPILE_SCHEMA     0
>>
>> This error appears on every time no matter what I set TYPE, even I
>> have excluded all the tables where this policy applies.
>>
>> Please help,
>>
>> Best Regards,
>>
>> George
>>
>> George Dimopoulos
>>
>> Oracle DBA and Weblogic  Admin
>>
>> TechTrend Inc.
>>
>> 8270 Willow Oaks Corporate Drive, Suite 625, Fairfax, VA 22031
>>
>> Office: 703-521-9392 | Fax 703-663-8668
>>
>> gdimopoulos@techtrend.us <mailto:gdimopoulos@techtrend.us>|
>> www.techtrend.us <http://www.techtrend.us/>
>>
> 
> 


-- 
Adrian Klaver
adrian.klaver@aklaver.com

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/

Thank you all very much for your help !!!

Regards,

George

-----Original Message-----
From: Gilles Darold <gilles@darold.net> 
Sent: Friday, June 12, 2020 1:50 AM
To: Adrian Klaver <adrian.klaver@aklaver.com>; George Dimopoulos <gdimopoulos@techtrend.us>
Cc: pgsql-general <pgsql-general@postgresql.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
functionis not in the schema you are exporting it will not help. Actually this is an Oracle issue you may want to
contactan Oracle DBA to see what can be done.
 


Best regards

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