SQL*PLUS

How do I…

2.1 Change the format of date fields returned in SQL*Plus?

2.2 Create and use SQL*Plus command files?

2.3 Save query results and SQL statements to a file?

2.4 View and modify SQL*Plus system variables?

2.5 Prompt users for substitution variables?

2.6 Pass parameters to a SQL*Plus script?

2.7 Use SQL*Plus as a report writer?

2.8 Use SQL*Plus to write repetitive scripts?

SQL*Plus is one of the most powerful tools used in developing applications for the Oracle database. SQL*Plus provides the most direct access to Oracle. Although it might seem that SQL*Plus is not user-friendly like graphical query tools, it provides a great deal of flexibility and runs on all platforms supporting Oracle. Unlike most graphical query tools, you can use SQL*Plus to manipulate data and create database objects.

SQL*Plus plays an important role in application development. It is a powerful prototyping tool that you can use to develop and test SQL statements before integrating them into your application. A working knowledge of SQL*Plus is strongly recommended for any developer using Oracle. In this chapter, you will learn how to make SQL*Plus an integral part of your development process. You will see examples of how SQL*Plus can help you develop Oracle applications efficiently.

2.1 Change the Format of Date Fields Returned in SQL*Plus
Date values are stored in the database with the year, month, day, year, hour, minute, and second. The value of a date column can be presented in a variety of formats using date functions. Also, time can be returned correctly for any time zone. If you query a date value without using a date function, the time portion of the date will not display and the format will be the default date format. This How-To presents the suite of date and time formatting returned in SQL*Plus.

2.2 Create and Use SQL*Plus Command Files
You can use SQL*Plus for many tasks, and most tasks consist of more than one step. SQL*Plus enables you to save a series of SQL, PL/SQL, and SQL*Plus commands in a file and run them as a single statement. SQL*Plus command files can be nested to create a complex sequence of events. This How-To explores how to create and use SQL*Plus command files.

2.3 Save Query Results and SQL Statements to a File
The more you work with Oracle, the easier it becomes to develop powerful SQL statements. But how can you avoid retyping the same SQL statement every time you need it? SQL*Plus enables you to save SQL statements and query results to files so they are available the next time you need them. This output can be edited, printed, or merged into a word processing document. This How-To guides you through the task of saving SQL statements and query results to files.

2.4 View and Modify SQL*Plus System Variables
Within SQL*Plus, system variables control the behavior of the SQL*Plus environment. In many ways, changing system variables is like executing commands. Changing the values of system variables changes the way SQL*Plus behaves when executing statements and retrieving records. In this How-To, you will learn to view and modify SQL*Plus system variables.

2.5 Prompt Users for Substitution Variables
User-defined variables give SQL*Plus a new level of flexibility. Developers can create variables that can be defined in SQL scripts or accepted from the user. This How-To explores creating user-defined variables in SQL*Plus and prompting users for values during runtime.

2.6 Pass Parameters to a SQL*Plus Script
As explained in How-To 1.5, a SQL*Plus script can contain substitution variables that the user enters at runtime. Another technique for increased flexibility is for parameters to be passed when calling a script. This How-To explores passing parameters to a SQL*Plus script during runtime.

2.7 Use SQL*Plus as a Report Writer
SQL*Plus commands and system variables enable you to format a report within SQL*Plus. You can create titles, change column headings, break on column changes, and even calculate totals. By using the formatting capabilities of SQL*Plus, you can create reports quickly and have greater control over the output of your queries. This How-To takes you through the different steps of formatting a report in SQL*Plus.

2.8 Use SQL*Plus to Write Repetitive Scripts
Almost everything you need to know about your Oracle database can be queried through SQL*Plus. Because SQL*Plus can write query output to files and command files can be executed by SQL*Plus, SQL*Plus can be used to create SQL statements. This How-To explores using queries to create SQL statements, which is the foundation for many other techniques discussed in the book.