Running SQL Queries

Executing a SQL query or statement is as simple as typing it into the text box in the i SQL*Plus Workspace and pressing the Execute button. When executing a single SQL statement, you do not need to worry about terminating your statement with any special character such as the semicolon. For example, you can execute:


or you can execute:


Any output from SQL statements that you execute will be displayed below the Execute button.

3.3.1 Paging Through Results

When a SQL statement brings back a large number of rows, i SQL*Plus displays results a page at a time. The number of rows displayed per "page" is determined by the PAGESIZE setting and defaults to 24. i SQL*Plus places a page break in between successive queries. Figure 3-8 shows two queries executed with one click of the Execute button. You can see that i SQL*Plus has displayed the result of the first query and has followed that result with a Next Page button. Click that button to see the next page with the result from the second query.

Figure 3-8. Paging through query results


One thing worth noticing about Figure 3-8 is that the first of the two statements was terminated with a semicolon. When executing multiple SQL statements in one go, you must terminate all but the final one.

Use SET PAGESIZE n to specify a page size other than the default of 24 rows. Use SET PAGESIZE 0 to turn off pagination entirely, causing all rows returned by a query to be displayed in one potentially very long page.


3.3.2 Getting Output as Plain Text

By default, i SQL*Plus displays query results in the form of an HTML table, as shown in Figure 3-9. While visually pleasing, such a table may get in the way when you want to copy and paste query results into a text document. If you'd rather see query output as preformatted HTML, issue the following command:



Figure 3-9. Displaying results as an HTML table


Output from queries executed after issuing this SET MARKUP command will be formatted using the HTML


tags, as shown in Figure 3-10. Use the command SET MARKUP HTML PREFORMAT OFF to go back to using HTML tables.

Figure 3-10. Displaying results as preformatted text


For those who are strongly GUI-oriented, i SQL*Plus provides a Script Formatting preferences page from which you can toggle the PREFORMAT setting using a radio button. Click on the Preferences button next to the Logout button, look on the left side of the resulting page under the heading System Configuration, click on Script Formatting, and scroll way down to the Preformatted Output radio buttons . Choose whichever option you prefer, scroll to the bottom of the page, and click the Apply button. Click the Workspace tab to return to the workspace page.

Any change you make to the PREFORMAT option is good only for the duration of your i SQL*Plus session. Chapter 14 talks more about customizing your SQL*Plus environment and shows some ways to make such customizations permanent or, in the case of i SQL*Plus, at least easy to repeat.


Introduction to SQL*Plus

Command-Line SQL*Plus

Browser-Based SQL*Plus

A Lightning SQL Tutorial

Generating Reports with SQL*Plus

Creating HTML Reports

Advanced Reports

Writing SQL*Plus Scripts

Extracting and Loading Data

Exploring Your Database

Advanced Scripting

Tuning and Timing

The Product User Profile

Customizing Your SQL*Plus Environment

Appendix A. SQL*Plus Command Reference

Appendix B. SQL*Plus Format Elements

Oracle SQL Plus The Definitive Guide, 2nd Edition
Oracle SQL*Plus: The Definitive Guide (Definitive Guides)
ISBN: 0596007469
EAN: 2147483647
Year: N/A
Pages: 151 © 2008-2020.
If you may any questions please contact us: