Oracle External Procedure를 통해 함수를 만들고 실행해야 할 일이 있어,
구글링을 엄청나게 하고 많은 에러가 나기를 반복한 후 결국 성공해냈다..
구글링을 하면서 내가 원하는 정보를 찾기 너무 어려웠어서 나중에 또 이렇게 사용해야 할 일이 있을 경우와
나처럼 Oracle External Procedure를 사용해야 할 일이 있는 사람들을 위해 정리를 해 본다..!
Oracle External Procedure는 C언어로 작성 된 코드를 라이브러리화 하여, Oracle 내부에 함수 또는 프로시저로
선언하여 C언어 코드를 호출할 수 있는 기능이다.
Oracle External Procedure를 사용 하는 방법은 다음과 같다.
1. 사용하고 싶은 코드를 C언어로 작성한다.
2. C언어를 .so라이브러리로 빌드한다.
3. 오라클 리스너에 External procedure를 사용할 수 있도록 설정해준다.
4. 오라클에 접속하여 2번에서 만든 라이브러리 경로를 주어 db에 라이브러리를 생성한다.
5. 4에서 생성한 라이브러리를 통해 호출 함수 또는 프로시저를 만든다.
나의 경우, 문자열을 받아 그 문자열을 내부코드에서 변형하여 변형 된 문자열을 return하고자 했다.
이 경우에서의 예시코드와 함께 External procedure를 생성해 보자!
1. 사용하고 싶은 코드를 C언어로 작성한다.
#include<stdio.h>
#include<string.h>
char *test_elephant(char *input_str, char *output_str)
{
char input_temp[1024+1] = {0,};
int i = 0;
strncpy(input_temp, input_str, strlen(input_str)+1);
for(i = 0; i < 1024; i++)
{
if( input_temp[i] == 0x00 )
input_temp[i] = 'A';
else
input_temp[i] += input_temp[i];
}
memcpy(output_str, input_temp, strlen(input_temp)+1);
return (char *)output_str;
}우선 엄청엄청 간단한 코드를 작성해보았다.
위 코드를 짠 이유는 procedure로 input_str에 varchar 값을 넣어서 호출한 후, output_str에 담아 return하고 싶어서인데,
위 코드에서 가장 중요한 점은!! ( 꼭 유념해야함) 함수 호출 시 리턴받을 버퍼 output_str을 받아와야 한다는 점이다.
보통 생각을 해보자면 위 소스코드에서 왜 굳이 output_str을 받아서 return을 하지..? 라고 생각할 것이다.
근데 위 코드에서 보면은 input_str에서 들어온 문자열의 길이보다 return될 문자열의 길이가 훨씬 길 수도 있다는 것을 생각할 수 있다.
그 점에서 내가 여러가지 시도한 방법으로는
1. 지역변수에 선언한 데이터에 문자열을 담아 return 한다
=> 오라클 함수로 출력 시 값이 안나와서 실패.
2. input_str에 담아서 return 한다
=> 한번은 출력되고 한번은 ORA-28575: unable to open RPC connection to external procedure agent 에러가 출력되며
문제 발생 ( 이 경우 C 내부 함수에서 문제가 있을 경우 발생하거나 네트워크가 제대로 연결되지 않을 경우
발생한다고 하는데, 아마도 들어온 버퍼보다 나가는 버퍼 사이즈가 커서 오버플로우가 나지 않았을까..하는
생각이다. 왜냐하면 들어온데이터가 크고, 나간 데이터가 작을 때는 정상적으로 잘 수행했기 때문..)
이렇게 여러번의 삽질 후 정상적으로 잘 출력 하기 위한 소스코드가 바로 위의 예시이다 왜 저렇게 작성했는지는
아래 프로시저를 보면 알 수 있다.
2. C코드를 .so 라이브러리로 빌드한다.
--linux
gcc -fPIC -m64 -nostdlib -c test_elephant.c -o test_elephant.o
gcc -fPIC -m64 -nostdlib -shared test_elephant.o -o test_elephant.so
--HP-UX
cc +DD64 -DSS_64BIT_SERVER -c test_elephant.c
cc +DD64 -DSS_64BIT_SERVER -b -o test_elephant.sl test_elephant.o위의 커맨드로 C코드를 라이브러리로 빌드해야한다.
그리고 이 라이브러리는 반드시 Oracle이 접근할 수 있는 경로에 두어야 한다..!
그렇지 않으면 라이브러리를 읽을 권한이 없다는 에러가 발생한다.
3. 오라클 리스너에 External procedure를 사용할 수 있도록 설정해준다.
$ORACLE_HOME/network/admin/listener.ora 파일에 External procedure를 사용할 수 있도록 셋팅을 해줘야하는데,
빨간색 부분이 추가 해 줘야할 부분이다.
ORA1912 =
(DESCRIPTION_LIST =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT =1523))
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
)
)
SID_LIST_ora1912 =
(SID_LIST =
(SID_DESC =
(ORACLE_HOME = /oracle/ora1912/ORA19/product/19.3.0.0/db_1)
(SID_NAME = ora1912)
)
(SID_DESC =
(SID_NAME = PLSExtProc)
(ORACLE_HOME = /oracle/ora1912/ORA19/product/19.3.0.0/db_1)
(PROGRAM=extproc)
(ENVS="EXTPROC_DLLS=ANY"))
)
윗 부분에 있는 KEY 작성부는 EXTPROC+PORT번호로 적어주는게 편리하다.
하단에 ENVS=~ 여기는 나는 ANY로 설정해두었지만 특정 라이브러리에만 접근하게 하려면 그 라이브러리의 경로를 적어주면 된다.
$ORACLE_HOME/network/admin/tnsnames.ora 파일에도 External procedure를 사용할 수 있도록 셋팅을 해줘야한다.
EXTPROC_CONNECTION_DATA =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC1523))
(CONNECT_DATA = (SID = PLSExtProc)
)
)
ora1912 =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1523))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = ORA1912)
)
)
여기에서 주의 할 점은 KEY는 반드시 listener.ora에 작성했던 키와 동일한 키로 작성해줘야한다**
이렇게 리스너 설정이 모두 완료되었으면
lsnrctl stop / lsnrctl start 명령어로 리스너를 재 기동 시켜서 인식시켜줘야한다!
정상적으로PLSExtProc가 뜬 것을 확인할 수 있다.
*리스너 설정에 External Procedure 설정을 제대로 하지 않으면 에러가 발생하니 External Procedure 생성 시 관련 에러가 떨어지면 반드시 리스너 설정을 제대로 했는 지 설정 후 재기동 했는지 꼭 확인해야한다.*
4. 오라클에 접속하여 2번에서 만든 라이브러리 경로를 주어 db에 라이브러리를 생성한다.
우선 sqlplus로 sysdba 접속 후 library 생성 권한을 줘야한다.
grant CREATE LIBRARY to [유저이름];그 후 function 또는 procedure를 생성 할 유저로 접속 후 라이브러리를 생성해 준다
create or replace library [라이브러리이름] as '라이브러리가 위치한 경로';/
ex)create or replace library elephantlib as '/oracle/ora1912/ORA19/product/19.3.0.0/db_1/test_elephant.so';/여기서 주의 할 점은 라이브러리 위치가 oracle 계정이 접근 불가능한 위치에 라이브러리가 있으면,
나중에 function 또는 procedure를 생성하고 실행할 때 라이브러리 접근이 불가능하다는 에러가 발생하므로 위치를 잘 지정해서 라이브러리를 옮겨놓아야 한다.
5. 4에서 생성한 라이브러리를 통해 호출 함수 또는 프로시저를 만든다.
나의 경우 프로시저를 만들고, 프로시저를 function으로 호출하도록 만들었다.
그 이유는 return할 데이터가 있고, 그 데이터의 타입이 varchar2형인데 return시 담아 줄 버퍼의 크기가 담으려는 데이터보다 사이즈가 작으면 ORA-28575: unable to open RPC connection to external procedure agent 에러가 발생한다고 위에서
말한 바가 있다.
때문에 나의 수많은 테스트로는 애초에 실행을 할 때 버퍼 사이즈의 크기를 지정하여 받아와야 에러가 발생하지 않아서 이렇게 생성해 주었다.
--procedure 생성
create or replace procedure
p_test_elephant(input IN VARCHAR2, output OUT VARCHAR2)
as external
language C
library elephantlib
name "test_elephant"
parameters(input string, output string);
--procedure 호출 function 생성
create or replace function f_test_elephant(input varchar2)
return varchar2
is
output varchar2(1024);
BEGIN
p_test_elephant(input,output);
DBMS_OUTPUT.PUT_LINE(output);
return output;
end;
/
--함수 실행부
select f_test_elephant('test text') from dual;만약 처음부터 function으로 생성하고 실행하고 싶다면 다음과 같은 방법으로도 사용 가능하다.
--function 생성
create or replace function
f_test_elephantinput IN VARCHAR2, output OUT VARCHAR2) return varchar2
as external
language C
library elephantlib
name "test_elephant"
parameters(input string, output string);
/
--함수 실행부
DECLARE
input varchar2(1024);
output varchar2(1024);
result varchar2(1024);
BEGIN
input := '집어넣을 문자열';
result := f_test_elephant(input,output);
DBMS_OUTPUT.PUT_LINE(output);
end;
/
한 줄의 SQL로 깔끔하게 실행하기 위해서는 위의 방법이 더 괜찮은 방법이다.
문자열을 return하기 위한 예제가 없어 어려움이 많았지만 다른 int나 float형의 return 방식으로는 오라클이 제공하는
메뉴얼을 참고하면 쉽게 구현 가능하니 다른 return형으로 작성을 원하는 사람은 아래 링크의 오라클 문서를 참고하면 도움이 될 것 같다.
참고
https://docs.oracle.com/cd/A58617_01/server.804/a58236/10_procs.htm#426081
External Procedures
Note: To call a packaged function from SQL statements, you must use the pragma RESTRICT_REFERENCES, which asserts the purity level of the function (the extent to which the function is free of side effects). PL/SQL cannot check the purity level of the corre
docs.oracle.com