sql - comandos - Función de reposo en ORACLE

sqlplus format output

Question

Necesito ejecutar una consulta SQL en ORACLE que toma una cierta cantidad de tiempo. Entonces escribí esta función:

CREATE OR REPLACE FUNCTION MYSCHEMA.TEST_SLEEP
(
TIME_  IN  NUMBER
)
RETURN INTEGER IS
 BEGIN
   DBMS_LOCK.sleep(seconds => TIME_);
RETURN 1;
 EXCEPTION
   WHEN OTHERS THEN
   RAISE;
   RETURN 1;
END TEST_SLEEP;

y yo llamo de esta manera

SELECT TEST_SLEEP(10.5) FROM DUAL

pero para trabajar necesito establecer la concesión de DBMS_LOCK al propietario del procedimiento.

¿Cómo puedo reescribir esta función sin usar la función DBMS_LOCK.sleep ?

Answer 1

¿Qué hay sobre el código Java envuelto por un procedimiento? Simple y funciona bien.

CREATE OR REPLACE AND COMPILE JAVA SOURCE NAMED SNOOZE AS
public final class Snooze {
  private Snooze() {
  }
  public static void snooze(Long milliseconds) throws InterruptedException {
      Thread.sleep(milliseconds);
  }
}

CREATE OR REPLACE PROCEDURE SNOOZE(p_Milliseconds IN NUMBER) AS
    LANGUAGE JAVA NAME ''Snooze.snooze(java.lang.Long)'';

Answer 2

A menos que se otorgue acceso a DBMS_LOCK.sleep , esto funcionará, pero es un hack horrible:

IN_TIME INT; --num seconds
v_now DATE;

-- 1) Get the date & time 
SELECT SYSDATE 
  INTO v_now
  FROM DUAL;

-- 2) Loop until the original timestamp plus the amount of seconds <= current date
LOOP
  EXIT WHEN v_now + (IN_TIME * (1/86400)) <= SYSDATE;
END LOOP;

Answer 3

Cree un procedimiento que solo haga su bloqueo e instálelo en un usuario diferente, que sea "de confianza" con dbms_lock (USERA), otorgue acceso a USERA a dbms_lock.

Luego solo concede USERB acceso a esta función. Entonces no necesitarán poder acceder a DBMS_LOCK

(asegúrese de no tener usera y userb en su sistema antes de ejecutar esto)

Conéctese como usuario con privilegios de concesión para dbms_lock, y puede crear usuarios

drop user usera cascade;
drop user userb cascade;
create user usera default tablespace users identified by abc123;
grant create session to usera;
grant resource to usera;
grant execute on dbms_lock to usera;

create user userb default tablespace users identified by abc123;
grant create session to userb;
grant resource to useb

connect usera/abc123;

create or replace function usera.f_sleep( in_time number ) return number is
begin
 dbms_lock.sleep(in_time);
 return 1;
end;
/

grant execute on usera.f_sleep to userb;

connect userb/abc123;

/* About to sleep as userb */
select usera.f_sleep(5) from dual;
/* Finished sleeping as userb */

/* Attempt to access dbms_lock as userb.. Should fail */

begin
  dbms_lock.sleep(5);
end;
/

/* Finished */

Answer 4

Desde Oracle 18c puede usar el procedimiento DBMS_SESSION.SLEEP :

Este procedimiento suspende la sesión por un período de tiempo específico.

DBMS_SESSION.SLEEP (seconds  IN NUMBER)

DBMS_SESSION.sleep está disponible para todas las sesiones sin necesidad de DBMS_SESSION.sleep adicionales. Tenga en cuenta que DBMS_LOCK.sleep está en desuso.

Si necesita una consulta simple, puede usar WITH FUNCTION :

WITH FUNCTION my_sleep(i NUMBER)
RETURN NUMBER
BEGIN
    DBMS_SESSION.sleep(i);
    RETURN i;
END;
SELECT my_sleep(3) FROM dual;

Answer 5

Hay un buen artículo sobre este tema: PL / SQL: Dormir sin usar DBMS_LOCK que me ayudó. Usé la Opción 2 envuelta en un paquete personalizado. Las soluciones propuestas son:

Opción 1: APEX_UTIL.sleep

Si APEX está instalado, puede usar el procedimiento "PAUSA" del paquete APEX_UTIL disponible públicamente.

Ejemplo: "Esperar 5 segundos":

SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE(''Start '' || to_char(SYSDATE, ''YYYY-MM-DD HH24:MI:SS''));
    APEX_UTIL.PAUSE(5);
    DBMS_OUTPUT.PUT_LINE(''End   '' || to_char(SYSDATE, ''YYYY-MM-DD HH24:MI:SS''));
END;
/

Opción 2: java.lang.Thread.sleep

Otra opción es el uso del método "sleep" de la clase de Java "Thread", que se puede usar fácilmente al proporcionar un procedimiento simple de envoltura PL / SQL:

Nota: Recuerde que "Thread.sleep" usa milisegundos.

--- create ---
CREATE OR REPLACE PROCEDURE SLEEP (P_MILLI_SECONDS IN NUMBER) 
AS LANGUAGE JAVA NAME ''java.lang.Thread.sleep(long)'';

--- use ---
SET SERVEROUTPUT ON ;
BEGIN
    DBMS_OUTPUT.PUT_LINE(''Start '' || to_char(SYSDATE, ''YYYY-MM-DD HH24:MI:SS''));
    SLEEP(5 * 1000);
    DBMS_OUTPUT.PUT_LINE(''End   '' || to_char(SYSDATE, ''YYYY-MM-DD HH24:MI:SS''));
END;
/

Answer 6

Parece que el procedimiento / función java podría funcionar. Pero ¿por qué no compila su función en un usuario como el esquema de la aplicación o una cuenta de administrador que tiene esta concesión y solo le permite ejecutar su cuenta de desarrollador en ella? De esa forma se usan los derechos de los definidores.

Answer 7

Puede usar DBMS_PIPE.SEND_MESSAGE con un mensaje que es demasiado grande para el conducto, por ejemplo, para un retraso de 5 segundos, escriba XXX en un conducto que solo puede aceptar un byte utilizando un tiempo de espera de 5 segundos como se muestra a continuación.

dbms_pipe.pack_message(''XXX'');<br>
dummy:=dbms_pipe.send_message(''TEST_PIPE'', 5, 1);

Pero eso requiere una subvención para DBMS_PIPE así que quizás no sea mejor.

Answer 8

Sería mejor implementar un mecanismo de sincronización. Lo más fácil es escribir un archivo después de que se complete el primer archivo. Entonces tienes un archivo centinela.

Entonces, los programas externos buscan que exista el archivo centinela. Cuando lo hace, sabe que puede usar los datos de manera segura en el archivo real.

Otra forma de hacerlo, que es similar a la forma en que algunos navegadores lo hacen cuando se descargan archivos, es tener el archivo llamado base-name_part hasta que el archivo se descargue por completo y luego, al final, renombrar el archivo como nombre base. De esta forma, el programa externo no puede "ver" el archivo hasta que se complete. De esta forma no sería necesario volver a escribir el programa externo. Lo cual podría ser mejor para esta situación.

Answer 9

Si Java está instalado en su 11G, puede hacerlo en una clase Java y llamarlo desde su PL / SQL, pero no estoy seguro de que no requiera también una concesión específica para llamar a Java.

Answer 10

Si se ejecuta dentro de "sqlplus", puede ejecutar un comando del sistema operativo host "sleep":

!sleep 1

o

host sleep 1