Table of Contents Basics Connexion preferences HelloWorld Connection Parameters Enable terminal output Transaction Procedure handling Misc

sqlplus :

• keywords are case_insentive
• there is no statement delimiter (;)
• call from OS: sqlplus <USER> @<SQL_SCRIPT_DIR>/<SQL_SCRIPT_NAME> 2>&1 >> <LOG_FILE>

Stored in login.sql, located in $ORACLE_HOME/sqlplus/admin.

define _editor=vi
set serveroutput on size 1000000
set trimspool on
set long 5000
set linesize 100
set pagesize 9999
column plan_plus_exp format a80
set sqlprompt '&_user.@&_connect_identifier.> '

/* Display results page by page */
SET NEWPAGE 0

/* Query parameter */
/* 0: Display ALL results of a query */
SET PAGESIZE 0
/* OFF: Supress sending column header of a query to the output */ */
SET HEADING OFF
/* OFF: Supress sending number of records affected to the output */
SET FEED OFF

/* Command parameter */
/* OFF: Supress sending command to the output */
SET ECHO OFF
/* OFF: Supress sending output of @commands to the output */ 
SET TERM OFF
/* OFF: Suppress sending output of DBMS_OUTPUT.PUT_LINE command to the output */
SET SERVEROUTPUT ON
      
DECLARE
    -- 
   sentence VARCHAR2(30) := 'Hello, world!';
   --
BEGIN
   --
   dbms_output.put_line(sentence);
   --   
EXCEPTION
   WHEN OTHERS THEN
      RAISE;
   --
END;
/

EXIT

Overview:

• to a schema: CONNECT /
• to an OS-based user: CONNECT /

Connect as SYSTEM, with SYSDBA privileges, while logged as root:

sqlplus / AS sysdba 
+ identify as SYSTEM

Overview:

• set value : SET =
• show value of a parameter: SHOW

SET SERVEROUTPUT ON
SET SERVEROUTPUT ON SIZE 1000000
SET TERMOUT ON

Allow blank lines in DML SET SQLBLANKLINES ON

At each statement (SQL or PL block):

• [default] COMMIT manually SET AUTOCOMMIT OFF
• COMMIT at each statement SET AUTOCOMMIT ON

At end of script execution, if using plain EXIT (no parameter, like in EXIT 0 ):

• default mode ( == SET EXITCOMMIT ON ):

 * if script ends successfully, COMMIT 
 * if script ends unsuccessfully, ROLLBACK.

• conservative mode ( == SET COMMITONEXIT OFF ):

 * if script ends successfully, ROLLBACK
 * if script ends unsuccessfully, ROLLBACK.

Subtleties here

Recompile:

• all: ALTER PACKAGE COMPILE (DEBUG);
• specification: ALTER PACKAGE COMPILE (DEBUG) SPECIFICATION ;
• body: ALTER PACKAGE COMPILE (DEBUG) BODY;

Execute:

• procedure (one-liner): EXECUTE (<PACKAGE_NAME>.)<PROCEDURE_NAME>(<PARAMETERS);

From command line

• Unix: echo <COMMAND> | sqlplus <CONNECT_STRING>

A handful:

• clear screen cl scr
• display executed queries: SET ECHO + launch sqlplus with -s option sqlplus
• get OS command prompt: host / exit