Author: marvin

set pagesize 0

set linesize 132

format first_name column A20

format salary column $999,999.99

http://download.oracle.com/docs/cd/B19306_01/server.102/b14357/ch6.htm

Guess who can never remember how to do the equivalent of “show tables” in Oracle’s SQL*Plus? That’s right: Me. There’s no shorthand for it. You have to use a query like this one:

select object_name from user_objects;

The user_objects table holds more than just table names, however. So you might want to limit the results to tables. Like this:

select object_name from user_objects where object_type = 'TABLE';

Some of the types that you my filter on are TABLE, INDEX, SEQUENCE, and FUNCTION. And yep, those are all uppercase, and you’ll have to type ’em in that way if you want it to match on most servers.

Can’t connect to your Oracle database like I couldn’t all freakin’ morning? Grrr. I think what I really couldn’t connect to was my tnsnames.ora file. I still have no idea how to get that to work after all these years. But this page explains how to put the connect string on the command line. And that just plain works.

In short, instead of depending on a tnsnames.ora file, you can use a command of the form:

sqlplus username@'(annoying connect string here)'

On Windows, use double quotes:

sqlplus username@"(annoying connect string here)"

It’s ugly to be sure, but it gets the job done. And it fits on a single line of some hunk of documentation that you (that is to say, I) have to write, and doesn’t rely on any installation dependent tnsnames.ora file.

If you’ve every tried to install Oracle on any UNIX/Linux system, you know what pain is — especially if all you want is the SQL*Plus client software.

Edit: This page does a better job of explaining it. (I think the page at Oracle’s site changed since I originally wrote this.)

What has also changed is that Oracle now provides the client software in its own downloadable package. You have to install a few additional libraries on Linux, but that’s not too tough.

Here’s a page that describes how to download and install SQL*Plus in a few relatively simple steps.

This Spring tutorial saved my life.

Just sayin’.

Kiva is an organization that makes microloans throughout the world to improve the standing of people everywhere. And it’s you who makes the loans — loans as small as $25. The repayment rate? Darned near 100%. And it’s kinda fun.