prog:plsql_sleep_19c
Unterschiede
Hier werden die Unterschiede zwischen zwei Versionen angezeigt.
Beide Seiten der vorigen RevisionVorhergehende ÜberarbeitungNächste Überarbeitung | Vorhergehende ÜberarbeitungLetzte ÜberarbeitungBeide Seiten der Revision | ||
prog:plsql_sleep_19c [2019/05/22 09:38] – [DBMS_LOCK.sleep] gpipperr | prog:plsql_sleep_19c [2023/02/01 11:42] – [PL/SQL - Eine DB Session "schlafen" legen - Wie und welche Sleep Funktion in welcher Oracle DB Version einsetzen?] gpipperr | ||
---|---|---|---|
Zeile 1: | Zeile 1: | ||
+ | =====PL/SQL - Eine DB Session " | ||
+ | |||
+ | **Aufgabe**: | ||
+ | |||
+ | Z.B. in einem Monitoring Job, der jede Minute prüfen ob ein andere Job noch aktiv ist und keine Fehler geworfen hat. | ||
+ | |||
+ | <note warning> | ||
+ | |||
+ | |||
+ | |||
+ | **Möglichkeiten: | ||
+ | |||
+ | **APEX Installiert: | ||
+ | * APEX_UTIL.pause ( aber <fc # | ||
+ | |||
+ | **DB Version vor 18c:** | ||
+ | * Java über java.lang.Thread.sleep | ||
+ | * DBMS_LOCK.sleep (<fc # | ||
+ | |||
+ | |||
+ | **DB Version ab 18c** | ||
+ | * <fc # | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | |||
+ | ====APEX_UTIL.pause==== | ||
+ | |||
+ | Ist in der DB APEX installiert, | ||
+ | |||
+ | 10 Sekunden warten: | ||
+ | <code plsql> | ||
+ | begin | ||
+ | | ||
+ | end; | ||
+ | / | ||
+ | </ | ||
+ | |||
+ | Jetzt ist nur die Frage, wie das intern umgesetzt wird. | ||
+ | Das Synonym " | ||
+ | |||
+ | Und alle Werte über 120 Sekunden werden mit dem Default 120 Sekunden überschrieben! | ||
+ | |||
+ | D.h. die maximale Wartezeit beträgt 120 Sekunden. | ||
+ | |||
+ | Eigene Routine um auch länger als 120s warten zu können und dann ab der Version 18 gleich | ||
+ | <code sql> | ||
+ | -- | ||
+ | CREATE OR REPLACE | ||
+ | PROCEDURE sleep_plsql(p_seconds IN NUMBER) | ||
+ | IS | ||
+ | v_parts pls_integer; | ||
+ | v_LastRound pls_integer; | ||
+ | v_max_pos_seconds pls_integer: | ||
+ | BEGIN | ||
+ | |||
+ | |||
+ | $IF DBMS_DB_VERSION.VER_LE_12 $THEN | ||
+ | | ||
+ | $ELSE | ||
+ | | ||
+ | $END | ||
+ | | ||
+ | IF p_seconds/ | ||
+ | --- floor largest integer equal to or less than | ||
+ | v_parts: | ||
+ | -- return the remainder | ||
+ | v_LastRound: | ||
+ | ELSE | ||
+ | v_parts:=0; | ||
+ | v_LastRound: | ||
+ | END IF; | ||
+ | |||
+ | |||
+ | -- if we are on 18 use the DBMS_SESSION sleep methode! | ||
+ | $IF DBMS_DB_VERSION.VER_LE_12 $THEN | ||
+ | |||
+ | -- we need an oracle apex security context .-( | ||
+ | -- put in your workspace | ||
+ | apex_util.set_security_group_id (p_security_group_id => apex_util.find_security_group_id (p_workspace => ' | ||
+ | |||
+ | -- APEX_UTIL.PAUS cann only sleep may 120s! | ||
+ | -- | ||
+ | IF v_parts > 0 THEN | ||
+ | FOR i IN 1..v_parts | ||
+ | loop | ||
+ | | ||
+ | END loop; | ||
+ | END IF; | ||
+ | --- | ||
+ | IF v_LastRound > 0 THEN | ||
+ | APEX_UTIL.PAUSE(v_LastRound); | ||
+ | END IF; | ||
+ | $ELSE | ||
+ | -- DBMS_SESSION can only sleep may 3600s! | ||
+ | -- | ||
+ | IF v_parts > 0 THEN | ||
+ | FOR i IN 1..v_parts | ||
+ | loop | ||
+ | | ||
+ | END loop; | ||
+ | END IF; | ||
+ | --- | ||
+ | IF v_LastRound > 0 THEN | ||
+ | DBMS_SESSION.sleep(v_LastRound); | ||
+ | END IF; | ||
+ | $END | ||
+ | END; | ||
+ | / | ||
+ | |||
+ | --- testen | ||
+ | |||
+ | set serveroutput on | ||
+ | begin | ||
+ | dbms_output.put_line(' | ||
+ | sleep_plsql(11); | ||
+ | dbms_output.put_line(' | ||
+ | sleep_plsql(119); | ||
+ | dbms_output.put_line(' | ||
+ | sleep_plsql(123); | ||
+ | dbms_output.put_line(' | ||
+ | sleep_plsql(245); | ||
+ | dbms_output.put_line(' | ||
+ | sleep_plsql(545); | ||
+ | dbms_output.put_line(' | ||
+ | end; | ||
+ | / | ||
+ | |||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | ===Problem ORA-20987: APEX - Package variable g_security_group_id must be set ==== | ||
+ | |||
+ | Fehler: | ||
+ | <code sql> | ||
+ | ORA-20987: APEX - Package variable g_security_group_id must be set. - Contact your application administrator. | ||
+ | </ | ||
+ | |||
+ | Lösung, Security ID setzen! | ||
+ | <code sql> | ||
+ | -- we need an oracle apex security context .-( | ||
+ | -- put in your workspace | ||
+ | | ||
+ | apex_util.set_security_group_id (p_security_group_id => apex_util.find_security_group_id (p_workspace => ' | ||
+ | | ||
+ | </ | ||
+ | |||
+ | Das ist dann leider nicht so schön, damit wird der Code doch sehr von der Umgebung abhängig. | ||
+ | |||
+ | |||
+ | === Wait Event - PL/SQL lock timer === | ||
+ | |||
+ | In der DB ist <fc # | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ==== Java ==== | ||
+ | |||
+ | Verwendung der Java Klasse " | ||
+ | |||
+ | |||
+ | <code sql> | ||
+ | create or replace PROCEDURE sleep_java(p_milli_seconds in number) | ||
+ | AS | ||
+ | LANGUAGE JAVA NAME ' | ||
+ | </ | ||
+ | |||
+ | |||
+ | <fc # | ||
+ | |||
+ | Wie: | ||
+ | {{ : | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====DBMS_LOCK.sleep==== | ||
+ | |||
+ | |||
+ | |||
+ | Nachteil: | ||
+ | |||
+ | * Sicherheitsprobleme mit DBMS_LOCK habe dazu geführt das her keine Public Synonym in der DB für dieses Package vorliegt und damit ein direkter Grant benötigt wird! | ||
+ | |||
+ | |||
+ | <note warning> | ||
+ | |||
+ | ---- | ||
+ | |||
+ | ====DBMS_SESSION.sleep==== | ||
+ | |||
+ | Ab der Version 18c! | ||
+ | |||
+ | Session " | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | begin | ||
+ | | ||
+ | end; | ||
+ | |||
+ | </ | ||
+ | |||
+ | |||
+ | === Problem! nur bis 3600s möglich! === | ||
+ | |||
+ | <code sql> | ||
+ | |||
+ | SYS@GPI-?> | ||
+ | 2 | ||
+ | 3 END; | ||
+ | 4 / | ||
+ | BEGIN | ||
+ | * | ||
+ | ERROR at line 1: | ||
+ | ORA-38148: invalid time limit specified | ||
+ | ORA-06512: at " | ||
+ | ORA-06512: at " | ||
+ | ORA-06512: at line 2 | ||
+ | </ | ||
+ | |||
+ | Siehe dazu den Code oben mit APEX, hier nochmal die wichigen Teile für eine Umgebung die noch 12c und 19c für die Software verwendt: | ||
+ | <code sql> | ||
+ | PROCEDURE sleep_plsql(p_seconds in number) | ||
+ | is | ||
+ | |||
+ | v_parts pls_integer: | ||
+ | v_LastRound pls_integer: | ||
+ | v_max_pos_seconds pls_integer: | ||
+ | v_use_java boolean: | ||
+ | |||
+ | BEGIN | ||
+ | |||
+ | $IF DBMS_DB_VERSION.VER_LE_12 $THEN | ||
+ | v_max_pos_seconds: | ||
+ | $ELSE | ||
+ | v_max_pos_seconds: | ||
+ | $END | ||
+ | |||
+ | IF p_seconds/ | ||
+ | --- floor largest integer equal to or less than | ||
+ | v_parts: | ||
+ | -- return the remainder | ||
+ | v_LastRound: | ||
+ | ELSE | ||
+ | v_parts:=0; | ||
+ | v_LastRound: | ||
+ | END IF; | ||
+ | |||
+ | -- if we are on 18 use the DBMS_SESSION sleep methode! | ||
+ | $IF DBMS_DB_VERSION.VER_LE_12 $THEN | ||
+ | | ||
+ | ... use apex sleep see code above | ||
+ | | ||
+ | | ||
+ | $ELSE | ||
+ | | ||
+ | | ||
+ | IF v_parts > 0 THEN | ||
+ | FOR i IN 1..v_parts | ||
+ | loop | ||
+ | | ||
+ | END loop; | ||
+ | END IF; | ||
+ | --- | ||
+ | IF v_LastRound > 0 THEN | ||
+ | DBMS_SESSION.sleep(v_LastRound); | ||
+ | END IF; | ||
+ | | ||
+ | |||
+ | end; | ||
+ | </ | ||
+ | |||
+ | ---- | ||
+ | |||
+ | |||
+ | |||
+ | |||
+ | ==== Quellen ==== | ||
+ | |||
+ | |||
+ | * https:// | ||
+ | * https:// | ||
+ | * https:// |
prog/plsql_sleep_19c.txt · Zuletzt geändert: 2023/02/01 12:12 von gpipperr