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:

SELECT * FROM dual;

or you can execute:

SELECT * FROM dual

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

figs/sqp2_0308.gif

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:

SET MARKUP HTML PREFORMAT ON

 

Figure 3-9. Displaying results as an HTML table

figs/sqp2_0309.gif

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



and

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

figs/sqp2_0310.gif

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.


     



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
Simiral book on Amazon

Flylib.com © 2008-2017.
If you may any questions please contact us: flylib@qtcs.net