{{:images:cropped-bergwelt_kopfzeile.jpg?550|Berge bei Oberstdorf}}


===== SQL*Plus Tips und Tricks ===== 

**Erste Version >> 01.2016**

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

  * http://www.pipperr.de/knowhow/sqlplus/sqlplus.html

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 <key><=</key> 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:

<code bash>

# Backspace Taste setzen
stty erase ^H

# SQLPlus starten
sqlplus / as sysdba

-- testen!

</code>

----

==== 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 <key>F7</key> kann ein Fenster mit den letzten Befehlen geöffnet werden:


{{ :dba:sqlplus_f7_key_01.png?300 | 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:
<code plsql>
-- 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;
</code>
Auf den **.** nach dem **&y** achten! Definiert das** Ende** einer SQL*Plus Ersetzungsvariablen.
\\

Oder alternativ mit DB und Server Namen:
<code sql>
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
---- 
</code>

Eine Übersicht über die oft hilfreiche Funktion SYS_CONTEXT findet sich bei Oracle hier: [[http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm|SYS_CONTEXT]]


----

==== & - ampersand  in SQL*Plus Scripten escapen ====

<code sql>

set escape on

-- mit \ escapen

..
and account_status not in ('LOCKED','EXPIRED \& LOCKED')
..

</code>

----

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


<code plsql>
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
</code>

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 [[https://orapowershell.codeplex.com/SourceControl/latest#sql/login.sql|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:
<code sql>
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'

</code>


----

==== 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.

<code sql>
spool log.out
set echo on
-- Befehl
alter index .....;

set echo off
spool off
</code>


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:

<code sql>
DOC 
-------------------------------------------------------------------------------

   Kommentar Text
   Kommentar Text
	
-------------------------------------------------------------------------------
#
</code>



----

==== 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:


<code sql>

-- 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

</code>


----

==== SQL*Plus Cursor ====

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


<code sql>


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;
/

</code>



----

==== 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:
<code sql>
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
.....

</code>



----

==== Error Logging in eine Tabelle in Oracle 11g ====


<code sql>

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

</code>

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 ===


<code sql>

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
           

</code>

=== Nach jeder Trefferzeile einen weiteren Spaltenumbruch einführen ===

<code sql>

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

</code>

=== 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.

<code sql>
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

</code>


----

=== 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.

<code sql>

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

</code>


----

==== HTML Berichte erstellen ====

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

Beispiel:
<code sql>

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

</code>



----

==== Copy Table Funktion ====

<code sql>
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.

</code>