[펌]PL/SQL에서 Unix System 명령 사용하기

아래 예제는 PL/SQL과 Pro*C를 이용하여 어떻게 유닉스 시스템 명령을 사용하는지를 보여주는 예제 이니 참고 바랍니다.

PL/SQL 프로그램이 데몬 역할을 하는 Pro*C 프로그램을 호출하여 유닉스 명령을 실행 하는 것인데
전 SQL*Plus에서 테스트 했지만 이를 Procedure or Function에서 테스트 해도 가능 할겁니다.

사용자가 만든 PL/SQL(Procedure, Function, Trigger등)에서 DAEMON 패키지의 execute_system 함수를 호출하면 이 함수가 dbms_pipe를 이용하여 pro*c 프로그램과 통신을 하여 pro*c로 만든 프로그램이 Unix Command를 실행시켜 주는 것 입니다.

핵심 역할을 하는 package는 dbms_pipe 라는 패키지인데... 별도로 찾아 보시길 바라며^^;;;

=================================================================================

1. DAEMON이라는 패키지를 만듭니다.

   CREATE OR REPLACE PACKAGE daemon AS
  FUNCTION execute_sql(command VARCHAR2, timeout NUMBER DEFAULT 10) RETURN NUMBER;
  FUNCTION execute_system(command VARCHAR2,timeout NUMBER DEFAULT 10) RETURN NUMBER;
  PROCEDURE stop(timeout NUMBER DEFAULT 10);
END daemon;
/　

2. 패키지의 BODY를 만듭니다.

CREATE OR REPLACE PACKAGE BODY daemon AS
  FUNCTION execute_system(command VARCHAR2,timeout NUMBER DEFAULT 10) RETURN NUMBER IS

    status       NUMBER;
    result       VARCHAR2(20);
    command_code NUMBER;
    pipe_name    VARCHAR2(30);
  BEGIN
    pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;

    DBMS_PIPE.PACK_MESSAGE('SYSTEM');
    DBMS_PIPE.PACK_MESSAGE(pipe_name);
    DBMS_PIPE.PACK_MESSAGE(command);
    status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
    IF status <> 0 THEN
      RAISE_APPLICATION_ERROR(-20010,
        'Execute_system: Error while sending.  Status = ' || status);
    END IF;

    status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);
    IF status <> 0 THEN
      RAISE_APPLICATION_ERROR(-20011,
        'Execute_system: Error while receiving.
         Status = ' || status);
    END IF;

    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

    status       NUMBER;
    result       VARCHAR2(20);
    command_code NUMBER;
    pipe_name    VARCHAR2(30);

  BEGIN
    pipe_name := DBMS_PIPE.UNIQUE_SESSION_NAME;

    DBMS_PIPE.PACK_MESSAGE('SQL');
    DBMS_PIPE.PACK_MESSAGE(pipe_name);
    DBMS_PIPE.PACK_MESSAGE(command);
    status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
    IF status <> 0 THEN
      RAISE_APPLICATION_ERROR(-20020,
        'Execute_sql: Error while sending.  Status = ' || status);
    END IF;

    status := DBMS_PIPE.RECEIVE_MESSAGE(pipe_name, timeout);

    IF status <> 0 THEN
      RAISE_APPLICATION_ERROR(-20021,
        'execute_sql: Error while receiving. 
         Status = ' || status);
    END IF;

    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
    status NUMBER;
  BEGIN
    DBMS_PIPE.PACK_MESSAGE('STOP');
    status := DBMS_PIPE.SEND_MESSAGE('daemon', timeout);
    IF status <> 0 THEN
      RAISE_APPLICATION_ERROR(-20030, 'stop: error while sending.  status = ' || status);
    END IF;
  END stop;
END daemon;
/

3. PL/SQL 프로그램과 통신하여 유닉스 명령을 실행하는 Pro*C 파일을 만들어 컴파일 합니다.
(daemon.pc)

#include <stdio.h>
#include <string.h>

EXEC SQL INCLUDE SQLCA;

EXEC SQL BEGIN DECLARE SECTION;
  char *uid = "scott/tiger";
  int status;
  VARCHAR command[20];
  VARCHAR value[2000];
  VARCHAR return_name[30];
EXEC SQL END DECLARE SECTION;

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);
}

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) {
    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)
    {
      command.arr[command.len] = '\0';
      if (!strcmp((char *) command.arr, "STOP"))
      {
        printf("Daemon exiting.\n");
        break;
      }

      else if (!strcmp((char *) command.arr, "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);

        status = system(value.arr);
        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")) {
        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);

        EXEC SQL WHENEVER SQLERROR CONTINUE;
        EXEC SQL EXECUTE IMMEDIATE :value;
        status = sqlca.sqlcode;

        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
      {
        printf
          ("Daemon error: invalid command '%s' received.\n",
            command.arr);
      }
    }
    else
    {
      printf("Daemon error while waiting for signal.");
      printf("  status = %d\n", status);
    }
  }
  EXEC SQL COMMIT WORK RELEASE;
  exit(0);
}

pro*c 컴파일하는 것은 별도로 설명 하지 않으니 강좌를 참고 하세요...

4. TEST 합니다...

   우선 pro*c로 작성한 daemon을 UNIX에서 실행 합니다...
  
   [TEST:/apps/home/test]daemon
    Daemon connected.
    Daemon waiting...

   SQL*Plus로 로그인 해서...

   SQL> variable v number

   SQL> execute :v := daemon.execute_system('ls -al');

   PL/SQL procedure successfully completed.

     [pro*c로 작성한 데몬에서 확인 하면...]

       Will execute system command 'ls -al'
       총 68

       drwxr-xr-x   2 soss_usr dba          512  4월  8일  19:54 .
       drwxr-xr-x   6 soss_usr dba         1024  4월  8일  19:51 ..
       -rwxr-xr-x   1 soss_usr dba          128  4월  8일  19:54 comp
       -rwxr-xr-x   1 soss_usr dba        25768  4월  8일  19:54 daemon
       -rw-r--r--   1 soss_usr dba         3421  4월  8일  19:53 daemon.pc
       -rw-r-----   1 soss_usr dba          852  4월  8일  19:54 makefile

================================================

어쨋든 ... 오라클 서버의 daemon.execute_system 함수를 호출하기만 하면 되므로

별도의 procedure/function을 만들어 부를 수도 있고, 또는 자바 프로그램(JSP, EJB)에서 부를 수도, 또는 닷넷 Application에서 ADO.NET을 이용하여 부를 수도 있을 것입니다.

 

테스트 해보시기 바랍니다.

 

from www.oraclejava.co.kr