Of Interest
Be responsible for your future
Enter the USA legally!
Visa, Green Card, Citizenship, Passport
Consultation Services
|
Oracle
»
UNIX
»
Commands
»
Using SQL-Plus
SQL-Plus is a query / command line utility which has some powerful formatting
capabilities.
Getting Started
; Command line terminator
/ Execute the current batch of commands
SET SERVEROUTPUT ON Allow messages from PL-SQL to be displayed
SHOW ERRORS Show errors from last batch
EDIT Run editor, and load buffer
CLEAR BUFFER Clear buffer commands
& Prompt for value
@ Run commands in @filename
/**** Examples ****/
/* prompt for process id, and kill */
alter system kill session '&Victim'
/
/* run commands in tables.sql */
@tables.sql
/
Setting the editor in sql-plus allows you to type "ed" and alter your SQL,
without having to re-type. For windows, set the editor to notepad.exe.
At the SQL Plus prompt, type this command to set the editor.
define _editor=vi
SQL Plus was originally a reporting utility. Unfortunately, it is the only
tool provided
by Oracle for generating flat files. To remove the 'friendly' formatting, here
are the commands required:
set echo off
set newpage 0
set pagesize 0
set space 0
set feedback off
set trimspool on
set heading off
set linesize 555
-- Then, to create a flat file, with pipe delimiters:
spool invoices.dat;
select invoice_id || '|' ||
invoice_dt || '|' ||
total_amt
from invoices
order by invoice_id
;
spool off;
SQLPLUS allows dynamic variable substitution, from the command line.
Here's an example:
select count(1) from invoices where region=&1 ;
exit
-- Then, to run the SQL with the parm CANADA ..
sqlplus scott/tiger@myserver @test1.sql CANADA
-- Try running the SQL without that last parm - you will be prompted to key in the variable!
|
|
|
Get the latest Rocket99 news and tech tips via
|
