메뉴 건너뛰기


Developer > DBMS

Oracle BLOB Data 추출 방법

2013.12.28 13:41

푸우 조회 수:11163


- pro*c 2.2에서 compile하는 경우는 package로 DBMS_LOB package 호출을 encapsulate 해서 사용해야 하고, proc 8.0 이상에서 compile하는 경우 DBMS_LOB package를 직접 호출하는 것이 가능하다.

- make 방법
  PRO*C 8.0인 경우 
  $ make -f demo_proc.mk EXE=my_prog OBJS=my_prog.o build \ 
    PROCFLAGS="sqlcheck=full userid=scott/tiger define=V8" 
  PRO*C 2.2: 
  $ setenv TWO_TASK v8_alias 
  $ make -f proc.mk EXE=my_prog OBJS=my_prog.o build \ 
    PROCFLAGS="sqlcheck=full userid=scott/tiger" 

 
- 수행 SQL script
create or replace package blob_it as 
   my_blob blob;
   function get_blob_len return number;
   procedure read_blob(amount in out number, offset in number, 
                       buf in out raw); 
end; 
/
create or replace package body blob_it as 
   function get_blob_len return number is 
   begin 
        return DBMS_LOB.GETLENGTH(my_blob); 
   end;
   procedure read_blob(amount in out number, offset in number, 
                       buf in out raw) is 
   begin 
        DBMS_LOB.READ(my_blob,amount,offset,buf); 
   end; 
end; 
/
drop table lob_tab; 
create table lob_tab (c1 number, c2 blob);
insert into lob_tab values (1, 
             utl_raw.cast_to_raw('AAAAAAAaaaaaaaaaa'));


- Program 예제

#include <stdio.h> 
#include <string.h>
#define TERM(X) ( X.arr[X.len] = '\0' ) 
#define SLEN(X) ( X.len = strlen((char *)X.arr) )
#define READ_SIZE 60
EXEC SQL INCLUDE SQLCA;

typedef struct {short len; char arr[READ_SIZE];} vr;
EXEC SQL BEGIN DECLARE SECTION;
    VARCHAR oracleid[20]; 
    EXEC SQL TYPE vr IS VARRAW(READ_SIZE); 
    vr my_vr;
EXEC SQL END DECLARE SECTION;
FILE *fp;
main() 
{ 
    char  action_str[30]; 
    long  amount; 
    long  offset; 
    short done; 
    long  total; 
 
    EXEC SQL WHENEVER SQLERROR DO o_error(action_str);
    strcpy( (char *)oracleid.arr, "scott/tiger" ); 
    SLEN( oracleid );
    strcpy( action_str, "connecting to d/b" ); 
    EXEC SQL CONNECT :oracleid;
    fp = fopen("my_blob.dat","wb");
    strcpy( action_str, "fetching blob locator" ); 
    EXEC SQL EXECUTE 
                BEGIN 
                   select c2 into blob_it.my_blob from lob_tab 
                   where c1 = 1; 
                   #ifndef V8 
                   :total := blob_it.get_blob_len; 
                   #else 
                   :total := DBMS_LOB.GETLENGTH(blob_it.my_blob); 
                   #endif 
                END; 
             END-EXEC;
    amount = READ_SIZE; 
    offset = 1; 
    done = 0;
    strcpy( action_str, "reading from blob" );
    while (!done) 
    { 
        EXEC SQL EXECUTE 
                    BEGIN 
                        #ifndef V8 
                        blob_it.read_blob(:amount,:offset,:my_vr); 
                        #else 
                        DBMS_LOB.READ(blob_it.my_blob,:amount,:offset,:my_vr); 
                        #endif 
                    END; 
                 END-EXEC;
        offset += amount;
        if (offset >= total) 
            done = 1;
        fwrite(my_vr.arr,(size_t)amount,(size_t)1,fp); 
    } 
    fclose(fp);
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    EXEC SQL ROLLBACK WORK RELEASE; 
}
  
int o_error( action_str ) 
char *action_str; 
{ 
    int i; 
    char error_str[150]; 
 
    EXEC SQL WHENEVER SQLERROR CONTINUE;
    for ( i = 0; i < sqlca.sqlerrm.sqlerrml; i++ ) 
    { 
        error_str[i] = sqlca.sqlerrm.sqlerrmc[i]; 
    } 
    error_str[i] = '\0'; 
    printf( "\nFailed with following Oracle error while %s:\n\n%s", 
            action_str, error_str );
    EXEC SQL ROLLBACK WORK RELEASE; 
    exit(1); 
}