string_udf.sql

WHENEVER SQLERROR EXIT SQL.SQLCODE

ALTER SESSION SET CONTAINER = XEPDB1;
/

-- Clean up in case objects already exist
BEGIN
  EXECUTE IMMEDIATE 'DROP FUNCTION string_udf_wrapper';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -4043 THEN RAISE; END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP LIBRARY string_udf_lib';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE != -4043 THEN RAISE; END IF;
END;
/
BEGIN
  EXECUTE IMMEDIATE 'DROP PUBLIC SYNONYM string_udf_wrapper';
EXCEPTION WHEN OTHERS THEN
  IF SQLCODE NOT IN (-1430, -1432) THEN RAISE; END IF;
END;
/

CREATE OR REPLACE LIBRARY string_udf_lib AS '/opt/oracle/product/21c/dbhomeXE/lib/string_udf.so';
/

CREATE OR REPLACE FUNCTION string_udf_wrapper(input VARCHAR2) RETURN VARCHAR2
AS EXTERNAL
    NAME "string_udf"
    LIBRARY string_udf_lib
    LANGUAGE C
    WITH CONTEXT
    PARAMETERS (CONTEXT, input STRING, RETURN INDICATOR SHORT, RETURN STRING);
/

GRANT EXECUTE ON string_udf_wrapper TO PUBLIC;
/

CREATE OR REPLACE PUBLIC SYNONYM string_udf_wrapper FOR sys.string_udf_wrapper;
/

SHOW ERRORS FUNCTION string_udf_wrapper;

EXIT;