SQL*Plus

Oracle's SQL*Plus utility is the standard command line or terminal session way to query an Oracle database. You can execute it and login interactively by just running the following:
sqlplus
If you want to automate the login then you can execute this:
sqlplus username/password@oracle-sid
You can further build on this by specifying a SQL Script to execute as follows:
sqlplus username/password@oracle-sid @filename.sql
I have used this in shell scripts but it is worth noting that if the .sql file does not end with an "exit" statement then SQL*Plus will keep running. Therefore you can use the following trick to pipe an exit in:
exit | sqlplus username/password@oracle-sid @filename.sql
Which does work a treat. It is also worth noting that this works whether the .sql file ends with an exit statement or not, which is nice.

It is worth noting there are two command line switches that are handy, especially when using within a script:

  • -L - attempt to login just the once
  • -S - silent mode, suppresses banners, prompts etc
There is another command line switch /nolog which allows you to start SQL*Plus without connecting to a database as follows:
sqlplus /nolog
From here you can "connect" a SYSDBA and start a database.

Executing Scripts

When you use SQL*Plus to execute a SQL Script there are two things I have found useful.

Finishing Gracefully

If you add quit; to the very end then SQL*Plus will terminate nicely, which is handy.

Error Handling

By default SQL*Plus will just keep going, so after the first error it continues, however often it is better to just stop on the first error, hence the following lines at the start of your script will help.
whenever sqlerror exit sql.sqlcode;
whenever oserror exit failure;
This will set the SQL*Plus exit code so you can check for non-zero values.