SQLPLUS – versteckte, interessante Schätze

Mit der Zeit sind immer wieder neue Funktionalitäten im SQLPLUS dazu gekommen, die für viele Benutzer sehr interessant sein können, ohne dass diese – bzw. deren Anwendungsmöglichkeiten – genauer behandelt wurden. In diesem Blog habe ich einige dieser Schätze behandelt.

SET FEEDBACK 6 SQL_ID

Die SQL_ID für ein Statement wird immer wieder benötigt um damit in V$ Views wie V$SQL, V$SQLAREA, V§$SQL_PLAN, etc. Informationen auslesen zu können. Damit man nicht immer nach der SQL_ID suchen muss, kann man sich diese seit Oracle 18c in SQLPLUS direkt anzeigen lassen!

SQL> SET feedback 6 SQL_ID
SQL> SELECT * FROM dual; 

D 
- 
X 

SQL_ID: a5ks9fhw2v9s1

SET LINESIZE WINDOW

Startet man SQLPLUS in einem Terminal Fenster, so hat dieses meist irgend eine willkürliche Größe, die von vielen Faktoren wie Fontgröße, Fenstergröße sowie der Auflösung des Bildschirms abhängig ist. Egal welche Einstellungen man bisher mit LINESIZE und PAGESIZE getroffen hat, es war in der Regel nicht optimal.

Ab Oracle 18c kann man man jetzt einfach statt:

SET LINESIZE 200 PAGESIZE 52

nur noch

SET LINSIZE WINDOW

nutzen. SQLPLUS ermittelt die aktuelle Größe des Terminalfensters und stellt intern die optimale LINESIZE und PAGESIZE ein.

Vorsicht Limitation

Startet man SQLPLUS mit einem Script – beispielsweise:

sqlplus scott/tiger@FREE @myreport.sql

wir ein SET LINESIZE WINDOW ignoriert, weil SQLPLUS davon ausgeht, dass es sich um eine Batchverarbeitung handelt und das Ergebnis NICHT im Terminalfenster benötigt wird. Dieses Verhalten wird von Oracle im Bug 29852429 – SET LINESIZE WINDOW COMMAND IS NOT IGNORED IN SCRIPT als „not a Bug“ und somit geplant beschrieben. Der Workaround dazu ist:

sqlplus scott/tiger@FREE
set LINESIZE WINDOW
@myreport.sql

Aufrufen von Scripts, die im gleichen Verzeichnis liegen

Auch schon recht lange möglich ist es, dass man Scripts, die im gleichen Verzeichnis liegen mittels @@ ansteuert. Dies wird seitens Oracle vor allem in den Catalog-Scripts in $ORACLE_HOME/rdbms/admin eingesetzt, macht aber auch für alle anderen Sinn.

Anwendungsbeispiel

Starten eines Scripts das weitere Scripts ausführt.

sqlplus scott/tiger@FREE @$HOME/myscriptdir/myapp1/deploy_schema.sql

im Script deploy_schema.sql werden jetzt weitere Scripts aufgerufen, die sich im Verzeichnis $HOME/myscriptdir/myapp1 befinden – beispielsweise:

@@create_table
@@create_index
@@create_constraints
@@create_views
@@create_packages
...

Das macht das Aufrufen von weiteren Scripts deutlich einfacher.

DBMS_SQL.RETURN_RESULT

Das Package DBMS_SQL gibt es schon seit Oracle 6, sofern man die PL/SQL Option – ja PL/SQL war mit Oracle 6 noch eine Option! – genutzt hat, und ermöglich dynamisches SQL in PL/SQL. Die Möglichkeit von EXECUTE IMMEDIATE im PL/SQL ab Oracle 8i macht das Package DBMS_SQL eigentlich überflüssig. Aus diesem Grund hat Oracle das Package DBMS_SQL über viele Versionen nur geringfügig erweitert. Mit Oracle 12c ist jedoch die Procedure RETURN_RESULT als Erweiterung gekommen und viele haben sich gefragt, wozu man das jetzt braucht. Eine Antwort ist: um sich das Leben in SQLPLUS leichter zu machen!

Schauen wir uns folgendes Beispiel an:

CREATE OR REPLACE PROCEDURE get_my_results (p_id IN NUMBER DEFAULT NULL)
AS
  l_cursor_1 SYS_REFCURSOR;
  l_cursor_2 SYS_REFCURSOR;
BEGIN
  IF p_id IS NOT NULL THEN
     OPEN l_cursor_1 FOR
        SELECT employee_id, last_name, salary
        FROM hr.employees
        WHERE department_id = p_id;

     DBMS_SQL.RETURN_RESULT (l_cursor_1);
     END IF;

  OPEN l_cursor_2 FOR
    SELECT COUNT(*) FROM hr.departments ;

  DBMS_SQL.RETURN_RESULT (l_cursor_2);
END get_my_results;
/

SQL> exec get_my_results (30) 

PL/SQL procedure successfully completed. 

ResultSet #1 

EMPLOYEE_ID LAST_NAME    SALARY 
----------- ----------- ------- 
        114 Raphaely      11000 
        115 Khoo           3100 
        116 Baida          2900 
        117 Tobias         2800 
        118 Himuro         2600 
        119 Colmenares     2500 

6 rows selected. 

ResultSet #2 

 COUNT(*) 
---------- 
        27

Beim Aufruf einer Prozedur kommen Resultate zurück, und das ohne SERVEROUTPUT ON! Ideal um Debugging in den verschiedenen SQL Tools (SQLPLUS, SQL Developer, TOAD,…) durchzuführen.