Custom Search
www.rocket99.com : Technical Guides Sybase Oracle UNIX Javascript


Technical Guides
Sybase
Oracle
UNIX
Javascript




Of Interest

Business Intelligence and Analytics



Oracle Training





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!











Oracle : Related Topics

Oracle : UNIX : Environment Settings for Oracle
Oracle : UNIX : SQL*Net
Oracle : UNIX : Import and Export
Oracle : UNIX : Initialization File
Oracle : UNIX : SQL Loader

Sybase Web Site
Sybase iAnywhere Mobile Web Site
Oracle Enterprise Web Site



Get the latest Rocket99 news and tech tips via






Site Index About this Guide to Sybase, Oracle, and UNIX Contact Us Advertise on this site




Copyright © 2016 Stoltenbar Inc All Rights Reserved.