CLOB INSERT SELECT 예제
http://blog.naver.com/jaebeom/100016554508
No. 11678
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 <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;
/* 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[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);
}
SELECT 예제
#include <oci.h>
#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;
/* 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 Documment
---------------------
Pro*C/C++ Precompiler Programmer's Guide
출처: 한국 oracle
