Gunther Oracle APEX und Datenbank Wiki

Diese Seite ist nicht editierbar. Sie können den Quelltext sehen, jedoch nicht verändern. Kontaktieren Sie den Administrator, wenn Sie glauben, dass hier ein Fehler vorliegt.
{{: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>