19.11.2020 1 min to read

Полезные функции для Oracle

Category : Статьи

Начиная с 12c для получения MD5 рекомендуется STANDARD_HASH: https://docs.oracle.com/en/database/oracle/oracle-database/19/sqlrf/STANDARD_HASH.html
Но иногда неуместно работать с RAW.

MD5 строки символов.
CREATE OR REPLACE FUNCTION MD5 (P_VALUE VARCHAR2) RETURN VARCHAR2 AS
V_RESULT DBMS_OBFUSCATION_TOOLKIT.VARCHAR2_CHECKSUM;
BEGIN
DBMS_OBFUSCATION_TOOLKIT.MD5 (
INPUT_STRING => P_VALUE, CHECKSUM_STRING => V_RESULT
);
RETURN V_RESULT;
END;

Применение
SELECT MD5('This is a String') AS A1 FROM DUAL;

BASE64 encode
CREATE OR REPLACE FUNCTION TO_BASE64(T IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_ENCODE(UTL_RAW.CAST_TO_RAW(T)));
END TO_BASE64;

Применение
SELECT TO_BASE64('This is a String') AS A1 FROM DUAL;

BASE64 decode
CREATE OR REPLACE FUNCTION FROM_BASE64(T IN VARCHAR2) RETURN VARCHAR2 IS
BEGIN
RETURN UTL_RAW.CAST_TO_VARCHAR2(UTL_ENCODE.BASE64_DECODE(UTL_RAW.CAST_TO_RAW(T)));
END FROM_BASE64;

Применение
SELECT FROM_BASE64(TO_BASE64('This is a String')) AS A1 FROM DUAL;

SYSDATE в формате UNIXTIME
CREATE OR REPLACE FUNCTION UNIXTS RETURN PLS_INTEGER IS
UNIX_EPOCH DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
UNIX_TS PLS_INTEGER;
TZD VARCHAR2(4);

BEGIN
UNIX_TS := (SYSDATE - UNIX_EPOCH) / (1/86400);
TZD := TO_CHAR( FROM_TZ( CAST( SYSDATE AS TIMESTAMP), 'CET'), 'TZD' );
IF TZD = 'CET' THEN
UNIX_TS := UNIX_TS - 3600;
ELSIF TZD = 'CEST' THEN
UNIX_TS := UNIX_TS - 7200;
ELSE
RAISE_APPLICATION_ERROR( -20902,'Unknown TZD "' || TZD || '"' );
END IF;
RETURN (UNIX_TS);
END UNIXTS;

Применение
SELECT UNIXTS() AS SYSDATE_UNIXTIME FROM DUAL;
SELECT UNIXTS()+1800 AS SYSDATE_UNIXTIME_1800 FROM DUAL;

Проще вариант SYSDATE в формате UNIXTIME
CREATE OR REPLACE FUNCTION GET_UNIXTS RETURN PLS_INTEGER IS
UNIX_EPOCH DATE := TO_DATE('19700101000000','YYYYMMDDHH24MISS');
UNIX_TS PLS_INTEGER;
BEGIN
UNIX_TS := (SYSDATE - UNIX_EPOCH) / (1/86400);
RETURN (UNIX_TS);
END GET_UNIXTS;

Применение
SELECT GET_UNIXTS() AS SYSDATE_UNIXTIME FROM DUAL;
SELECT GET_UNIXTS()+1800 AS SYSDATE_UNIXTIME_1800 FROM DUAL;