{{: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 <= 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:
{{ :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:
-- 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: [[http://docs.oracle.com/cd/E11882_01/server.112/e26088/functions184.htm|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 [[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' ..text with ' ...'
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 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 " und "COMPUTE SUM OF 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 "SQL Usage Report -
" -
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 TO { () } USING
: Datenbankzeichenfolge, z.B. hr/your_password@d:chicago-mktg
: EINES der Schl³sselw÷rter: APPEND, CREATE, INSERT oder REPLACE
: Name der Zieltabelle
: eine durch Kommata getrennte Liste der Zielspalten-Aliasnamen
: eine beliebige, g³ltige SQL SELECT-Anweisung
Eine fehlende FROM- o. TO-Klausel verwendet die akt. SQL*Plus-Verbindung.