Re: oracle-l Digest V14 #334 (sql.bsq, now modularized to dcore.bsq)

Thanks for chiming in Mark.

In response to your comment on user-defined types, yeah I don't care about those. I just care about what shows up in obj$.type#. For UDT's one of those numbers is going to cover that.

I like your ideas. I have spent a lot of time in those BSQ files - from the very moment I discovered them. They are an absolutely awesome source of information and can lead to loads of new things to learn. They certainly have for me.

However, in this particular case I was looking for something that you would be able to encapsulate in a piece of code. Production ready code. In other words, I was hoping it would be available somewhere, in some dictionary table, a base table, or even an x$ table. But having looked all over and also seeing fairly little responses here, I am lead to believe that it isn't out there. The most prevalent evidence being that even in most recent versions, Oracle still hard-codes that decode() in so many views, instead of using "that missing table". Perhaps they have a similar version of bootstrap$ stored in a variable somewhere that returns the decode for them :)

Also elaborating on Alan's suggestion; yeah I can parse that dba_objects() decode, and add some handlers to make it viable in the current version(s) [ I'd need a slightly different query for each of 11.2, 12.1 and 12.2 ] ; but, I don't know, I just don't like it from a code quality PoV. It just feels ugly. And I don't like ugly code.

Perhaps I have to take another angle at this altogether and eliminate the need for this mapping.

Stefan

On Mon, Dec 25, 2017 at 2:32 AM, Mark W. Farnham <mwf_at_rsiz.com> wrote:

> Look for something like: C:/ora/p/12.1/dbhome/RDBMS/ADMIN as a directory,
> depending on the length of symbolic tokens you use in OFA.
>
>
>
> For example if you like having very long strings everywhere with all the
> overhead that entails, you might have:
>
>
>
>
>
> /u01/app/oracle/product/12.2.2/dbhome_1/RDBMS/ADMIN
>
>
>
> anyway, find that directory.
>
>
>
> Then, depending on vintage, you will find one or more .bsq files. For a
> long time there was just sql.bsq. Recently sql.bsq has become a reference
> to a list of .bsq files.
>
> Don’t change any of these files unless… hmm… let’s just say don’t change
> any of these files.
>
>
>
> To conveniently look at them and avoid accidental change hilarity, I
> usually copy, for example, dcore.bsq to dcore_bsq.txt and never even bring
> the originals into an editor.
>
>
>
> I think most of what you’re looking for is in dcore and dobj, but these
> can indeed change between versions and since there can be user defined
> complex types. I haven’t chased down all the references in a couple
> decades, and once you get to oracle 8 with user defined types there is no
> really comprehensive final answer (unless “user defined” is good enough for
> you as a type.).
>
>
>
> An itch in my little finger is saying there might be a C .h file or
> something else that has exactly what you want, but I don’t believe anyone
> can share that with you in lieu of onerous and likely unobtainable legal
> agreements. And I think the general licenses still prohibit decompilation
> (and I’m not certain you can get a version with the symbol table
> unstripped), so this may be impossible.
>
>
>
> This all would be a nice thing for Oracle to publish as a document. Given
> the size of the documentation library I can’t be certain whether it is
> published or not. But certainly the operation of the oracle program
> requires that its programmers have this reference and if they add a type it
> is an intense integration operation to get it right everywhere.
>
>
>
> mwf
>
>
>
> PS: you can learn a lot just reading these files but there is a real risk
> of consuming more time than it is worth. I do still love the sizing of some
> fundamental tables and the comments from early developers about that sizing
> and the number and size of columns they expect in a table.
>
>
>
> *From:* oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_
> freelists.org] *On Behalf Of *Stefan Knecht
> *Sent:* Sunday, December 24, 2017 11:53 AM
> *To:* Alan Sterger
> *Cc:* oracle-l-freelists
> *Subject:* Re: oracle-l Digest V14 #334
>
>
>
> Thanks Alan - I'm indeed aware that there is a representation of that data
> in dba_objects. However, that view changes between versions, and I was
> looking for a more "permanent" solution, without the need to write a
> potentially breaking parser.
>
>
>
> I have since snooped around a bit more and it seems that Oracle themselves
> are constantly using that decode to do the mapping, which leads me to
> believe that such a place does not exist.
>
>
>
> Cheers nonetheless and have a good X-Mas yourself!
>
>
>
> Stefan
>
>
>
>
>
>
>
>
>
>
>
> On Sun, Dec 24, 2017 at 10:54 PM, Alan Sterger <asterger_at_earthlink.net>
> wrote:
>
> Stefan,
>
> DBMS_METADATA can retrieve object types within DBA_OBJECTS with this query:
>
> SQL> set pagesize 0
> SQL> set long 90000
> SQL> select
> 2 dbms_metadata.get_ddl('VIEW', 'DBA_OBJECTS', 'SYS')
> 3 from dual;
>
> where get_ddl three arguments are:
> 1- object type (table, index, view)
> 2- object name
> 3- schema owner
>
> On my client's 11R2 database, query above produces a decode list (0 to
> 101) of o.type# with human readable object_type (varchar19).
>
> Merry Christmas,
>
> -- Alan Sterger
>
>
> On 12/24/2017 1:05 AM, FreeLists Mailing List Manager wrote:
>
> oracle-l Digest Sat, 23 Dec 2017 Volume: 14 Issue: 334
>
> In This Issue:
> Oracle TYPE# to human readable name
>
> ----------------------------------------------------------------------
>
> From: Stefan Knecht <knecht.stefan_at_gmail.com>
> Date: Sat, 23 Dec 2017 19:50:39 +0700
> Subject: Oracle TYPE# to human readable name
>
> Hi all
> Does anyone know of a structure (table, fixed table, anything) that has a
> mapping of all the object types known to the present Oracle version?
>
> What I'm looking for is basically a list, a mapping, of all possible values
> of obj$.type# and their human-readable counterpart. If you look at the
> definition of the DBA_OBJECTS view, it's basically in there with a DECODE()
> on type#. But I'd rather refrain from parsing that if I can help it.
>
> Is this data available anywhere within Oracle?
>
> Cheers
>
>
>
> Stefan
>
>
>
>
>
>
> --
>
> //
>
> zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
>
> Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat
> | _at_zztat_oracle
>

-- 
//
zztat - The Next-Gen Oracle Performance Monitoring and Reaction Framework!
Visit us at zztat.net | Support our Indiegogo campaign at igg.me/at/zztat |
_at_zztat_oracle

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