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]
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!
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!
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.
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
SET escape ON -- mit \ escapen .. AND account_status NOT IN ('LOCKED','EXPIRED \& LOCKED') ..
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
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'
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“.
Mit Hilfe des DOC Befehls können größere Kommentar Felder in SQL Scripten verwendet werden.
Beispiel:
DOC ------------------------------------------------------------------------------- Kommentar Text Kommentar Text ------------------------------------------------------------------------------- #
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
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; /
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 .....
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
Mit BREAK und TTITEL kann aus einer einfachen SQL*Plus Abfrage ein „echter“ Bericht erzeugt werden .-).
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
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
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
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
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
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.