TECH
QUESTION

자주하는 질문답변 입니다.

Oracle

게시글 보기
작성자 유건데이타 등록일 2015-05-13
제목 CLOB INSERT SELECT 예제
CLOB INSERT SELECT 예제
=======================

Purpose
-------
pro*c에서 clob column에 data를 insert,select 하는
방법을 sample을 통해 알아봅니다.



Example
-------


먼저 다음과 같은 table을 만듭니다.

create table lob_table
( id number(5),
blob_col BLOB default EMPTY_BLOB(),
clob_col CLOB default EMPTY_CLOB(),
nclob_col NCLOB default EMPTY_CLOB(),
bfile_col BFILE default NULL );


INSERT 예제

#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 VARCHAR(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, "bokim/bokim" );
SLEN( oracleid );
TERM( oracleid );

strcpy( action_str, "connecting to d/b" );
EXEC SQL CONNECT :oracleid;

EXEC SQL
INSERT INTO lob_table VALUES ( 1004, NULL, 'kim byung oh',
NULL, NULL);

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


SELECT 예제

#include
#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 VARCHAR */
typedef struct {short len; char arr[READ_SIZE];} vr;

EXEC SQL BEGIN DECLARE SECTION;

VARCHAR oracleid[20];
EXEC SQL TYPE vr IS VARCHAR(READ_SIZE);
vr my_vr;

EXEC SQL END DECLARE SECTION;

FILE *fp;

main()
{
long amount;
long total;
OCIClobLocator *Lob_loc;

EXEC SQL WHENEVER SQLERROR DO o_error(action_str);

strcpy( (char *)oracleid.arr, "bokim/bokim" );
SLEN( oracleid );

strcpy( action_str, "connecting to d/b" );
EXEC SQL CONNECT :oracleid;

fp = fopen("my_clob.dat","w+");

strcpy( action_str, "fetching clob locator" );

EXEC SQL ALLOCATE :Lob_loc;

EXEC SQL DECLARE C CURSOR FOR
select c_lob from lob_table;
EXEC SQL OPEN C;


EXEC SQL WHENEVER NOT FOUND DO break;

for (;;)
{
EXEC SQL FETCH C INTO :Lob_loc;

strcpy( action_str, "reading from clob" );

EXEC SQL LOB OPEN :Lob_loc READ ONLY;
amout = 0;
my_vr.len = READ_SIZE;

EXEC SQL WHENEVER NOT FOUND DO break;
while (true)
{
EXEC SQL LOB READ :amount FROM :Lob_loc INTO :my_vr;
printf("Read %d characters\n", my_vr.len);
fwrite(my_vr.arr, (size_t)my_vr.len, (size_t)1, fp);
}
printf("Read %d characters\n", amount);
EXEC SQL LOB CLOSE :Lob_loc;
}

EXEC SQL FREE :Lob_loc;
EXEC SQL CLOSE C;

fclose(fp);


printf("hahaha3\n");

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


Reference Documents
---------------------
Pro*C/C++ Precompiler Programmer's Guide
Comment
등록된 코멘트가 없습니다.