Re: Get database name in OCI or Transact-SQL

Michael Chen wrote:

> Jim,
>
> I meant to say PL/SQL. However, your suggestion doesn't help me. All V$
> views are only accessible to SYS and SYSDBA logins. I need a way to get the
> database name after the OCI program login as a generic user, like
> scott/tiger. Any idea?
>
> If I were to use MS SQL Server, I can do a "select db_name()" to get the
> default database associated with my account. I need to do the same in OCI,
> otherwise all the database attributes are not accessible.
>
> Thank you
>
> --Michael
>
> "Jim Kennedy" <kennedy-family_at_attbi.com> wrote in message
> news:uH6m9.469950$kp.1327430_at_rwcrnsc52.ops.asp.att.net...
> > I don't know how using Transact SQL would work with an Oracle database.
> > Transact-sql is a MS SQLServer thing.
> > You could get the information from a v$table in the database. Look in the
> > Oracle Reference guide.
> > Jim
> > "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
> > > Transact-SQL for me to get the database name after the connection is
> > > established?
> > >
> > > Thank for your help.
> > >
> > > --Michael

It is not true that you need to be a DBA to see v$ tables.

If you truly need this information then your DBA will grant you the privilege. Ask them to do the following:

GRANT SELECT ON v$... TO <your schema_name>

If they don't then you likely didn't need the information.

Daniel Morgan Received on Wed Oct 02 2002 - 10:22:16 CDT