100-102

Previous

Table of Contents

Next

The ttitle Command

The ttitle command instructs SQL*Plus to print a title at the top of each page of a report. You can perform a number of actions using the ttitle command, such as:

   You can use multiple lines in the title using the skip clause, as shown in this example:

 ttitle skip  n  SYSDATE; 

In this example, n is the number of lines to be skipped and defaults to one line. If n is set to 0, SQL*Plus returns to the beginning of the current line.

   You can align text to the left, right, center, or to a specified column, as shown in the following example:

 ttitle left 'MaldenPower' center 'Quality Control' right SYSDATE;   ttitle col 50 'Report'; 

   You can specify a number of tabs for alignment of the footer, as shown in this example:

 ttitle tab  n  'Malden Power'; 

In this example, n is an integer value; a negative value skips backward and a positive value skips forward.

   You can turn the title off and on without affecting its definition, as shown in this example:

 ttitle off;   ttitle on; 

The undefine Command

The undefine command is used to dispose of a variable inside an SQL session. The format of the undefine command is:

 undefine <variable>; 

Once a variable has been undefined, it can no longer be referenced unless it is redefined.

Connecting To SQL*Plus

You ll probably connect to SQL*Plus using a command like:

 sqlplus  username/password  

SQL*Plus provides some alternative methods of connecting that you should be aware of. For instance:

 sqlplus [-silent]  username/password  

Logging into SQL*Plus using the -silent flag instructs the program not to give any informational messages (not even a prompt). This is particularly useful when your script is dynamically generating code or when executing SQL*Plus from inside another piece of software.

If you login and find that you need to be working under another schema, you can login to a new schema without exiting SQL*Plus and reconnecting by using the following command:

 connect  username/password  

You can use any of these login commands without typing your username or password as part of the command; SQL*Plus will prompt you for these values if you don t include them in the command. Using your password as part of a command reveals the password to anyone who sees the command, so it s probably better to enter your password when prompted in most situations.

Logging Out Of SQL*Plus

The exit and quit commands are used to leave SQL*Plus and return to the operating system. You should be aware that using either of these commands implicitly commits changes to the database. If you ve made changes that you don t want to save, you ll have to roll back prior to issuing one of these commands.

You can use this command to return a status to an OS-level application that called the script, as shown in the following example:

 exit success 

There are five values that can be returned: success , failure , warning , integer , and variable .

    success ”The script ended execution normally. The value of this status depends on the host operating system.

    failure ”The script failed at some point. The value of this status depends on the host operating system.

    warning ”The script exited in a warning state. The value of this status depends on the host operating system.

    integer ”You can return specific integer values that suit your needs.

    variable ”You can return the contents of a variable as the status. This variable does not have to be an integer or number variable.

Script Parameters

When you call a script, you can pass parameters to the script in the command line, as shown in the following example:

 sqlplus  username/password  @set_grade 999999999 2103 'A' 

This is what the set_grade.sql script looks like:

 UPDATE ENROLLED_COURSES SET    course_grade  = upper ('&&3') WHERE  ssn           = &&1 AND    course_number = &&2; 

In this example, 999999999 is referenced in the script as &&1 , and 2103 is referenced in the script as &&2 . The student s grade for course 2103 is referenced as &&3 .

Parameters are referenced by integer values in the order in which they are passed (one of the reasons why it s important to document what parameters are used and in what order they should be passed).

References to parameters are typically made using the ampersand (&) character; this character can be altered using the set define command. A single ampersand instructs SQL to prompt the user for the value of the parameter. Double ampersands (&&) instruct SQL to prompt the user for a value if there is no value already stored for the parameter; variables defined with a single & are undefined immediately after the variable is used. Consequently, if a script is to run without interaction, you should use double ampersands to reference your parameters.

Spooling Output To Files

The spool command is used to control the direction of output to a file. The syntax for the spool command is:

 spool filename[.sql]; 

If no extension is given for the spool file, SQL*Plus assumes that you want the output file to have an extension of .LST.

To stop spooling to a file, use the following command:

 spool off; 

Substitution Variables

A substitution variable is a variable name preceded by one or two ampersands, like the variable used in the following example:

 SELECT count (*) FROM   STUDENTS WHERE  last_name = upper (&LastName); 

When this script is run, SQL*Plus will prompt the user for each undefined substitution variable it encounters.

PL/SQL In Scripts

The use of IF - THEN - ELSE logic, looping structures, and other features can make writing a powerful script much simpler. It s extremely difficult to simulate these logical control structures using only SQL statements.

Previous

Table of Contents

Next