Oracle External Procedure 사용법(C모듈)

1. External Procedure 란?

 

복잡한 수식계산을 Oracle에서 제공하는 기능으로만 충분하지 않을 경우가 있는데, 이럴 경우 C나 JAVA 같은 언어로 복잡한 기능을 작성한 후 Oracle 에서는 파라미터를 넘겨서 해당 결과를 받으면 수행속도의 개선을 가져올 수 있는데, 간단히 말하자면 C언어나 VB, JAVA 등의 언어를 사용하여 SQL에서 구현하기 어렵거나 복잡한 것을 구현한 뒤 SQL에서 호출해서 사용하는 것을 말한다.

 

 

2. 장점 및 단점

 

External Procedure을 사용하여 얻는 장점으로는 Java나 C의 재 활용성이 우수하다. 반면 External Procedure을 사용으로 발생하는 단점은 Session이 종료되지 않으면 extProc는 Oracle에서 메모리를 관리하는 영역이 아니라 O/S영역이기 때문에 한번 호출 될 때마다 해당 Session이 종료되지 않으면 끝까지 살아남게 되어 지속적인 메모리에 남아 있게 되어 O/S의 메모리 부하가 생길 수 밖에 없다.

 

그래서, O/S의 메모리를 최대한 줄일 수 있는 방법은 다음과 같다.

 

① Session의 수를 제한하여 O/S의 메모리 한계치를 벗어나지 않도록 조정한다.

② O/S에서 extProc로 생성된 것 중 오래된 Process를 Kill한다. Process를 Kill하더라도 없으면 재생성 되므로 큰 문제는 발생되지 않는다.

③ 어플리케이션에서 불필요하게 External Procedure를 호출하는 Function의 사용을 제거하고, 사용 완료 후 Session를 종료하여 메모리의 부하를 최소화하게 한다.

 

 

3. C 모듈(libbarocrypt.so)

 

C 모듈은 Java 모듈과 달리 External Procedure를 사용하는 것이 번거롭지만 External Procedure의 동작 순서는 다음과 같다.

 

 

① 사용자가 SQL에서 External Procedure에서 작성한 Function을 DB에 요청을 한다.

 

② Shared SQL Area에서 해당 문장을 Parsing하면서 External Procedure을 알고 NET8 Listener에서 사용자 SQL이 External Procedure를 호출 했으니 해석해 달라고 요청한다.

 

③ Listener는 다시 extProc 프로세스를 생성하면서 O/S에 있는 External Procedure가 있는 DLL, Procedure Name, Parameter를 넘겨준다.

 

④ extProc를 O/S에 있는 DLL 파일을 찾아서 O/S의 메모리에 Load하여 요청 받은 Function의 결과를 처리한다.

 

⑤ 처리된 결과를 SQL에 return해 준다.

 

⑥ Session이 종료되면 extproc도 자동으로 종료된다.

 

 

3.1 C 모듈 library 생성

 

C 모듈을 Oracle 내에 암복호화 Library(libcrypt_encrypts-암호화 Library, libcrypt_decrypts-복호화 Library)를 다음과 같이 각각 생성한다. (Oracle 사용자가 Library를 생성하는데 필요한 권한 필요)

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom   SQL*Plus: Release 11.2.0.1.0 Production on 화 9월 12 14:13:40 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> CREATE OR REPLACE LIBRARY libcrypt_encrypts AS '/data/icom/nurit/crypt/libusercrypt.so' ;   2  /   Library created.   SQL> CREATE OR REPLACE LIBRARY libcrypt_decrypts AS '/data/icom/nurit/crypt/libusercrypt.so' ;   2  /   Library created.   SQL> commit;   Commit complete.

 

 

3.2 암복화 Stored Function 생성 및 확인

 

Oracle에 sqlplus로 접속한 뒤 TO_ENCRYPT(암호화 함수), TO_DECRYPT(복호화 함수)를 다음과 같이 생성 및 확인한다.

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom   SQL*Plus: Release 11.2.0.1.0 Production on 화 9월 12 14:16:06 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> CREATE OR REPLACE FUNCTION TO_ENCRYPTS (input VARCHAR2) return VARCHAR2       as external       language C       library libcrypt_encrypts       name "baro_encrypts"       parameters  (input  STRING);   2    3    4    5    6    7  /   Function created.   SQL> CREATE OR REPLACE FUNCTION TO_DECRYPTS (input VARCHAR2) return VARCHAR2       as external       language C       library libcrypt_decrypts       name "baro_decrypts"       parameters  (input  STRING);   2    3    4    5    6    7  /   Function created.   SQL> commit;   Commit complete.

 

 

3.3 listener 구성

 

C 모듈을 사용하기 전에 원하는 Shared Library에 대한 경로를 포함하도록 listener를 구성해야 한다. EXTPROC_DLLS 환경 변수를 설정하여 수행하는데, Oracle 계정에 로그인 후 다음과 같이 listener.ora 파일에 설정한다.

 

SID_LIST_LISTENER =   (SID_LIST =     (SID_DESC =       (SID_NAME = PLSExtProc)       (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)       (PROGRAM = extproc)       (ENVS="EXTPROC_DLLS=ANY")     )     (SID_DESC =       (SID_NAME = ODB)       (ORACLE_HOME = /oracle/app/oracle/product/11.2.0)     )   )   LISTENER =   (DESCRIPTION_LIST =     (DESCRIPTION =       (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1521))       (ADDRESS = (PROTOCOL = TCP)(HOST = project)(PORT = 1521))     )   )   ADR_BASE_LISTENER = /oracle/app/oracle

 

 

3.4 tnsnames.ora 구성

 

C 모듈을 사용하기 전에 listener.ors 파일의 설정한 Key, SID와 동일한 값으로 Oracle 계정에 로그인 후 EXTPROC_CONNECTION_DATA을 다음과 같이 tnsnames.ora 파일에 설정한다.

 

ODB =   (DESCRIPTION =     (ADDRESS = (PROTOCOL = TCP)(HOST = project)(PORT = 1521))     (CONNECT_DATA =       (SERVER = DEDICATED)       (SERVICE_NAME = ODB)     )   )   EXTPROC_CONNECTION_DATA =   (DESCRIPTION =         (ADDRESS = (PROTOCOL = IPC)(HOST = project)(KEY = EXTPROC1521))         (CONNECT_DATA = (SID = PLSExtProc)) )

 

tnsnames.ora 파일을 설정한 후 반드시 Oracle 계정에 로그인 후 listener를 재기동해야 한다.

 

> lsnrctl start | stop | status

 

 

3.5 암복화 함수(TO_ENCRYPTS, TO_DECRYPTS) 테스트

 

icam1:icom /data/icom/nurit/crypt> sqlplus icom/icom   SQL*Plus: Release 11.2.0.1.0 Production on 화 9월 12 14:16:06 2017   Copyright (c) 1982, 2009, Oracle.  All rights reserved.     Connected to: Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production With the Partitioning, OLAP, Data Mining and Real Application Testing options   SQL> SELECT TO_ENCRYPTS('qwerqwerqwer이종일qwerqwer') FROM DUAL;   TO_ENCRYPTS('QWERQWERQWER이종일QWERQWER') -------------------------------------------------------------------------------- LkrLD7uCXBnPZreic9NgsHgsDjWQG1QQL0w9UHndzy8=   SQL> SELECT TO_DECRYPTS('LkrLD7uCXBnPZreic9NgsHgsDjWQG1QQL0w9UHndzy8=') FROM DUAL;   TO_DECRYPTS('LKRLD7UCXBNPZREIC9NGSHGSDJWQG1QQL0W9UHNDZY8=') -------------------------------------------------------------------------------- qwerqwerqwer이종일qwerqwer