SQL Plus
From TekiWiki
This whole wiki has now moved to http://www.springboardsolutions.com/wiki
Go to this page SQL Plus
| Line 1: | Line 1: | ||
| - | SQL Plus is [[Oracle]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, SQL Developer | + | SQL*Plus is the [[Oracle Database]]'s supplied interactive SQL client. In later versions of Oracle, for interactive SQL queries, [[SQL Developer]] has a better GUI. However, to run scripts and run SQL as part of batch scripts, SQL Plus is your man. |
==Setting Up SQL Plus== | ==Setting Up SQL Plus== | ||
| Line 5: | Line 5: | ||
SQL Plus has set of options that can be used to tailor the output. You can set these up to allow you to display things on screen or to create batch files. | SQL Plus has set of options that can be used to tailor the output. You can set these up to allow you to display things on screen or to create batch files. | ||
| - | ===Create a file with only data=== | + | ===Create a file with only data from your SQL=== |
To remove all headings, feedback etc. from the output. If you have just started SQL Plus: | To remove all headings, feedback etc. from the output. If you have just started SQL Plus: | ||
| Line 24: | Line 24: | ||
</pre> | </pre> | ||
| + | The difference between long and maxdata (in the above example, 60,000-55,000=5,000) gives the space for the other columns in the rest of the row | ||
===Changing the Prompt=== | ===Changing the Prompt=== | ||
| Line 43: | Line 44: | ||
===Editing Your SQL=== | ===Editing Your SQL=== | ||
| - | Use your favourite text editor to edit files - for instance - TextPad: | + | Use your favourite text editor to edit files - for instance - TextPad - by altering the SQL Plus system variable: |
<pre> | <pre> | ||
| Line 61: | Line 62: | ||
set serveroutput on size unlimited | set serveroutput on size unlimited | ||
</pre> | </pre> | ||
| + | |||
| + | ===Set a Spool Filename with Database Name and Timestamp=== | ||
| + | |||
| + | Set a SQL*Plus substitution variable by a database query and use this as part of the name of the file: | ||
| + | |||
| + | <pre> | ||
| + | column colFileSuffix new_value FileSuffix | ||
| + | |||
| + | select | ||
| + | name||'_'||to_char(sysdate,'YYYY-MM-DD-HH24-MI-SS') colFileSuffix | ||
| + | from v$database; | ||
| + | |||
| + | spool ReportFile_&&FileSuffix | ||
| + | ...commands... | ||
| + | spool off | ||
| + | </pre> | ||
| + | |||
| + | |||
| + | Articles about SQL: | ||
| + | |||
| + | * [[SQL Plus]] | ||
| + | * [[Creating Comma Seperated Lists]] | ||
| + | * [[Sampling a Table]] | ||
| + | * [[Tree Queries]] | ||
| + | |||
| + | * [[Example Country Table]] | ||
| + | * [[SQL Developer]] | ||
