Re: Get database name in OCI or PL/SQL

From: Michael Chen <michaelc7_at_earthlink.net>
Date: Wed, 02 Oct 2002 14:46:18 GMT
Message-ID: <_EDm9.4795$lV3.450294@newsread1.prod.itd.earthlink.net>

Jim,

As I said earlier, the end user most likely connect to their Oracle server by
passing a TNS alias to my program. This alias defined in the tnsname.ora file which encapsulates many configuration details, includeing the database name in the SERVICE_NAME setting. For example, a customer may have the following tnsname.ora entry:

XYZCorporateDatabase =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.1)(PORT = 1521))     )
    (CONNECT_DATA = (SERVICE_NAME = ORCL816))   )

Then, my program only knows it is connected via XYZCorporateDatabase, but the OCIDescribeAny() function needs the database name ORCL816 as its parameter to create the OCI_PTYPE_DATABASE descriptor.

Why do I need this descriptor? Take a look at my original post below, and no I do not want schema name. If you like to know what I am doing, take a look at the two hyper-links I included in my original post and try to write
an OCI program to get those database attributes. By doing so, you will find out how poorly the OCI documentation was written for this feature, and how clumzy the design is.

Thank you

--Michael

"Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message news:J4tm9.4006$dp1.9810_at_rwcrnsc52.ops.asp.att.net...
> Once you are connected I would think you already know the name. How did
you
> connect? Of what use is the database name anyway? It isn't like
SQLServer
> where you have multiple databases in an instance. Perhaps you mean the
> schema name? I've usedOracle for a long time and the only time I need the
> database name is either with regards to replication so we have unique
names
> or I am cloning a production system to a test system and I want to rename
> the database. (So I can have a test instance, a development instance on
the
> same machine all copies of a point in time of production.) OCIDescribeAny
> seems pretty straighforward maybe you wanted it too look more ODBC API
like?
> Jim

>
>

> "Michael Chen" <michaelc7_at_earthlink.net> wrote in message
> news:XYnm9.3554$lV3.301252_at_newsread1.prod.itd.earthlink.net...
> > Jim, TurkBear, David and Cliff,
> >
> > Thank you for your help. You all suggested using the v$database and 2
> other
> > v$ views to get the database name. The problem with me is that I am
> writing
> > a generic shrink-wrap software that will be distributed to a large
number
> of
> > customers, and the program may be run using any account with any
> > priviledges. It is not practical at all to ask all DBAs to grant public
> > access to these security sensitive v$ views, or create a pre-defined
> synonym
> > and hardcode such synonym into my program.
> >
> > I am hoping to find a generic and definitive way to get the database
name,
> > so that my program can make uses of database attributes. Otherwise, it
is
> > fair to say that OCI is very poorly designed in this particular feature.
> > Actually, the whole OCIDescribeAny and attribute hierachy thing are
poorly
> > designed and unprofessionally documented.
> >
> > Thank you
> >
> > --Michael
> >
> > "Michael Chen" <michaelc7_at_earthlink.net> wrote in message
> > news:Q61m9.1053$OB5.100694_at_newsread2.prod.itd.earthlink.net...
> > > Hello,
> > >
> > > In Oracle 8i, function OCIDescribeAny can return OCI_PTYPE_DATABASE
type
> > > information such as OCI_ATTR_CURSOR_COMMIT_BEHAVIOR and
> > > OCI_ATTR_LIST_SCHEMAS. To get the handle from OCIDescribeAny for a
> > > database, you need to pass the database name:
> > >
> > >
> > >
> >

>
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/oc
> > > i15r32.htm
> > > also
> > >
> > >
> >

>
http://download-west.oracle.com/otndoc/oracle9i/901_doc/appdev.901/a89857/oc
> > > i06des.htm#444119
> > >
> > > The problem is my OCI connection may not have prior knowledge of the
> > > database name. What is passed to function OCIServerAttach may be just
> the
> > > TNS alias name defined in tnsnames.ora. Is there any way in OCI or
> > > PL/SQL for me to get the database name after the connection is
> > > established?
> > >
> > > Thank for your help.
> > >
> > > --Michael
> > >
> >
> >

>
>
>

Received on Wed Oct 02 2002 - 09:46:18 CDT