SQL and PL/SQL unleashed

SQL and PL/SQL unleashed ….
Neuheiten bei Oracle 11g und Oracle 12c im Bereich SQL und PL/SQL
Johannes Gritsch
Datenbanken sind unsere Welt
www.dbmasters.at
Themenübersicht
●
●
●
●
●
●
●
Neue Scheduler Job Typen „SQL_SCRIPT“ und „BACKUP_SCRIPT“
SQL Row Limit: PERCENT und TIES
WITH-Klausel mit Funktionen
Alert.log per SQL lesen
DBMS_UTILITY.EXPAND_SQL_TEXT: Hilfestellung beim Tunen
UTL_CALL_STACK: Nicht nur Hilfestellung beim Debuggen
DBMS_SCHEDULER: automatische Benachrichtigung per mail
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Neuer Job Typ „SQL_SCRIPT“ im Scheduler
● Startet eine SQL*Plus Session
● Führt nicht nur SQL-Statements sondern auch SQL*Plus
Kommandos aus
● Input kommt aus einem VARCHAR2 Feld
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
SQL*Plus Script: Text
● Script-Text hinterlegen
DECLARE
v_sql_script VARCHAR2(32767) := q'[
WHENEVER SQLERROR EXIT
SET SERVEROUTPUT ON
SET LINESIZE 200 PAGES 2000
SPOOL my_sql_script.txt
BEGIN dbms_output.put_line('Hello World'); END;
/
SPOOL OFF
]';
BEGIN
…
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
SQL*Plus Script: Credentials anlegen
● Credentials anlegen
DBMS_credential.create_credential(
credential_Name => 'OS_CREDENTIAL',
username => 'oracle',
password => 'nichtgeheim',
database_role => 'SYSDBA',
nur, wenn wirklich notwendig
enabled => TRUE);
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
SQL*Plus Script: Job erstellen
● Job erstellen:
DBMS_scheduler.create_job (
job_Name => 'MY_SQLPLUS_JOB',
job_Type => 'SQL_SCRIPT',
job_action => v_sql_script,
number_of_arguments => 0,
credential_name => 'OS_CREDENTIAL',
enabled => TRUE);
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
RMAN Script
● Es gibt auch einen neuen Typ „BACKUP_SCRIPT“, der eine
RMAN-Session führt
● Damit können RMAN-Scripts aus SQL oder PL/SQL ausgeführt
werden
● Hat im Zusammenspiel mit CDB noch „Issues“ – ist daher noch
nicht endgültig fertig
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
ROW LIMIT
●
●
●
●
SQL 2011 Standard
Eingeführt mit 12c
Bessere Performance als Select mit ROWNUM von inline View
Leichter lesbar
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
SQL Row Limit
Neue Syntax für begrenzte Zeilenanzahl
Alte Syntax:
SELECT *
FROM (SELECT * FROM employees ORDER BY employee_id)
WHERE rownum <=5;
Neue Syntax (SQL 2011 Standard):
die ersten 5 Records:
SELECT * FROM employees ORDER BY employee_id
FETCH FIRST 5 ROWS ONLY;
die nächsten 5 Records:
SELECT * FROM employees ORDER BY employee_id
OFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
SQL Row Limit mit Prozent
die ersten 5 Prozent:
SELECT * FROM employees ORDER BY employee_id
FETCH FIRST 5 PERCENT ROWS ONLY;
die nächsten 5 Prozent:
SELECT * FROM employees ORDER BY employee_id
OFFSET
5 ROWS (SQL
FETCH
NEXTStandard):
5 PERCENT ROWS ONLY;
Neue Syntax
2011
die ersten 5 Prozent unter Berücksichtigung gleicher Werte:
SELECT * FROM employees ORDER BY employee_id
FETCH FIRST 5 PERCENT ROWS WITH TIES;
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Erweiterung der WITH-Klausel
WITH-Klausel mit Funktion:
WITH FUNCTION five_perc (p_val NUMBER)
RETURN NUMBER
IS
BEGIN
RETURN p_val * 0.05;
END;
SELECT last_name,salary,five_perc (salary) AS "5%“
FROM employees;
• die Funktion ist nur innerhalb des Statements verfügbar
• WITH-Funktionen übersteuern Stored Functions
NICHT aber SQL-Funktionen (SYSDATE, TRUNC, …)
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
WITH-Klausel mit Exception
WITH-Klausel mit Exception:
WITH FUNCTION is_numeric (v_val IN VARCHAR2)
RETURN NUMBER
IS
v_dummy NUMBER;
BEGIN
v_dummy:= to_number (v_val);
RETURN 1;
EXCEPTION
WHEN value_error THEN RETURN -1;
END is_numeric;
…
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Möglichkeiten / Grenzen der WITH-Klausel
● Eventuelle Performance-Steigerung durch WITH-Klausel
Funktionen
● Eher nur für „Einmal-Funktionen“ gut einsetzbar
● Wird nur von den neuesten Clients unterstützt:
– SQL*Plus 12c
– SQL Developer 4.x
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Pragma UDF (User Defined Function)
● Für Funktionen, die vorwiegend im SQL-Kontext verwendet werden:
CREATE OR REPLACE FUNCTION five_perc (p_val NUMBER)
RETURN NUMBER
IS
PRAGMA UDF;
v_dummy NUMBER;
…
BEGIN
…
● Verwendet das Parameterformat von SQL
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Alert.log per SQL lesen
● View X$DBGALERTEXT (ab 11g)
SELECT originating_timestamp, message_text
FROM x$dbgalertext
WHERE originating_timestamp > sysdate-2
AND (message_text LIKE '%ORA-%'
OR
message_text like '%Fatal%');
● Die Quelle ist das XML-File im ADR, es können auch XMLMethoden (xmlelement, xmlattributes, …) angewendet werden
● Alternative: V$DIAG_ALERT_EXT (kann langsam sein)
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
DBMS_UTILITY.EXPAND_SQL_TEXT
● Ersetzt VIEWs durch entsprechendes SQL auf darunterliegende Objekte
CREATE OR REPLACE VIEW emp10
AS SELECT * FROM employees WHERE department_id = 10;
● Anwendung:
DECLARE
orisql CLOB;
outsql CLOB;
BEGIN
orisql := 'SELECT ename, department_id FROM emp10 WHERE salary>1000';
dbms_utility.expand_sql_text (orisql, outsql);
dbms_output.put_line(outsql);
END;
/
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
DBMS_UTILITY.EXPAND_SQL_TEXT (Forts.)
● Ergebnis
SELECT "A1"."ENAME" "ENAME","A1"."DEPARTMENT_ID" "DEPARTMENT_ID" FROM
(SELECT "A2"."ENAME" "ENAME","A2 "."DEPARTMENT_ID" "DEPARTMENT_ID" FROM
"HR"."EMPLOEES" "A2" WHERE "A2"." DEPARTMENT_ID "=10) "A1" WHERE
"A1"."SALARY">1000
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
UTL_CALL_STACK
● Bisher: DBMS_Utility.Format_Call_Stack
– Listet den Call Stack als Array von Textzeilen auf
– Gut lesbar, aber schwer automatisch zu analysieren
● Abhilfe: UTL_CALL_STACK
– Besser strukturiert
– Lässt automatische Analyse zu
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
UTL_CALL_STACK Beispiel
CREATE OR REPLACE PROCEDURE print_call_stack
AS
depth PLS_INTEGER:= utl_call_stack.dynamic_depth ();
PROCEDURE print_stack
IS
BEGIN
dbms_output.put_line ('Lexical
Depth
Line
Name' );
dbms_output.put_line ('Depth
Number
' );
dbms_output.put_line ('-----------------' );
FOR i IN REVERSE 1 .. depth LOOP
dbms_output.put_line (
RPAD (utl_call_stack.lexical_depth(i), 10 ) || RPAD (i, 7) ||
RPAD (TO_CHAR (utl_call_stack.unit_line (i),'99'),9 )||
utl_call_stack.concatenate_subprogram (utl_call_stack.subprogram (i)));
END LOOP;
END print_stack;
BEGIN
print_stack;
END print_call_stack;
/
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
UTL_CALL_STACK Beispiel Output
SQL> EXEC call_stack_demo.main();
Lexical
Depth
------1
2
3
0
1
Depth
----5
4
3
2
1
Line
Number
---14
11
7
16
10
Name
---CALL_STACK_DEMO.MAIN
CALL_STACK_DEMO.MAIN.SUB
CALL_STACK_DEMO.MAIN.SUB.DEEPSUB
PRINT_CALL_STACK
PRINT_CALL_STACK.PRINT_STACK
BEGIN call_stack_demo.main(); END;
*
ERROR at line 1:
ORA-06501: PL/SQL: program error
ORA-06512: at "HR.CALL_STACK_DEMO", line 8
ORA-06512: at "HR.CALL_STACK_DEMO", line 11
ORA-06512: at "HR.CALL_STACK_DEMO", line 14
ORA-06512: at line 1
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
DBMS_SCHEDULER: email notification einrichten
● Scheduler Jobs können automatisch (Miss-)Erfolgsmeldungen
per mail verschicken
● Einrichten:
dbms_scheduler.set_scheduler_attribute
('email_server','smtp_server:25');
dbms_scheduler.set_scheduler_attribute
('email_sender','[email protected]');
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
DBMS_SCHEDULER: email notification aktivieren
dbms_scheduler.add_job_email_notification (
job_name => 'irgend_ein_job',
recipients => '[email protected]',
events => 'job_failed');
Oder mit Filterbedingung:
dbms_scheduler.add_job_email_notification (
job_name => 'irgend_ein_job',
recipients => '[email protected]',
events=> 'job_failed',
filter_condition => 'event.error_code = 7445');
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Zusammenfassung
●
●
●
●
●
●
●
SQL*Plus Script: SQL*Plus direkt aufrufen
SQL Row Limit
WITH-Klausel mit Funktionen
Alert.log per SQL lesen
DBMS_UTILITY.EXPAND_SQL_TEXT: Views auswickeln
UTL_CALL_STACK: Call Stack besser analysieren können
DBMS_SCHEDULER: email notification
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at
Q&A
SQL and PL/SQL unleashed
Datenbanken sind unsere Welt
www.dbmasters.at