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

12.1.0.2 without patches uses just the package name followed by the NUL character.

SQL> select object_name from obj;

OBJECT_NAME

PKG_121
SYS_PLSQL_88405862_9_1
SYS_PLSQL_88405862_DUMMY_1

SQL> select to_char(ora_hash('PKG_121'||chr(0)), 'fm0XXXXXXX') from dual;

TO_CHAR(O

I think Oracle started appending the schema in 12.1.0.2.160719 (untested).

On Sun, 2 Apr 2023 at 21:47, DOUG KUSHNER <dougk5_at_cox.net> wrote:

> 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> 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
>
>
>
>
>
>
>

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