Scripting with SQL*Plus

As you can imagine, once you start dealing with long SQL statements and with numerous COLUMN commands, it quickly gets tiring to type in all those commands each time you want to generate a report. Fortunately, you do not have to do this. SQL*Plus can read commands from a text file. Simply use any text editor, such as vi or Notepad, and create a text file containing the commands that you want to execute. For example, to generate the report you saw earlier that lists all database objects, you could place the following commands into a text file named db_objects_by_type.sql:

COLUMN owner FORMAT A12
COLUMN object_type FORMAT A10
COLUMN object_name FORMAT A30
SELECT owner, object_type, object_name
FROM dba_objects
ORDER BY owner, object_type, object_name;

Once you have built a file containing these commands, you can execute that file using a one-letter SQL*Plus command known as "@".
The @ command is described in the next lesson.

SQL*Plus is an interactive and batch query tool that is installed with every Oracle Database installation. It has a command-line user interface. There is also the SQL*Plus Instant Client which is a stand-alone command-line interface available on platforms that support the OCI Instant Client. SQL*Plus Instant Client connects to any available Oracle database, but does not require its own Oracle database installation. See the Oracle Call Interface Programmer's Guide for more information on the OCI Instant Client.
SQL*Plus has its own commands and environment, and it provides access to the Oracle Database. It enables you to enter and execute SQL, PL/SQL, SQL*Plus and operating system commands to perform the following: Format, perform calculations on, store, and print from query results

1. Examine table and object definitions
2. Develop and run batch scripts
3. Perform database administration

You can use SQL*Plus to generate reports interactively, to generate reports as batch processes, and to output the results to text file, to screen, or to HTML file for browsing on the Internet. You can generate reports dynamically using the HTML output facility of SQL*Plus.