Business Intelligence and Analytics
SQL-Plus is a query / command line utility which has some powerful formatting
; 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 */
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.
SQL Plus was originally a reporting utility. Unfortunately, it is the only
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:
select invoice_id || '|' ||
invoice_dt || '|' ||
order by invoice_id
SQLPLUS allows dynamic variable substitution, from the command line.
Here's an example:
select count(1) from invoices where region=&1 ;
-- 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