Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Get database name in OCI or PL/SQL

Re: Get database name in OCI or PL/SQL

From: David Fitzjarrell <oratune_at_msn.com>
Date: 2 Oct 2002 10:56:25 -0700
Message-ID: <32d39fb1.0210020956.54959e67@posting.google.com>


I don't know about your system, but I had to have a much larger field than 25 for this to work:

DECLARE
IDontKnowWhereIHaveConnected VARCHAR2(64); BEGIN

     IDontKnowWhereIHaveConnected := database_name;
     dbms_output.put_line('I am connected to ' ||
IDontKnowWhereIHaveConnected);
END;     

/

Granted, it does work for non-DBA accounts, although it returns the fully qualified database name:

12:46:25 DBTECH> /
I am connected to DBTECH.SYSDIV.BCGSYSTEMS.COM

PL/SQL procedure successfully completed.

12:46:26 DBTECH> Of course you could always use substr() and instr() to return only the name:

DECLARE
IDontKnowWhereIHaveConnected VARCHAR2(64); BEGIN

     IDontKnowWhereIHaveConnected := database_name;
     IDontKnowWhereIHaveConnected :=

substr(IDontKnowWhereIHaveConnected, 1,
instr(IDontKnowWhereIHaveConnected, '.', 1, 1) -1);

     dbms_output.put_line('I am connected to ' || IDontKnowWhereIHaveConnected);
END;
/

and get:

12:55:24 DBTECH> /
I am connected to DBTECH

PL/SQL procedure successfully completed.

12:55:50 DBTECH> David Fitzjarrell

rajXesh_at_hotmail.com (RK) wrote in message news:<548b9514.0210020559.63e6ea90_at_posting.google.com>...
> Michael,
>
> You can try the following code in PL/SQL
>
> DECLARE
> IDontKnowWhereIHaveConnected VARCHAR2(25);
> BEGIN
> IDontKnowWhereIHaveConnected := database_name;
> dbms_output.put_line('I am connected to ' || IDontKnowWhereIHaveConnected);
> END;
>
> The database name is defined in dbms_standard
>
> -- rajXesh
>
>
> "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 - 12:56:25 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US