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

One tactic would be to create by enumeration all the types you do care about as columns of a single table, perhaps with the type created in the column comment and then spit out for that single table the join of the column number, type#, and the comment to populate a type map table for direct look ups. I’m not entirely sure of your goal. I was thinking it was a list of the possible types.  

Of course if Oracle adds a new native type you would need to add a column to your table.  

mwf  

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Stefan Knecht Sent: Monday, December 25, 2017 6:49 AM
To: Mark W. Farnham
Cc: Alan Sterger; oracle-l-freelists
Subject: 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

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 Received on Mon Dec 25 2017 - 20:17:56 CET