Re: Implicitly created TYPE object comparison between 11g & 12c+

Hi Mikhail,

Great catch! I'm pleased to see that my guess about ora_hash() working on concatenation was correct. By the way, how did you come across this information? I'm always eager to learn more about such investigations.

P.S. To be honest, I haven't been able to find my notes from 2017, so I'm uncertain about the differing object names in various databases (as mentioned in my previous point #5, that was from my answer in RuOUG telegram channel). Have you had a chance to test this behavior on different platforms? It might be related to the big/little endian issue, or perhaps I simply made a mistake. :)

On Sat, Apr 1, 2023 at 9:37 PM Mikhail Velikikh <mvelikikh_at_gmail.com> wrote:

> 1. The hash value is an ora_hash on
> <package_name>||chr(0)||<owner>||chr(0).
> E.g.:
>
> SQL> select ora_hash('PKG_DUMMY'||chr(0)||'TC'||chr(0)) from dual;
>
> ORA_HASH('PKG_DUMMY'||CHR(0)||'TC'||CHR(0))
> -------------------------------------------
> 3182294215
>
> Whereas the actual types were called sys_plsql_*bdadf4c7*% (3182294215 in
> hex)
>
> 2. the hash should be the same across different databases. At least, the
> algorithm is the same in both 12.1 (Jan'23 Bundle) and 19.18.
>
> 3. to get those types in 18c and beyond, set _EnableShadowTypes = true
> (can be set at the session level).
> Useful MOS note discussing this parameter: Pipeline Function Returning UDT
> Defined in the Package Specification Raises ORA-00902 Post Database Upgrade
> to 18c (Doc ID 2696498.1)
>
>
>
> On Sat, 1 Apr 2023 at 19:58, DOUG KUSHNER <dougk5_at_cox.net> wrote:
>
>> No luck with either recommendation. I ran all of the SYS queries in the
>> trace file that preceded the type creation statements, but none of them
>> returned the value *8642B728.*
>>
>> Guess this will remain a mystery.
>>
>> Thanks,
>> Doug
>>
>>
>> On March 31, 2023 at 9:03 AM Jonathan Lewis <jlewisoracle_at_gmail.com>
>> wrote:
>>
>>
>> For this example, what's the object_id of the package?
>>
>> The hex might be a multi-part number from which you could split out the
>> package id. Convert the object_id to hex then keep multiplying by 2 until
>> you overshoot the hex.
>> Alternatively, how about enabling tracing while you execute the package
>> and create the objects - you might see a clue about the source of the hex -
>> e.g. a call to a sequence.
>>
>>
>> Regards
>> Jonathan Lewis
>>
>>
>>
>> On Fri, 31 Mar 2023 at 16:38, DOUG KUSHNER <dougk5_at_cox.net> wrote:
>>
>> Our developers have created packages that define a type. Upon package
>> creation, 3 type objects are implicitly created in the package owner's
>> schema, all with name 'SYS_PLSQL%'.
>>
>> In 11.2.0.4, the names are similar to the following, where '3557076' is
>> the package's object_id.
>>
>> SYS_PLSQL_*3557076*_DUMMY_1
>> SYS_PLSQL_3557076_27_1
>> SYS_PLSQL_3557076_9_1
>>
>> In 12.1, the type name does not contain the parent object's id, but some
>> unknown hex value.
>>
>> SYS_PLSQL_*8642B728*_9_1
>> SYS_PLSQL_8642B728_DUMMY_1
>> SYS_PLSQL_8642B728_27_1
>>
>> Does anyone know how to find the parent package's object_id in 12.1 and
>> later versions?
>>
>>
>> Regards,
>> Doug
>>
>>
>>
>>
>

-- 
Best regards,
Sayan Malakshinov
Oracle performance tuning engineer
Oracle ACE
http://orasql.org

--
http://www.freelists.org/webpage/oracle-l