Re: INFORMATION_SCHEMA views in Oracle

crwarman_at_yahoo.com (Craig Warman) wrote in message news:<a24e13f4.0411010611.217cb7e2_at_posting.google.com>...
> No, my request is not reinventing the wheel. I'm very familiar with
> the objects in Oracle data dictionary. To be clear,
> INFORMATION_SCHEMA is a SQL-92 metadata implementation that some
> vendors (such as Microsoft, surprisingly) have implemented, while
> others (Oracle, in this case) have not. Oracle's reason for not
> implementing this is because the data dictionary views do in fact
> provide all of the information defined by INFORMATION_SCHEMA. I have
> no problem with that. The system I'm migrating, however, makes
> extensive use of this INFORMATION_SCHEMA metadata, so rather than
> re-write those calls to make them Oracle-compliant I need to first see
> if it would be a less time-intensive task to implement the schema
> (this is important to my client, to minimize codebase modifications).
> And, given that nature of the problem, it seems prudent to see if
> someone else hasn't already given it a shot before diving in.
>
> So your response to my post was, frankly, unecessarily confrontational
> and abrasive. There are enough rude people on the Internet. You
> don't need to be one of them.
>
> Craig
>
>
>
> DA Morgan <damorgan_at_x.washington.edu> wrote in message news:<1099098801.860646_at_yasure>...
> > Craig Warman wrote:
> >
> > > I'm working on a MS SQL to Oracle migration project for a Perl-based
> > > system. This system makes use of the INFORMATION_SCHEMA metadata
> > > implementation in MS SQL. I'd like to emulate that in an Oracle
> > > 9.2.0.4 instance, which (I believe) means I'll need to develop some
> > > equivalent views in a schema of that name.
> > >
> > > I've done a good bit of searching, and found a posting in this forum
> > > by Peter Weighill (link below) that provides a starting point. But
> > > before I go re-inventing the wheel, has anyone come up with anything
> > > more comprehensive than what Peter posts there? If so, I'd like to
> > > build on that if I could.
> > >
> > > Here's a link to the above-mentioned post:
> > > http://groups.google.com/groups?hl=en&lr=&selm=ca4tin%24sca%241%40hercules.btinternet.com
> > >
> > > Also, if this post comes to the attention of Peter Weighill, just
> > > wanted to pass on my thanks for his having shared that info.
> > >
> > > Craig
> >
> > Your entire request is "reinventing the wheel": You just need to learn
> > the objects in the data dictionary.
> >
> > Log onto Oracle with DBA privs and perform the following:
> > SELECT * FROM dictionary;
> > Which item has what you are looking for you'll need to figure out as
> > you've given us no hints.

Craig, I can see how there could be some advantages and benefits to application developers if all major RDBMS vendors supported a common set of dictionary views. This is providing that the dictionary view set defined by ANSI was fully normalized.

Still, the reality is that up untill now each database vendor has created their own set of dictionary views if they have them at all and do not just provide a set of commands to display dictionary information. If you want the information schema in Oracle you will probably have to write most of it yourself unless other DBA's interested in porting the schema post what they have done.

Just my two-cents -- Mark D Powell -- Received on Tue Nov 02 2004 - 09:10:39 CST