Re: Possible to determine in which table an object is stored?

"TurkBear" <john.greco_at_dot.state.mn.us> wrote in message news:kv72cvgmve7qkmmuppms6v8u8tncjtbqvs_at_4ax.com...
> "Maximus" <qweqwe_at_qwqwewq.com> wrote:
>
> >Is there a way to determine which schema and table a particular object
> >belongs to?
> >
> >For example, I want to write a function that accepts an object ref and
 then
> >does operations on it's storage table (or none at all if the object is
 not
> >persistent).
> >
> >Regards
> >
> >
> Multiple group posting is not usually a good idea..
>
> Try using the
>
> ALL_OBJECTS view to determine which OBJECTS may have a table storage
 component..
>
> By linking this to other views ( ALL_TABLES, etc) you should be able to
 work with it in the way you want..
>
> here is what is in the all_objects table:
> SQL> desc all_objects
> Name Null? Type
> ----------------------------------------- -------- ----------------------
 -
> OWNER NOT NULL VARCHAR2(30)
> OBJECT_NAME NOT NULL VARCHAR2(30)
> SUBOBJECT_NAME VARCHAR2(30)
> OBJECT_ID NOT NULL NUMBER
> DATA_OBJECT_ID NUMBER
> OBJECT_TYPE VARCHAR2(18)
> CREATED NOT NULL DATE
> LAST_DDL_TIME NOT NULL DATE
> TIMESTAMP VARCHAR2(19)
> STATUS VARCHAR2(7)
> TEMPORARY VARCHAR2(1)
> GENERATED VARCHAR2(1)
> SECONDARY VARCHAR2(1)
> --------------------------------------------------------------------------

I figured out how to extract the object type oid and table oid from an object REF using the REFTOHEX function. One the system oids are had, it's just a matter of looking them up in all_objects, all_tables, all_types to find the schema, table, and type associated with the object REF.

Kind of hacky but hey... it works.

Regards Received on Thu May 15 2003 - 01:54:01 CEST