SQL+ connect identifier

Sergio describes on his blog how you can connect with sql+ to to a database without the need of the tnsnames.ora file. This is great, but only available since 10gR1, and you have to provide the password in the connect string.

  1. You are absolutely right in both statements. However, user input still needs the knowledge of the url and thus knowledge of
    IP numbers, ports and so on. And how do we handle those nasty things called ‘overnight batchruns’ where there are no users
    available?
    BTW, people who are using their own local tnsnames file should be flogged :-)

  2. 1. This is NOT true. You can provide the username and password at the moment you get the connection: DriverManager.getConnection (dbUrl, dbUser, dbPwd);. The username and password should not be part of the url.
    2. NOT true either. It is very possible to ‘create’ the jdbc url at runtime based on user’s input or a configuration file. Besides, when you use an Oracle client with a local tnsnames.ora you have to change your configuration too. By the way, web applications offer similar flexibility through the use of DataSources.

  3. Of course JDBC drivers can be used to connect to the database but in my opinion there are two major disadvantages:

    1. Configuration of the driver requires the login credentials in the url which is a potential security risk.
    2. If a database is moved to another machine, if the database is renamed, if the port of the listener is changed, if…. any
    other change, then the JDBC driver must be reconfigured.

    IMHO the best practice is to install a minimal runtime client on the machine that is used to connect to an Oracle database.

  4. Java clients use JDBC for the database connection and do not rely on an Oracle client. Many Java ide’s, like JDeveloper and Eclipse with the database tools, offer database functionality. Many Java based clients are available, but I do not have a recommendation. In the past I’ve used Squirrel.
    But if you need an Oracle client, like TOAD or PLSQL Developer, you can try the Oracle Instant client.

  5. Do you know any SQL Tool which doesn not require Oracle Client to be installed? This feature os the best in these cases. Personally, I do not want to install Oracle Client on my machine. I searched online, but could not get any.

  6. <code>
    SQL> connect system/oracle
    @//vamisnt02:1521/lab
    Connected.
    
    SQL> connect system/oracle
    @//vamisnt02:1521/lab.amis.nl
    Connected.
    
    SQL> connect system
    @//vamisnt02:1521/lab.amis.nl
    SP2-0306: Invalid option.
    
    Usage: CONN[ECT] [logon] [AS {SYSDBA|SYSOPER}]
    where [logon]  ::= [username][/[password]][@[connect_identifier]] | /
    
    SQL> connect system
    @"//vamisnt02:1521/lab.amis.nl"
    Enter password: ******
    Connected.
    
    SQL> connect system/oracle
    @(DESCRIPTION=(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)
    (HOST=vamisnt02.amis.nl)(PORT=1521)))
    (CONNECT_DATA=(SERVICE_NAME=LAB)))
    Connected.
    
    </code>