Re: Have host (Tnsname), need Machine:Port:Sid in PL/SQL ?

From: Douglas Hawthorne <douglashawthorne_at_yahoo.com.au>
Date: Sat, 27 Mar 2004 10:18:41 GMT
Message-ID: <5yc9c.126366$Wa.110004@news-server.bigpond.net.au>

"Christof Kaiser" <kaiser_at_logiball.de_no_spam> wrote in message news:4062f110$0$274$4d4ebb8e_at_read.news.de.uu.net...
> Douglas,
>
> thanks for your input!
>
> Yes I know there can be many tsnnames. I am intrested in the one which
> is used if I start sqlplus on the local machine eg
> sqlplus scott/tiger_at_orcl
>
> The reason why I am after this is a migration of our application form
> OCI8 to JDBC connections:
>
> Till now, it was pretty much script based, and one "administration
> schema" was holding the available data schemas. including username and
> host. This was done to allow our application (GUI) to present a list of
> available schemas holding data.
>
> But now, we migrated the application to something java based, using jdbc
> rather then oci8 to access oracle. hence, in the "admin schema", we need
> these machine:1521:sid strings stored rather then the host (and of
> course a proper tnsnames).
>
> This replacemnet (host replaced by machine:port:sid should be done only
> once during the migration.
>
> Another way would be defining and filling a table with all hosts and
> corresponding jdbc information. However, it would be cooler to do it
> automatically.
>
> Regards
> Christof

Christof,

As I understand your post, you would have an entry in your local TNSNAMES.ORA on the client machine similar to the following: ACMECORP =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST=listener.acme.com.us)(PORT=1521))     )
    (CONNECT_DATA =

      (SERVER = DEDICATED)
      (SERVICE_NAME = sales.acme.com.in )

    )
  )

And you want to convert this to the following JDBC connection string: "jdbc:oracle:thin:@listener.acme.com.us:1521:sales"

In "Opening a Connection for the JDBC Thin Driver" on p.3-10 of "Oracle9i JDBC Developer's Guide and Reference", you will find: "Because you can use the JDBC Thin driver in applets that do not depend on an Oracle client installation, you cannot use a TNSNAMES entry to identify the database to which you want to connect. You have to either: "* Explicitly list the host name, TCP/IP port and Oracle SID of the database to which you want to connect.
"or:
"* Use a keyword-value pair list."

The difficulty in this approach is the mapping between the SERVICE_NAME and the SID. This is done by the listener using its LISTENER.ORA and any dynamic registrations. However if follow the Oracle recommendations, then the SID will be the first token of the SERVICE_NAME parameter.

I found a Java-based parser for TNSNAMES.ORA at http://www.orsweb.com/downloads/source/495.html (TNSParser.java) which appears almost to do what you want. I tried running it on WinXP Pro under 9.2.0.1 and have not been able to get any output.

Douglas Hawthorne Received on Sat Mar 27 2004 - 04:18:41 CST