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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxx> 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@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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@xxxxxxxxxxxxx> 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@xxxxxxxxx>
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 |
@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 |
@zztat_oracle
