Gunther Oracle APEX und Datenbank Wiki

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]

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.

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

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.