No. 10939
PL/SQL에서 DBMS_PIPE를 이용해서 PRO*C CALL하기
===============================================
PURPOSE
--------
다음은 DBMS_PIPE를 이용하여 PRO*C 를 Call하는 방법을 알아본다.
Explanation
----------------
일단 먼저 DBMS_PIPE package에 대해 알아보자.
이 package는 SYS의 소유이며, catproc.sql을 실행함으로써 생성된다.
이를 run하기 위해서는 execute privileges가 있어야 한다.
이 package는 같은 database에 연결된 session끼리 message 를 send
할 수 있도록 한다. 그 중 한 session 이 PL/SQL block 이고 다른
session 이 C program일 수 있다.
그러면 다음에서 daemon.pc 와 daemon.sql을 살펴보자:
daemon.pc 는 C program이고 이는 message를 receive할 수 있도록
running 되고 있어야 한다.
그러나 이는 대부분 sleeping state이다.
이 상태에서 program은 pipe를 통해 전달되는 message를 기다리며
looping 상태에 있다.
message 를 받을 때 이 program은 wake up되며 message를 실행한다.
이 'sleeping' 과 'waking up'은 dbms_pipe.receive_message() 에
의한다.
daemon.sql 은 PL/SQL package 이고 이 package는 deamon 에
message 를 send하고 receive 할 procedure 로 구성되어 있다.
deamon 은 STOP command 를 제외하고, 항상 message 를 package 에게
다시 send 한다.
이 process 를 위해 sql*plus 에서 deamon.sql 을 실행하여 package를
생성하여야 하고, deamon.pc 를 compile 하여 실행 file 을 만들어야
한다.
compile 시 precompile option 인 'sqlcheck=semantics'를 사용하여야
한다. ( program 이 embedded PL/SQL block을 가지므로 )
또한 'userid' precompile option 을 사용하여 precompiler가 어떻게
database에 connect 되는지 알 수 있도록 한다.
eg) userid = scott/tiger'.
우리는 system commands 를 실행하고, (limited) dynamic sql을
실행하기 위해
functions daemon.execute_system() and daemon.execute_sql()를
call하기 위해 package를 사용할 수 있다.
이 daemoon packaged procedures execute_system() 과 execute_sql() 은
SQL*Plus, 다른 precompiler의 embedded PL/SQL, 심지어 forms의
trigger 에서 실행 가능하다 .
이 package는 오직 non-query SQL statements(no bind variable)를
실행시만 사용 가능하다.
NOTE: 또한 daemon package는 결과를 display 시 dbms_output
package 를 사용한다.
이를 위해 'set serveroutput on' 이 미리 define되어 있어야 한다.
/***************************************************************
File: daemon.sql
이는 daemon package이고, dbms_pipe 를 통해 deamon listener에게
message 를 send한다. 이는 2 function 과 1 procedure 를 갖는다 :
execute_sql: 첫번째 argument 로 주어진 sql command 를 실행을
위해 daemon listener 에게 넘겨준다. 이 sql command 는 query이면
안 된다.
sqlcode를 return 한다.
execute_system: 첫번째 argument로 주어진 system command를 실행을
위해 daemon listener 에게 넘겨준다. system command 의 결과를
return 한다.
stop: deamon 이 exit 하도록 한다.
이후 execute_sql 과 execute_system 은 deamon 이 restart
될 때까지 실행 불가능하다.
이 package는 pipe name 을 통해 message 를 send 하는데,
이는 dbms_pipe.unique_session_name 이다. 이런 방법으로 각
session은 자기의 pipe를 통해 실행되고 다른 session의 message를
receive할 수 없다.
***************************************************************/
create or replace package daemon as
/* Executes a non-query sql statement or plsql block. Arguments:
command: the sql statement to execute
timeout: (optional) number of seconds to wait to send or
receive a message
Returns the sqlcode after execution of the statement. */
function execute_sql
(command varchar2, timeout number default 10)
return number;
/* Executes a system (host) command. Arguments:
command: the command to execute
timeout: (optional) number of seconds to wait to send or
receive a message.
Returns the value passed to the operating system by the
command. */
function execute_system
(command varchar2, timeout number default 10)
return number;
/* Tells the daemon listener to exit. Arguments:
timeout: (optional) number of seconds to wait to
send the message.*/
procedure stop(timeout number default 10);
end daemon;
/
create or replace package body daemon as
function execute_system
(command varchar2, timeout number default 10)
return number is
s number;
result varchar2(20);
command_code number;
pipe_name varchar2(30);
begin
/* Use uniqe_session_name to generate a unique name for the
return pipe.
We include this as part of the inital message to the daemon,
and it is send along the pipe named 'daemon'. */
pipe_name := dbms_pipe.unique_session_name;
/* Send the 'SYSTEM' command to the daemon. */
dbms_pipe.pack_message('SYSTEM');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('daemon', timeout);
if s <> 0 then
raise_application_error(-20010,
'Execute_system: Error while sending. Status = ' || s);
end if;
/* Check for the handshake message. Note that we are now
listening on the pipe which is unique to this session. */
s := dbms_pipe.receive_message(pipe_name, timeout);
if s <> 0 then
raise_application_error(-20011,
'Execute_system: Error while receiving. Status = ' || s);
end if;
/* Get the operating system result code, and display it using
dbms_output.put_line(). */
dbms_pipe.unpack_message(result);
if result <> 'done' then
raise_application_error(-20012,
'Execute_system: Done not received.');
end if;
dbms_pipe.unpack_message(command_code);
dbms_output.put_line('System command executed. result = ' ||
command_code);
return command_code;
end execute_system;
function execute_sql(command varchar2, timeout number default 10)
return number is
s number;
result varchar2(20);
command_code number;
pipe_name varchar2(30);
begin
/* Use uniqe_session_name to generate a unique name for the
return pipe.
We include this as part of the inital message to the daemon,
and it is send along the pipe named 'daemon'. */
pipe_name := dbms_pipe.unique_session_name;
/* Send the 'SQL' command to the daemon. */
dbms_pipe.pack_message('SQL');
dbms_pipe.pack_message(pipe_name);
dbms_pipe.pack_message(command);
s := dbms_pipe.send_message('daemon', timeout);
if s <> 0 then
raise_application_error(-20020,
'Execute_sql: Error while sending. Status = ' || s);
end if;
/* Check for the handshake message. Note that we are now
listening on the pipe which is unique to this session. */
s := dbms_pipe.receive_message(pipe_name, timeout);
if s <> 0 then
raise_application_error(-20021,
'Execute_sql: Error while receiving. Status = ' || s);
end if;
/* Get the result code from the SQL statement, and display
it using dbms_output.put_line(). */
dbms_pipe.unpack_message(result);
if result <> 'done' then
raise_application_error(-20022,
'Execute_sql: Done not received.');
end if;
dbms_pipe.unpack_message(command_code);
dbms_output.put_line('SQL command executed. sqlcode = ' ||
command_code);
return command_code;
end execute_sql;
procedure stop(timeout number default 10) is
s number;
begin
/* Send the 'STOP' command to the daemon. */
dbms_pipe.pack_message('STOP');
s := dbms_pipe.send_message('daemon', timeout);
if s <> 0 then
raise_application_error(-20030,
'Stop: Error while sending. Status = ' || s);
end if;
end stop;
end daemon;
/
/**************************************************************
File : daemon.pc
이것은 deamon listener를 위한 source code.
이 program은 3개의 'daemon commands':
STOP : oracle 로부터 disconnect & exit
SYSTEM : operating system command 처럼 pipe 의 다음 item 을 실행.
SQL : sql 문장처럼 pipe 의 다음 item 을 실행. 또한 sqlcode 를
return
* compile 방법 :
7.x인 경우 => make -f proc.mk EXE=daemon OBJS=daemon.o
PROCFLAGS="sqlcheck=semantics userid=scott/tiger" build
8.x인 경우 => make -f demo_proc.mk EXE=daemon OBJS=daemon.o
PROCFLAGS="sqlcheck=semantics userid=scott/tiger" build
*****************************************************************/
#include <stdio.h>
#include <string.h>
EXEC SQL include sqlca;
EXEC SQL begin declare section;
char *uid = "scott/tiger"; /* User/password to connect to Oracle */
int status; /* Return value for dbms_pipe.send_message
and dbms_pipe.receive_message */
varchar command[20]; /* Daemon command to execute */
varchar value[2000]; /* Value (SQL statement or system command)
associated with previous daemon command */
varchar return_name[30]; /* Name of the pipe on which to send the
results */
EXEC SQL end declare section;
/* This is the error handler for connecting to Oracle.
If we failed on the connection attempt, we need to exit the program. */
void connect_error() {
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL whenever sqlerror continue;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while connecting:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon quitting.\n");
exit(1);
}
/* This is the general error handler. Note that we don't exit the program
in this case. We just print the error and continue. This is because any
errors probably will not affect future operations, and we should keep the
daemon running. This of course depends on the error, and you may want to
change this behavior. */
void sql_error() {
char msg_buffer[512];
int msg_length;
int buffer_size = 512;
EXEC SQL whenever sqlerror continue;
sqlglm(msg_buffer, &buffer_size, &msg_length);
printf("Daemon error while executing:\n");
printf("%.*s\n", msg_length, msg_buffer);
printf("Daemon continuing.\n");
}
main() {
EXEC SQL whenever sqlerror do connect_error();
EXEC SQL connect :uid;
printf("Daemon connected.\n");
EXEC SQL whenever sqlerror do sql_error();
printf("Daemon waiting...\n");
while (1) {
/* Wait for a message to be received, using pipe daemon. */
EXEC SQL EXECUTE
begin
:status := dbms_pipe.receive_message('daemon');
if :status = 0 then
dbms_pipe.unpack_message(:command);
end if;
end;
END-EXEC;
if (status == 0) {
/* At this point, we have successfully received a message.
Now we need to determine which daemon command to execute. */
command.arr[command.len] = '\0';
if (!strcmp((char *)command.arr, "STOP")) {
/* STOP command received. Simply exit the program. */
printf("Daemon exiting.\n");
break;
}
else if (!strcmp((char *)command.arr, "SYSTEM")) {
/* SYSTEM command received. Unpack the next 2 values.
These will be the name of the return pipe, and the command
to pass to the operating system. */
EXEC SQL EXECUTE
begin
dbms_pipe.unpack_message(:return_name);
dbms_pipe.unpack_message(:value);
end;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute system command '%s'\n", value.arr);
/* Execute the command. */
status = system(value.arr);
/* Send a message back to indicate that the command has been
executed. Also send the result of the system command.
Use the pipe passed in from the first message for this. */
EXEC SQL EXECUTE
begin
dbms_pipe.pack_message('done');
dbms_pipe.pack_message(:status);
:status := dbms_pipe.send_message(:return_name);
end;
END-EXEC;
if (status) {
printf("Daemon error while responding to system command.");
printf(" status: %d\n", status);
}
}
else if (!strcmp((char *)command.arr, "SQL")) {
/* SQL command received. Unpack the next 2 values. These will be
the name of the return pipe, and the SQL command to execute. */
EXEC SQL EXECUTE
begin
dbms_pipe.unpack_message(:return_name);
dbms_pipe.unpack_message(:value);
end;
END-EXEC;
value.arr[value.len] = '\0';
printf("Will execute sql command '%s'\n", value.arr);
/* Execute the command. Note that we don't want to go to
the error handler if there is a problem - we just pass
the code back. */
EXEC SQL whenever sqlerror continue;
EXEC SQL EXECUTE IMMEDIATE :value;
status = sqlca.sqlcode;
/* Reset the error handler, and send a message back
to indicate that the command has been executed.
Also send the sqlcode.
Use the pipe passed in from the first message for this. */
EXEC SQL whenever sqlerror do sql_error();
EXEC SQL EXECUTE
begin
dbms_pipe.pack_message('done');
dbms_pipe.pack_message(:status);
:status := dbms_pipe.send_message(:return_name);
end;
END-EXEC;
if (status) {
printf("Daemon error while responding to sql command.");
printf(" status: %d\n", status);
}
}
else {
/* Invalid daemon command received. */
printf("Daemon error: invalid command '%s' received.\n", command.arr);
}
}
else {
/* We get here if an error was received while the daemon was waiting.
If the status = 1, this is a timeout and is probably not a problem.
However, the default timeout for the receive_message function is
1000 days, so unless the daemon is kept running for over 3 years
without receiving a signal, you won't time out. */
printf("Daemon error while waiting for signal.");
printf(" status = %d\n", status);
}
}
EXEC SQL commit work release;
}
Reference Documents
--------------------
<Note:14082.1>
