Inhaltsverzeichnis

Berge bei Oberstdorf

SQL*Plus Tips und Tricks

Erste Version » 01.2016

Für eine vertiefte Einführung in SQL*Plus siehe auch:

Und in Slideshare:

[slideshare id=51535888&doc=01-sqlplus-150812090712-lva1-app6892]

12.2 Login.sql Security Verhalten - NUR noch ORACLE_PATH

Mit der DB Version 12.2 wird NICHT mehr bei start von SQL*Plus die login.sql aus dem SQL_PATH bzw. die globale login.sql aufgerufen.

Das automatische Aufrufen beim Start von SQL*Plus ist eigentlich eine Sicherheitslücke, da nicht geprüft wird ob die Daten nicht böswillig verändert wurde.

Soll aber die login.sql explizit verwendet werden, muss die Umgebungsvariable ORACLE_PATH (Windows SQLPATH ) gesetzt werden.

Liegt unter diesem Pfad eine login.sql wird diese dann wieder automatisch ausgeführt.

Beispiel für eine Login.sql ⇒ https://github.com/gpipperr/OraPowerShell/blob/master/Ora_SQLPlus_SQLcL_sql_scripts/login.sql

Der Admin muss selber sicherstellen das diese Datei entsprechend geschützt ist!

Backspace Taste unter der Linux/Unix Bash

Gelegentlich kann unter Linux die Backspace ⇐ Taste in SQL*Plus nicht richtig verwandt werden.

Tritt dieser Fehler auf, muss die Tastatur für das tty, die aktuelle Console, richtig eingestellt werden: 

# Backspace Taste setzen
stty erase ^H
 
# SQLPlus starten
sqlplus / as sysdba
 
-- testen!

Kommandozeilen Buffer unter der Linux/Unix Bash

In Linux ist in SQL*Plus kein echter Kommandozeilen Buffer mit einer Historie implementiert.

wie https://blogs.oracle.com/LetTheSunShineIn/entry/using_the_full_tty_real

Meiner Erfahrung nach ergeben sich aber meist ein paar Nachteile, besonders mit den von Oracle gelieferten sys Skripts bei Updates und ähnlichen.

Die F7 Taste in SQL*Plus unter Windos

Mit der F7 kann ein Fenster mit den letzten Befehlen geöffnet werden:

SQL*Plus F7 Taste

Ersetzungvariablen in SQL*Plus

Tagesdatum im Spool verwenden

Aufgabe:
Bei jeden Aufruf eines Scripts soll das Tagesdatum als Dateiname des Spool verwendet werden.

Beispiel: 

-- setzen
SET termout off
col x new_value y
define y=?
SELECT TO_CHAR(SYSDATE,'YYYYMMDDHH24MISS') x FROM dual;;
SET termout ON
spool &y._spool.LOG
  -- hier das tun was soll
spool off;

Auf den . nach dem &y achten! Definiert das Ende einer SQL*Plus Ersetzungsvariablen.

Oder alternativ mit DB und Server Namen: 

col SPOOL_NAME_COL new_val SPOOL_NAME
 
SELECT ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'.log' 
  AS SPOOL_NAME_COL
FROM dual
/
 
prompt
 
spool &&SPOOL_NAME
------
prompt "logs will be generated into"
spool
----

Eine Übersicht über die oft hilfreiche Funktion SYS_CONTEXT findet sich bei Oracle hier: SYS_CONTEXT

& - ampersand in SQL*Plus Scripten escapen

SET escape ON
 
-- mit \ escapen
 
..
AND account_status NOT IN ('LOCKED','EXPIRED \& LOCKED')
..

Prompt in SQL*Plus mit dem Hostnamen der DB versehen:

SET termout off
DEFINE _EDITOR=vi
col x new_value y
define y=?
SELECT SYS_CONTEXT('USERENV','SERVER_HOST') x FROM dual;
SET sqlprompt "_USER'@'_CONNECT_IDENTIFIER-&y>"
SET termout ON

Alternativ zu „SYS_CONTEXT('USERENV','SERVER_HOST')“ könnte auch „select lower(HOST_NAME) x from v$instance;“ verwendet werden, das darf aber dann nicht jeder User!

siehe auch login.sql

Quote Strings in SQL*Plus:

DB: 10g/11g
Bei dem Erstellen von dynamischen SQL ist es oft problematisch das ' zu maskieren.

Lösung:
Q'<quote_start> ..text with ' …<quote_end>'

Quote Zeichen können z.B. sein:

Beispiel: 

SQL>SELECT Q'[ select * from user_tables where table_name='TEST' ]' FROM dual;
 
Q'+SELECT*FROMUSER_TABLESWHERETABLE_NAME='TEST'+'
---------------------------------------------------
 SELECT * FROM user_tables WHERE TABLE_NAME='TEST'

In SQL*Plus in SQL*Plus Scripten den Befehlt mit ausgeben

Mit dem Setzen der Eigenschaft „echo“ von SQL*Plus in einem SQL*Plus Script wird erreicht, das die SQL Befehle wiederum ausgegeben werden. 

spool log.out
SET echo ON
-- Befehl
ALTER INDEX .....;
 
SET echo off
spool off

Im erzeugten Logfile wird nun das Kommando „alter index ..“ mit angezeit, nicht nur die Meldung „Index wurde erstellt“.

DOC Ausgabe in SQL*Plus

Mit Hilfe des DOC Befehls können größere Kommentar Felder in SQL Scripten verwendet werden.

Beispiel: 

DOC 
-------------------------------------------------------------------------------
 
   Kommentar Text
   Kommentar Text
 
-------------------------------------------------------------------------------
#

DB Edition ermitteln und je nach DB Edition alternatives SQL Script aufrufen ( eine Art if in SQL*Plus .-) )

Idee hinter diesen Code Fragment ist der Aufruf von Installations SQL Scripten je nach Parameter des Anwenders und der DB Edition: 

-- enviroment
-- read first parameter
define INSTALL_PART_QUESTION='&1'
 
prompt
prompt 'Retrieving information about the DB:'
prompt
 
-- which DB version we are using
variable DBEDITION  varchar2(10);
 
SET serveroutput ON
DECLARE
  v_ver    varchar2(100);
  v_compat varchar2(100);
  v_product varchar2(100);
BEGIN
  SELECT product INTO v_product  FROM product_component_version WHERE product LIKE '%atabase%' AND rownum=1;
  dbms_output.put_line('--- '||v_product||'---');
  dbms_utility.db_version(v_ver, v_compat);
  dbms_output.put_line('--- Version of the database : ' || v_ver ||' Compatible: ' || v_compat ||'  ----' );
  IF dbms_utility.is_cluster_database THEN
    dbms_output.put_line('--- This is a Oracle Real Application Cluster DB --  starting from instance ::'||dbms_utility.current_instance);
  ELSE
    dbms_output.put_line('--- This is a Single Instance Oracle Database --');
  END IF;	
  IF instr(LOWER(v_product),'enterprise') > 1 THEN
    :DBEDITION:='EE';
    dbms_output.put_line('--- This is a Enterprise Edition ---');
  ELSE
    :DBEDITION:='SE';
    dbms_output.put_line('--- This is a Standard Edition ---');
  END IF;	
END;
/
 
 
prompt
print	
prompt
 
col SCRIPTPART_COL new_val SCRIPTPART_INSTALL
 
SELECT decode(:DBEDITION
				,'EE'
				,CASE  
				 WHEN UPPER('&&install_option_question')='YES' 
					THEN 'create_option_schema_ee.sql'
					ELSE 'create_schema_ee.sql'
				 END
				,'create_schema_se.sql'
			) AS SCRIPTPART_COL
FROM dual
/
 
prompt
 
-- call the choosen script
@@./&&SCRIPTPART_INSTALL

SQL*Plus Cursor

Verwendung eine Ref Cursors (Cursor auf einen beliebige SQL Abfrage) in SQL*Plus: 

 
variable c_refc refcursor
 
 
BEGIN
    OPEN :c_refc FOR SELECT * FROM dual;
END;
/
 
 
-----------------------
-- Referencing with print will close the cursor
 
print :c_refc 
 
 
 
-----------------------
-- Close with out check
--
BEGIN
    close :c_refc;
END;
/
*
ERROR at line 1:
ORA-01001: invalid cursor
ORA-06512: at line 2
 
 
----------------------
-- Better- Check if Cursor is already opened
 
 
BEGIN
    IF :c_refc%ISOPEN THEN
	close :c_refc;
    ELSE
        dbms_output.put_line('Cursor still closed');
    END IF;
END;
/

Spalten vertical anzeigen

In SQL*Plus ist es leider nicht ganz einfach eine SQL Ausgabe vertical anzuzeigen.

Über den Umweg XML zu verwenden kann aber eine zwei Spaltige Darstellung erreichet werden:

Beispiel: 

SELECT * 
   FROM xmltable('ROWSET/ROW/*' 
			passing xmltype(cursor(SELECT * FROM dba_lobs WHERE UPPER(owner)=UPPER('&&OWNER.') AND UPPER(TABLE_NAME)=UPPER('&&TABLE_NAME.'))) 
			COLUMNS property varchar2(30) path 'node-name(.)'
					, VALUE      varchar2(30) path '.'
		 )
/
 
PROPERTY                       VALUE
------------------------------ ----------------------------
OWNER                          GPI
TABLE_NAME                     LOG_STORAGE_TEST
.....

Error Logging in eine Tabelle in Oracle 11g

SQL>SHOW errorlogging
errorlogging IS OFF
 
SQL>SET errorlogging ON
 
SQL>SHOW errorlogging
errorlogging IS ON TABLE GPI.SPERRORLOG
 
-- Tabelle wird im aktuellen Schema angelegt
 
DESC GPI.SPERRORLOG
 
 
SQL>DESC SPERRORLOG
 Name                 NULL?    Typ
 --------------------- -------- -----------------------
 
 USERNAME                        VARCHAR2(256 CHAR)
 TIMESTAMP                       TIMESTAMP(6)
 SCRIPT                          CLOB
 IDENTIFIER                      VARCHAR2(256 CHAR)
 MESSAGE                         CLOB
 STATEMENT                       CLOB
 
 
--- fehler 
SQL> SELECT * FROM none_exit_tabes;
 
 
--
 
SQL>SELECT COUNT(*) FROM GPI.SPERRORLOG;
 
    COUNT(*)
------------
           1
 
SQL>SET errorlogging off

see http://neeraj-dba.blogspot.de/2012/02/sqlplus-error-logging-in-oracle-11g.html

Berichte formatieren

Mit BREAK und TTITEL kann aus einer einfachen SQL*Plus Abfrage ein „echter“ Bericht erzeugt werden .-).

Einen Titel mit Seiten Nummer erzeugen

SQL>help TTITLE
 
 TTITLE
 ------
 
 Places AND formats a title at the top OF each report page.
 Enter TTITLE WITH no clause TO list its CURRENT definition.
 
 The OLD form OF TTITLE IS used IF ONLY a single word OR
 a string IN quotes follows the TTITLE command.
 
 TTI[TLE] [printspec [text|variable] ...] | [OFF|ON]
 
 WHERE printspec represents one OR more OF the following clauses:
 
     COL n          LE[FT]        BOLD
     S[KIP] [n]     CE[NTER]      FORMAT text
     TAB n          R[IGHT]
 
 
 
TTITLE COL 15  FORMAT 99  'My Test Page Nr.:' SQL.PNO
 
SELECT 1 FROM dual GROUP BY cube(1,1)
/
 
TTITLE OFF
 
-----
 
 
              My Test Page Nr.: 1
           1
------------
           1
           1
           1
           1

Nach jeder Trefferzeile einen weiteren Spaltenumbruch einführen

BREAK ON ROW SKIP 2
 
SELECT 1 FROM dual GROUP BY cube(1,1)
/
 
CLEAR BREAKS
 
 
              My Test Page Nr.:  1
           1
------------
           1
 
 
           1
 
 
           1
 
 
           1

Summe eines gesamten Berichtes anzeigen

Mit „BREAK ON report“ und „COMPUTE SUM OF <column> ON report“ kann eine Summen Angabe am Ende des Berichts erzeugt werden. 

BREAK ON report
COMPUTE SUM OF counter ON report
 
SELECT 1 AS counter FROM dual GROUP BY cube(1,1)
/
 
     COUNTER
------------
           1
           1
           1
           1
------------
           4

Summe der Zeilen eines Berichts anzeigen beim Gruppenumbruch anzeigen

Mit „BREAK ON <break_column>“ und „COMPUTE SUM OF <column> ON break_column“ kann eine Summen nach jeder Änderung der Gruppenspalte erstellt werden. 

BREAK ON id
COMPUTE SUM OF wert ON id;
 
 
 
SELECT level-1 AS id ,10 AS wert 
   FROM dual 
CONNECT BY level < 4
/
 
          ID         WERT
------------ ------------
           0           10
************ ------------
SUM                    10
           1           10
************ ------------
SUM                    10
           2           10
************ ------------
SUM                    10
 
 
CLEAR BREAK
CLEAR COMPUTES

HTML Berichte erstellen

Mit dem HTML Markup können die Berichte aus SQL*Plus mit HTML erzeugt werden.

Beispiel: 

col SPOOL_NAME_COL new_val SPOOL_NAME
 
SELECT REPLACE(ora_database_name||'_'||SYS_CONTEXT('USERENV','HOST')||'_'||to_char(sysdate,'dd_mm_yyyy_hh24_mi')||'_report.html','\','_')   AS SPOOL_NAME_COL
--' resolve syntax highlight bug FROM my editer .-(
FROM dual
/
 
-- define the head Section of the report
 
SET MARKUP HTML ON SPOOL ON PREFORMAT OFF ENTMAP ON -
HEAD "<TITLE>SQL Usage Report</TITLE>               -
<STYLE type='text/css'>                             -
<!-- BODY {background: #FFFFFF}                     -
     span.findings { color:red } -->                -
</STYLE>"                                           -
TABLE "WIDTH='90%' BORDER='1'"
 
 
-- start spool
--
spool &&SPOOL_NAME
 
 
-- to avoid escaping of HTML syntax elements with ENTMAP OFF
--
COLUMN sql_text format a150 heading "SQL|Text" WORD_WRAPPED ENTMAP OFF
 
 
--
-- do something
--
 
--close html page
--
SET markup html off
 
-- spool off
--
spool off
 
 
-- works only in a ms windows environment
-- auto start of the result in a browser window
host &&SPOOL_NAME

Copy Table Funktion

SQL>copy TABLE
 
Verwendg.: COPY FROM <DB> TO <DB> <Opt> <Tab.> { (<Spalt>) } USING <SEL>
  <db>   : Datenbankzeichenfolge, z.B. hr/your_password@d:chicago-mktg
  <Opt>  : EINES der Schl³sselw÷rter: APPEND, CREATE, INSERT oder REPLACE
  <Tab.>:  Name der Zieltabelle
  <Spalt>: eine durch Kommata getrennte Liste der Zielspalten-Aliasnamen
  <SEL>  : eine beliebige, g³ltige SQL SELECT-Anweisung
Eine fehlende FROM- o. TO-Klausel verwendet die akt. SQL*Plus-Verbindung.