sqlplus – Making Oracle a Little Easier

For those not in the know, most database systems have a cli utility that allows for execution of SQL queries from the command line. Coming from a MySQL background, the entire setup, and configuration of Oracle seems very daunting. I knew of sqlplus, but not much outside of that.

Flashforward to today, where I had to run a process from cron on our HP-UX machine. I copied the structure of a few other files for examples, changed the relevant sections, then tried to run my code. I was met with aggrevating failure. To keep others from suffering as I did, I present a concise guide to SQLPlus:

Getting SQLPlus:

SQLPlus ships with the Oracle client and networking drivers, database packages, but not with the Oracle Instant Client.  If you are running the instant client, get SQLPlus from here. Unfortunately, will need an oracle account, so “borrow” one to save some time

Example Connection:

sqlplus username/password/TNS_name
SQL> SELECT 'x' FROM dual;
SQL> DESCRIBE dual;
SQL> disconnect
SQL> exit

Voila, you have now conquered the “Hello World” of SQLPlus. On to more useful things… To execute a stored procedure, use the following format (after logging in):

SQL> BEGIN
SQL>   dbms_output.put_line('Hello World!');
SQL> END;
SQL> /

Note the trailing “/” character. This tells SQL plus that we are done with our procedure call, and to execute it. One more point of interest…

SQL> INSERT INTO test (col1, col2, col3) VALUES ('val1', 'val2', 'val3');
SQL> commit;
SQL> exit

In a transaction based environment (where changes are not applied until we say so) committing our change is easy. Just call “commit” before exiting.

Ok, now lets move the SQL and the execution into two separate files. We will create a “.sh” file to call the SQL (“.bat” for Windows), and a “.sql” file containing our SQL code. Using the SQLPlus convention, we can reference a filename instead of SQL to execute by prefixing it with an “@” symbol.

Contents of “test.sh”:

#/bin/sh

sqlplus username/password/TNS_name @test.sql > output.txt

exit 0

Contents “test.sql”:

select 'x' from dual;
exit

Make sure to chmod these puppies so we can execute them directly. After a quick check that “output.txt” contains the results of our query, our proof of concept is done. Let move this into Cron:

Unix shell

$>crontab -e

Crontab entry

@daily /path/to/test.sh

Save and exit, and you should now have an updated crontab running our test.sh file daily.