DBMS_LOB을 이용한 BLOB 데이타 HANDLING 예제 프로그램
==================================================

1) select 하는 예제

- 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
#include

#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;

/* Structure for VARRAW */
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;
}
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);
}


2) insert 하는 예제

- 수행 SQL문
create directory BFILE_DIR as '/mnt3/rctest80/ldt';

- make 방법
$ make -f demo_proc.mk EXE=my_prog OBJS=my_prog.o build \
PROCFLAGS="sqlcheck=full userid=scott/tiger"

- Bfile을 이용하여 데이타를 로드하는 프로그램 예제

#include
#include

#define SLEN(X) ( X.len = strlen((char *)X.arr) )

EXEC SQL INCLUDE SQLCA;
VARCHAR oracleid[20];
FILE *fp;

main()
{
char action_str[30];


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;

EXEC SQL EXECUTE
DECLARE
lobd BLOB;
fils BFILE :=BFILENAME('BFILE_DIR', 'a30.bmp');
amt INTEGER;
BEGIN
insert into lob_tab values (1, empty_blob())
returning c2 into lobd;
DBMS_LOB.FILEOPEN(fils, dbms_lob.file_readonly);
DBMS_LOB.LOADFROMFILE(lobd, fils, dbms_lob.getlength(fils));
DBMS_LOB.FILECLOSE(fils);
END;
END-EXEC;

EXEC SQL COMMIT 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);
}