간단한 c파일 작성

int add ( int a , int b )
{
   return a + b;
}

동적 library 생성

gcc -shared -fPIC -o add.so add.c

library 생성

library가 있는 폴더에서 sqlplus를 접속했다.

oracle> create or replace library ext_lib as 'add.so';
        /

Library created.

External Procedure 생성

oracle> CREATE OR REPLACE FUNCTION ext_func( p1 BINARY_INTEGER, p2 BINARY_INTEGER ) 
           RETURN binary_integer AS
        LANGUAGE C
        LIBRARY ext_lib NAME "add";
        /
              
Function created.

External Procedure 실행기

ORA-28595: Extproc agent : Invalid DLL Path

SEONY@oracle> select ext_func(1,1) from dual;
select ext_func(1,1) from dual

*
ERROR at line 1:
ORA-28595: Extproc agent : Invalid DLL Path
ORA-06512: at "SEONY.EXT_FUNC", line 1
ORA-06512: at line 1

• External Procedure 환경변수 설정
  • '''$ORACLE_HOME/hs/admin/extproc.ora''' 파일에 아래와 같이 설정한다.

  SET EXTPROC_DLLS=ANY

  Oracle 12c와 달리 library loading을 위한 agent가 server에서 실행된다.

  Oracle 18c 메뉴얼에서
  Note:The default configuration for external procedures no longer requires a network listener to work with Oracle Database and extproc. Oracle Database now spawns extproc directly, eliminating the risk that Oracle Listener might spawn extproc unexpectedly. This default configuration is recommended for maximum security.

실행 성공

• 위와 같이 extproc.ora 설정 후 다시 쿼리 실행

SEONY@oracle> SELECT ext_func( 1 , 1 ) FROM dual;

EXT_FUNC(1,1)
-------------
            2