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

In 19c, the ora_hash of the parent object matched the substring in the type object's name.

In 12.1.0.2.0 (with no patch sets installed), the ora_hash of the parent package was the same as in 19c, but did not match the substring in the type name (SYS_PLSQL_8642B728_9_1).

'C1BEECFF' != '8642B728' Makes you wonder how they computed the hash value in early 12.1 releases.

> On April 1, 2023 at 1:36 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 mailto: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 mailto: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 mailto: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
> > > >
> > > > > > >
> > > > >
> >
> >
> >
> > >

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