Is it possible to get the oracle server port no from a sql query?


+3  A: 

It doesn't seem to be directly possible: see the discussion here on askTom


No, but in order to run a query you're going to need a connection to the DB so I suspect you can get the port using a call to java.sql.DatabaseMetaData.getURL(). Not sure exactly what the Oracle drivers return but something like this should do the trick:

String url = connection.getMetadata().getURL();
String port = url.substring(url.indexOf(":"), url.indexof("/", url.indexOf(":")) - 1);
Nick Holt
chicken and egg: how can you make a connection without knowing the port number? can't get meta data without it.
@duffymo: like I said, if you're going to run a query you must have a connection. If the question was how do I connect to some Oracle instance on the network somewhere then then agreed you're screwed and it's time to phone the DBAs and get some more information.
Nick Holt
im creating a connection to the oracle database using the sun.jdbc.odbc.JdbcOdbc driver.Just require the DSN to create the connection.Once I have the connection I want to find out the port no on which the server is running..
@Fell: I've managed to avoid the JDBC bridge driver but surely it still needs the connection information host, *port*, SID, etc, which is returned as per the docs (http://java.sun.com/j2se/1.3/docs/guide/jdbc/getstart/bridge.doc.html)? If the port is missing then I suspect the DB is listening on the default port, 1521 I think for Oracle.
Nick Holt