Category Archives: SQLPlus

SQLPlus Tutorial

SQLPlus

SQL * Plus is a tool that allows you to interact directly with the Oracle database. SQL * Plus is an Oracle command line utility that allows users to interactively execute SQL and PL/SQL commands. SQL * Plus enables you to manipulate blocks of SQL and PL / SQL formatting the query results, to copy data between tables… Read More »

SQLPlus Whenever Sqlerror

WHENEVER SQLERROR Command Syntax: WHENEVER SQLERROR {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:BindVariable] [COMMIT|ROLLBACK]|CONTINUE [COMMIT|ROLLBACK|NONE]} WHENEVER SQLERROR Command Examples: WHENEVER SQLERROR EXIT SQL.SQLCODE select no_column from dual; WHENEVER SQLERROR EXIT SQL.SQLCODE Begin SELECT NO_COLUMN FROM DUAL; End; /

SQLPlus Whenever Oserror

WHENEVER OSERROR Syntax: WHENEVER OSERROR {EXIT [SUCCESS|FAILURE|n|variable|:BindVariable] [COMMIT|ROLLBACK] |CONTINUE [COMMIT|ROLLBACK|NONE]} WHENEVER OSERROR Examples: WHENEVER OSERROR EXIT START no_such_file

SQLPlus Variable

VARIABLE Command Syntax: VAR[IABLE] [variable [NUMBER|CHAR|CHAR (n [CHAR|BYTE])|NCHAR|NCHAR (n) |VARCHAR2 (n [CHAR|BYTE])|NVARCHAR2 (n)|CLOB|NCLOB|REFCURSOR]] VARIABLE Command Examples: VARIABLE id NUMBER VARIABLE text CHAR (20) VARIABLE var REFCURSOR SET AUTOPRINT ON VARIABLE var REFCURSOR BEGIN OPEN :var FOR SELECT id, name, city, email FROM customers_details_view WHERE id > 5000 ORDER BY name; END; /

SQLPlus Undefine

UNDEFINE Command Syntax: UNDEFINE variable … UNDEFINE Command Example: Undefine a single varibale: UNDEFINE var Undefine two varibales: UNDEFINE var1 var2

SQLPlus Ttitle

TTITLE Command Syntax: TTITLE [printspec [text|variable] …] [ON|OFF] TTITLE Command Example: TTITLE LEFT ‘Monthly Sales’ CENTER ’01 Dec 2011′ RIGHT ‘Page:’ FORMAT 999 SQL.PNO SKIP CENTER ‘Data in sales’ TTITLE OFF

SQLPlus Timing

TIMING Command Syntax: TIMING [START text|SHOW|STOP] TIMING Command Example: TIMING START SQL_TIMER TIMING SHOW TIMING STOP

SQLPlus Store

STORE Command Syntax: STORE SET file[.ext] [CRE[ATE]|REP[LACE]|APP[END]] STORE Command Example: STORE SET test STORE SET test.old APPEND

SQLPlus Startup

STARTUP Command Syntax: STARTUP options | migrate_options Options syntax: [FORCE] [RESTRICT] [PFILE=filename] [QUIET] [ MOUNT [dbname] | [ OPEN [open_options] [dbname] ] | NOMOUNT ] Migrate_options syntax: [PFILE=filename] MIGRATE [QUIET] STARTUP Command Examples: STARTUP STARTUP NOMOUNT STARTUP MOUNT STARTUP OPEN DATABASE STARTUP OPEN RECOVER STARTUP FORCE RESTRICT MOUNT STARTUP PFILE=testparm NOMOUNT STARTUP FORCE RESTRICT PFILE=init.ora OPEN DATABASE

SQLPlus Start

START Command Syntax: START {url|file[.ext] } [arg…] START Command Example: Create a file TEST.sql SELECT id, name, price FROM products WHERE name=’&1′ AND price>&2; SQL> START TEST SAP 5000 or SQL> START HTTP://machine_name.domain:port/TEST.SQL SAP 5000