SQL Extensions   «Prev 

Modify Column Attributes

Editing Scripts in SQL*Plus Command-Line

You can use a number of SQL*Plus commands to edit the SQL command or PL/SQL block currently stored in the buffer.

Editing the Current Line

The SQL*Plus CHANGE command enables you to edit the current line. Various actions determine which line is the current line:
  1. LIST a given line to make it the current line.
  2. When you LIST or RUN the command in the buffer, the last line of the command becomes the current line. (Note, that using the slash (/) command to run the command in the buffer does not affect the current line.)
  3. If you get an error, the error line automatically becomes the current line.

1) In the previous lesson, we saved a file with a query. This show the results when you execute the query. The BUYER column is so wide that we do not even see the third column in our window. The first column has an ugly heading, as does the third column.
In the previous lesson, we saved a file with a query. This show the results when you execute the query. The BUYER column is so wide that we do not even see the third column in our window. The first column has an ugly heading, as does the third column.

2) To modify the column attributes, edit the file using text editor
To modify the column attributes, edit the file using the text editor

3) The first change is to modify the column heading for the PRODUCT_NAME column. The first line in the file is inserted as seen here. Double quotes around the heading text allows us to use spaces and lower case letters. Notice that the COLUMN command is added before the query. Environment settings must be put in place prior to executing the query. However, once they are changed, the change remains in effect until you leave SQL*Plus.
The first change is to modify the column heading for the PRODUCT_NAME column. The first line in the file is inserted as seen here. Double quotes around the heading text allows us to use spaces and lower case letters. Notice that the COLUMN command is added before the query. Environment settings must be put in place prior to executing the query. However, once they are changed, the change remains in effect until you leave SQL*Plus.

4) The second change to adjust the width of the BUYER column. Notice that we use the column alias name as it is shown in the query. The WORD_WRAP parameter is also added so that if the name is longer than 10 characters, it will wrap to a second line, breaking between words.
The second change to adjust the width of the BUYER column. Notice that we use the column alias name as it is shown in the query. The WORD_WRAP parameter is also added so that if the name is longer than 10 characters, it will wrap to a second line, breaking between words.

5) The final change is to tell SQL*Plus to omit the line at the end of the query stating how many rows were returned. Do this with the SET FEEDBACK OFF command. After completing these changes, exit the text editor by clicking the X in the top right corner.
The final change is to tell SQL*Plus to omit the line at the end of the query stating how many rows were returned. Do this with the SET FEEDBACK OFF command. After completing these changes, exit the text editor by clicking the X in the top right corner.

6) Notepad prompts you to save the changes . Clicking on the Yes button saves the changes to the PR_CUST.sql file
Notepad prompts you to save the changes . Clicking on the Yes button saves the changes to the PR_CUST.sql file.

7) Now that you have returned to SQL*PLUS, execute the SQL*Plus environment commands and the query using the START command.
Now that you have returned to SQL*PLUS, execute the SQL*Plus environment commands and the query using the START command.

8) The results of the query look different now. The column heading has changed, the width of the BUYER is shorter, and you can see part (but not all) of the SALES_DATE column data. There is no record count at the end. All these settings stay in place until we exit SQL*Plus or modify the setting again during our session.
The results of the query look different now. The column heading has changed, the width of the BUYER is shorter, and you can see part (but not all) of the SALES_DATE column data. There is no record count at the end. All these settings stay in place until we exit SQL*Plus or modify the setting again during our session.