SQL+ connect identifier Oracle Headquarters Redwood Shores1

SQL+ connect identifier

October 21, 2005 Database, Oracle 11 Comments

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.

Related Posts

About The Author

Aino Andriessen

Aino Andriessen is principal consultant and expertise lead 'Continuous Delivery'. His focus is on Oracle Fusion Middleware ADF and SOA development, Continuous Delivery, architecture, improving the software development proces and quality management. He is a frequent presenter at Oracle Open World, ODTUG Kaleidoscope, UKOUG Technology Conference and OUGN VĂ¥rseminar. He writes articles and publishes at the AMIS technology blog (http://technology.amis.nl/blog/).

11 Comments

  1. Bert Jan Meinders November 11, 2005

    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. Aino November 9, 2005

    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. Bert Jan Meinders November 9, 2005

    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. Aino November 8, 2005

    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. Reader November 8, 2005

    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. John Scott November 4, 2005

    Thanks, glad you enjoyed it Marco đŸ˜‰

  7. John Scott November 4, 2005

    Thanks, glad you enjoyed it Marco đŸ˜‰

  8. Marco Gralike October 27, 2005

    John Scott posted a nice idea on Sergio’s blog see: http://www.shellprompt.net/~jes/docs/ApacheDBLog.pdf

  9. Anil October 22, 2005

    you may also do sqlplus scott/tiger@//host:port/sid

  10. Andre October 21, 2005

    Damn. Marco. You beat me. No need for tnsnames.ora -).

  11. Marco Gralike October 21, 2005 
    
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.