Oracle Practical 8
Page 1 of 2
COMMAND FILES
By now, you have received logons and passwords for your own individual accounts within Oracle.
This means that you now have the power to create objects, for projects etcā¦ What we cover this
week will be very useful in this respect. It does not relate directly to any Lectures, but donāt worry,
all of that material will be covered within these tutorials. What we are covering today is the
creation of professional Oracle command files.
A SQL COMMAND FILE.
For example, you might create a command file which when run within SQLplus carries out a few
basic tasks, like setting up the SQLplus environment, accepting runtime variables, setting script
specific variables, logging, run date recording etcā¦ And in the middle of all this, you can specify
things you want done, such as Table creation, data inserts etcā¦
The purpose of all this, is so that another person can come along later and examine the scripts,
see who created them, and when they were last run (from logs), and if there were any errors
during the scripts runā¦
Command Files: Setting environment variablesā¦
To do this we use SET, which turns a SQLplus (the interface) feature ON or OFF or sets it to a
certain value.
SET LINESIZE 400 sets the line width, or the total number of characters that can be displayed
before wrapping onto the next line.
PAGESIZE 0 sets the number of lines per page. By setting it to 0, we are telling SQLplus that we
only want data displayed and never print the column headings.
ECHO OFF makes SQLplus echo (display) commands to the screen as they execute within a
command file. OFF makes SQLplus execute them without displaying them. Results of queries on
the other hand are controlled by TERMOUT.
TERMOUT OFF tells SQLplus not to display any contents from a command file, or results from
queries within that file. This is useful when you are spooling results to a file, as when Oracle has
to spool results to a screen also, performance is degraded,
FEEDBACK OFF tells oracle not to tell us how many rows were selected by a query.
VERIFY OFF tells SQLplus not to confirm variable substitutions at runtime. Examples will be
evident later. (Where the & sign is used).
Note at the end of the script, these environment settings should always be set back to their
original values so that SQLplus behaves in a normal fashion. Or else, exit SQLplus, as when
these are set like this, they are only valid for the current session.
Command Files: Defining Script Variablesā¦
This is done with the following syntax:
DEFINE SCRIPTNAME = ātest1ā
What we have done here is told SQLplus, that every time it comes across the variable
SCRIPTNAME, substitute it with ātest1ā. Note that Case is unimportant when specifying a variable
name. Down a few lines further in the file, you will see this variable has been printed with the
Ampersand sign preceeding it. This tells SQLplus that we are dealing with a variable, and to
substitute it with the value we already specified.
Command Files: Displaying/Printing to screenā¦
In this command file, we use PROMPT to tell SQLplus that we want strings printed to screen as
instructions or information to the user. Because we have TERMOUT ON and ECHO OFF at this
Docsity.com
