Naked Oracle connections

'Want to connect to Oracle without

tnsnames.ora

or

sqlnet.ora

?

I'll show you how.

As a developer, the main slices of time I spend with Oracle go to:

• figuring out a configuration that gives me any sort of workable connection;
• inferring the current data dictionary, and how it differs from the documented one; and
• waiting for central administration to correct privileges that were already alleged to have been assigned.

Textbooks focus most on SQL coding, and schema and algorithm design, of course. While that's as it should be, it certainly doesn't reflect my on-the-job experience. Far too much of my time goes to tracking down basic connection configurations, often when the Oracle administrator has either moved on to another position, is completely backed up with emergency tasks, or is otherwise unavailable. The usual advice from co-workers starts with, "just use what's in

tnsnames.ora

...", but you can imagine the problems with that: as a debugger or developer, I need to make changes, and the reference

*.ora

is inevitably the production one, which it would be unprofessional to modify.

Even when it is permissible to update

*.ora

, it's often inconvenient, or, more accurately, it's more convenient to maintain a complete test script or debugging configuration or development jig in a single file, rather than trying to synchronize several different "moving pieces"--a

*.ora

, a

*.sql

, and so on.

The good news, though, is that the widely-believed folklore about Oracle is false: it is possible to connect without

*.ora

. It's possible, if rather recondite, to fit everything on a single command-line. Here's an example from recent work of mine:

    sqlplus $ACCOUNT/$PASSWORD@'(DESCRIPTION=
                        (ADDRESS=(PROTOCOL=TCP)
                                          (HOST=$HOST)
                                          (PORT=1521))
                        (CONNECT_DATA=
                                          (SERVER=DEDICATED)
                                          (service_name=$SN)))'

Notice this is slightly stylized; in the real one, the $PAR-s are actually dereferenced, and there's no newline. Use of true variables introduces more subtleties beyond the scope of today's tip.

This is not new; the command-line ...@'...' interpretation has been possible for at least fifteen years. Many developers don't believe it exists, though, or are confused about the syntax. It's also timely to note that now, in 2010, developers can work with and learn essentially all database managers at no cost; all the vendors have no-charge versions available for development. The conclusion: don't wait for someone else to set you up to be productive with Oracle or other RDBMSs: you already have what you need.

This story, "Naked Oracle connections" was originally published by ITworld.