Through SQL*Plus, you can store one or more commands in a file called a command file. After creating a command file, you can retrieve, edit, and run it. The INPUT command is used to enter SQL*Plus commands into the buffer, then you will save the buffer contents. Let's write some simple interactive commands, then we will progress to formatting query results.

The following command file will direct the user to supply a report title and store the input in the variable MYTITLE. The PROMPT and ACCEPT commands allow you to ask for and store a value from the user. First, you may want to clear the buffer.

You should receive a buffer cleared message.

Press the enter key twice here to tell ORACLE you have finished entering commands. Then use the following command to save the report to a file named PROMPTDEPT.

You will receive a Created file promptdept message. Now, run the command file.

Enter a title up to 30 characters long. ç This is the message that is displayed for the user.

Title: ç This is the prompt at the beginning of the line, waiting for user input.

Department Report as of 11/4/97 ç This is what I entered for the title of the report, followed by enter.

The following report is displayed.

The TTITLE command sets the top title for your report. CENTER specifies the position of the top title.

Before continuing, turn the TTITLE command you entered in the command file off as shown below:

Let's change the format of this report by changing the column headings. First retrieve the file.

We want to add lines after line 3 that change column headings, so we LIST line 3. Then type the INPUT command followed by enters, and ORACLE allows us to add as many lines as we need.

Hit the enter key twice to let ORACLE know you are finished adding lines. Now, LIST the command file.

Now you are ready to write over the old file and replace it with this new version.

You will receive a message that ORACLE wrote the file.

Run the command file. Enter any title you prefer for your report. I chose "Department Report as of 11/4/97".

This is what the report looks like now.

Don't forget to turn off the TTITLE command. You also need to clear columns.

Let's try creating a query that asks the user for input. Suppose we want to allow the user to find the information about any employee in the EMPLOYEE table by entering the employee number. The following commands will do this. First, clear the buffer.

Hit enter twice to let ORACLE know you have finished and then save the command file.

Run the file.

Enter a valid 3 digit employee number. ç This message is displayed to prompt the user for input.

101 ç I chose to enter 101. You may use any employee number in the EMPLOYEE table.

ORACLE shows the old and new values of empno, then displays the report.

Since we specified that our variable was a number, other values are not accepted. Try entering a value that is not a number. 

John ç I entered "John".

"John" is not a valid number ç ORACLE tells me my input is not valid.

100 ç I try again and enter a number this time. The following report is displayed.

Let's change this query so that the salary is formatted with a dollar sign, and with a comma after the thousands. Retrieve the command file.

We want to add a line after line 2, so LIST line 2, then use the INPUT command.

Hit enter twice to show ORACLE you are finished adding lines. Then LIST the new command.

Save the changed file, then run it.

Clear columns before continuing.

Last revised 11/5/1997 by J. Warren, Kennesaw State University

Department of Computer Science and Information Systems