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

Метки: ,

Начиная с 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;
Close Menu