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 16: | Line 16: | ||
===Writing out "LONGs"=== | ===Writing out "LONGs"=== | ||
| - | To increase the length of a LONG displayed in SQL Plus: | + | To increase the length of a LONG column displayed in SQL Plus: |
<pre> | <pre> | ||
| 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 41: | Line 42: | ||
set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> " | set sqlprompt "_USER'@'_CONNECT_IDENTIFIER> " | ||
</pre> | </pre> | ||
| + | |||
| + | ===Editing Your SQL=== | ||
| + | Use your favourite text editor to edit files - for instance - TextPad - by altering the SQL Plus system variable: | ||
| + | |||
| + | <pre> | ||
| + | define _editor=C:\Progra~1\TextPa~1\Textpad.exe | ||
| + | </pre> | ||
| + | |||
| + | ===Show Output from PL/SQL=== | ||
| + | To see output from PL/SQL (and see as much as you can): | ||
| + | |||
| + | <pre> | ||
| + | /* Pre 10gR2 client */ | ||
| + | set serveroutput on size 1000000 | ||
| + | </pre> | ||
| + | |||
| + | <pre> | ||
| + | /* Post 10gR2 client */ | ||
| + | set serveroutput on size unlimited | ||
| + | </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]] | ||
