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

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