Vordefinierte Variablen in SQLPlus verwenden - Teil II

Im vorigen Blog-Eintrag habe ich bereits einige vordefinierte SQL*Plus Variablen erklärt und gezeigt, wie man diese gut verwenden kann. Nun geht es um eine weitere Variable sowie um die Optimierung des SQL-Prompts, um sich bestmöglich zurechtztufinden. Mein Kollege Daniel Wenzel hat mich noch mal auf die Variable "_DATE" hingewiesen. Wenn dieser Variablen kein Wert zugewiesen ist, dann setzt SQL*Plus automatisch das aktuelle Datum/Zeit ein. Das kann an mehreren Stellen hilfreich sein. Man kann es z.B. wieder für den SQL-Prompt verwenden:

C:\Users\marco.mischke>findstr prompt c:\Oracle\client19\sqlplus\admin\glogin.sql
set sqlprompt "_DATE > "

C:\Users\marco.mischke>sqlplus mpm/*******@db

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 13:51:08 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 13 2024 13:50:46 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

13.02.24 >

 Na gut, so hilfreich ist das eventuell doch nicht. Doch halt, der angezeigte Wert wird eben mit dem in der Umgebung definierten Datumsformat in Text umgewandelt. Also muss man lediglich NLS_DATE_FORMAT in der Ungebung setzen, um einen vernünftigen Zeitstempel zu erhalten.

C:\Users\marco.mischke>set NLS_DATE_FORMAT=dd.mm.yyyy hh24:mi:ss

C:\Users\marco.mischke>sqlplus mpm/*******@db

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 13:53:11 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 13 2024 13:51:09 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

13.02.2024 13:53:13 > select * from dual;

D
-
X

13.02.2024 13:53:33 >

Aber man kann die Variable auch anders verwenden. Für SQL macht es wenig Sinn, denn da gibt es ja SYSDATE und Co. um den aktuellen Zeitstempel zu ermitteln. Aber beispielsweise für Logfiles kann das nützlich sein.

C:\Users\marco.mischke>set NLS_DATE_FORMAT=yyyymmdd_hh24miss

C:\Users\marco.mischke>sqlplus mpm/********@db

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:03:43 2024
Version 19.15.0.0.0

Copyright (c) 1982, 2021, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 13 2024 14:03:15 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.21.0.0.0

SQL> spool sqllog_&date..log
Enter value for date:
SQL> spool sqllog_&_DATE..log
SQL> select * from dual;

D
-
X

SQL> spool off
SQL> host "dir sqllog*.log"
 Datenträger in Laufwerk C: ist Windows
 Volumeseriennummer: AA0A-FA2B

 Verzeichnis von C:\Users\marco.mischke

13.02.2024  14:04               292 sqllog_20240213_140411.log
               1 Datei(en),            292 Bytes
               0 Verzeichnis(se), 65.902.559.232 Bytes frei

Hier wurde also eine Datei mit dem passenden Zeitstempel erstellt.

Ein weiteres Thema im ersten Blog-Eintrag war der Connect Identifier für den SQL-Prompt und die Tatsache, dass dieser zum einen recht lang sein kann und zum anderen man gerade im Multitenant Umfeld keine optimale Darstellung erhält. Hier kann man aber mit anderen Kniffen des SQL*Plus Abhilfe schaffen. Zur Erinnerung, mit der Variable _CONNECT_IDENTIFIER könnte man den Prompt so gestalten:

[oracle@vm160 ~]$ grep sqlprompt $ORACLE_HOME/sqlplus/admin/glogin.sql
set sqlprompt "_USER'['_CONNECT_IDENTIFIER]> "
[oracle@vm160 ~]$ sqlplus hr/hr@vm160.support.robotron.de:1521/pdb1.support.robotron.de

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:15:54 2024
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 13 2024 14:15:01 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

HR[vm160.support.robotron.de:1521/pdb1.support.robotron.de]>

Damit ist die halbe Zeile weg. Verwendet man aber eine Query in der "glogin.sql", um eine Variable mit einem Wert zu belegen, kann man diesen Wert dann für den Prompt verwenden und erhält so einen deutlichen kürzeren und prägnanteren Text:

[oracle@vm160 ~]$ grep -ve '^--' $ORACLE_HOME/sqlplus/admin/glogin.sql

define gname=idle
column global_name new_value gname
set heading off
set termout off
col global_name noprint
select upper(sys_context ('userenv', 'instance_name') || ':' || sys_context('userenv', 'con_name')) global_name from dual;
set sqlprompt '_user @ &gname:>'
set heading on
set termout on

[oracle@vm160 ~]$ sqlplus hr/hr@vm160.support.robotron.de:1521/pdb1.support.robotron.de

SQL*Plus: Release 19.0.0.0.0 - Production on Tue Feb 13 14:20:17 2024
Version 19.18.0.0.0

Copyright (c) 1982, 2022, Oracle.  All rights reserved.

Last Successful login time: Tue Feb 13 2024 14:15:54 +01:00

Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.18.0.0.0

HR @ ORCL19:PDB1:>

Wir haben also Nutzernamen, Namen der CDB und auch der PDB in einem kurzen Text untergebracht. Natürlich funktioniert das nur, wenn die Datenbank auch gestartet ist. Daher wird die Variable mit "idle" vorbelegt, so dass im Falle eines Falles zumindest ein sinnvoller Wert im Prompt steht. 

Bleibt am Ende nur eines übrig: Viel Freude beim Ausprobieren und Experimentieren.