create or replace FUNCTION pa_ObtenerValorConstante (p_constante IN VARCHAR2)
RETURN VARCHAR2
IS
v_Dummy PA_detalle_constantes.valor%TYPE;
PROCEDURE RegistrarConstante (p_constante IN VARCHAR2, p_valor IN VARCHAR2)
IS
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
INSERT INTO pa_detalle_constantes (des_constante, valor)
VALUES (p_constante, p_valor);
COMMIT;
EXCEPTION
WHEN DUP_VAL_ON_INDEX
THEN
NULL;
END RegistrarConstante;
BEGIN
SELECT valor
INTO v_Dummy
FROM pa_detalle_constantes
WHERE des_constante = p_constante;
RETURN v_Dummy;
EXCEPTION
WHEN NO_DATA_FOUND
THEN
EXECUTE IMMEDIATE ('begin :1 := ' || p_constante || '; end;')
USING IN OUT v_Dummy;
RegistrarConstante (p_constante, v_Dummy);
RETURN v_Dummy;
END pa_ObtenerValorConstante;
Ejemplo:
select pa_ObtenerValorConstante ('CONSTANTE') from dual;
