Re: Converting ORACLE_SID to service name and browsing active Oracle database service instances

(Resent because either freelists handles valid mail badly if it's base64-encoded for UTF-8 support or because there are some users whose clients do so. This should be non-UTF-8 and therefore non-base64-encoded.)

Hi.

I am currently facing two problems that I need to solve and have not as yet been able to find a satisfactory answer to them. For the first issue, I can do some home-grown parsing if I absolutely must, but it would seem to me that there should be a way to do this. The last is much harder.

A brief background. I am working on software that automates the operating-system backup mechanism for Oracle (once the database is in ARCHIVELOG mode). The last version we released used otlv4.h talking through the Oracle 8i oci8 libraries. Our agent, used for configuring backup tasks, must be able to connect to any Oracle database instance on the current system. $ORACLE_HOME was causing significant problems and we had to do a hack to be able to connect to Oracle 8i or 9i databases. It worked reasonably well ... on Solaris.

We recently ported it to work on Windows as well as Solaris, and in the process we changed from plain oci8 to OCI over the Oracle 10g Instant Client so that we could avoid the whole $ORACLE_HOME mess, and it has worked beautifully except for a few minor problems.

1. For the existing Solaris installations, it would be very useful if I could *easily* convert the ORACLE_SID that I have for old-style connections into a service name for use with Oracle Instant Client. That is, if my SID is SOL8IDEV and its service name is sol8idev.development.example.com, is there any way that I can do this without parsing through the tnsnames.ora for the active listener? I can always do the latter, but that's ugly.
2. Our user experience team is very unhappy that we seem to be unable to browse available Oracle database instances on a machine, requiring the DBA enter the full service name of the database by hand. Does anyone know of a programmatic way to do query this from the localhost listener? Again, I am pretty sure that I could execute "lsnrctl status" and parse the output, but that seems pretty ugly and fragile.

I found programs that queried the TNS listener, but this only seems to work on Oracle 9i and lower listeners; has anyone found a way to do this that is supported by Oracle directly?

-austin

--
http://www.freelists.org/webpage/oracle-l