PRO*C에서 LOB DATATYPES에 접근하는 예제

No. 12131

 

PRO*C에서 EMBEDDED SQL STATEMENTS를 사용해서 LOB DATATYPES에 접근하는 예제
==========================================================================

 

Pro*C에서 LOB를 사용하는 방법에는 다음 3가지가 있습니다.

 

(1) PL/SQL blocks에서 DBMS_LOB package를 이용하는 방법

 

(2) OCI function을 이용하는 방법

 

(3) Embedded SQL statements을 이용하는 방법

 

다음은 (3)번째 방법에 대한 pro*c에서 지원하는 명령어들입니다.

 

  o APPEND: Appends lob value at the end of another LOB.
    EXEC SQL LOB APPEND :src TO :dst;

 

  o ASSIGN: Assigns LOB or BFILE locator to another.
    EXEC SQL LOB ASSIGN :src TO :dst;

 

  o CLOSE: Close LOB or BFILE.
    EXEC SQL LOB CLOSE :src;

 

  o COPY: Copy all or part of LOB value into another LOB.
    EXEC SQL LOB COPY :amt FROM :src [AT :src_offset] TO :dst [AT dst_offset];

 

  o CREATE TEMPORARY: Creates a temporary LOB.
    EXEC SQL LOB CREATE TEMPORARY :src;

 

  o ERASE: Erase the given amount of LOB data starting from a given offset.
    EXEC SQL LOB ERASE :amt FROM :src [AT :src_offset];

 

  o FILE CLOSE ALL: Closes all the BFILES open in the current session.
    EXEC SQL LOB FILE CLOSE ALL;

 

  o FILE SET: Set DIRECTORY alias and FILENAME in a BFILE locator.
    EXEC SQL LOB FILE SET :file DIRECTORY = :alias, FILENAME = :filename;

 

  o FREE TEMPORARY: Free the temporary space for the LOB locator.
    EXEC SQL LOB FREE TEMPORARY :src

 

  o LOAD FROM FILE: Copy all or part of BFIL into an internal LOB.
    EXEC SQL LOB LOAD :amt FROM FILE :file [AT :src_offset]
      INTO :dst [AT :dst_offset];

 

  o OPEN: Open a LOB or BFILE for read or read/write.
    EXEC SQL LOB OPEN :src [ READ ONLY | READ WRITE ];

 

  o READ: Reads all or part of LOB or BFILE into a buffer.
    EXEC SQL LOB READ :amt FROM :src [AT :src_offset]
      INTO :buffer [WITH LENGTH :buffer];

 

  o TRIM: Truncates the LOB vlaue.
    EXEC SQL LOB TRIM :src to :newlen;

 

  o WRITE: Writes contents of the buffer to a LOB.
    EXEC SQL LOB WRITE [APPEND] [FIRST | NEXT | LAST | ONE ]
        :amt FROM :buffer [WITH LENGTH :buflen] INTO :dst [AT :dst_offset];

 

  o DESCRIBE: Retrieves the attributes from a LOB.
    EXEC SQL LOB DESCRIBE :src GET attribute1 [{, attributeN}]
      INTO :hv1 [[INDICATOR] :hv_ind1] [{, :hvN [[INDICATOR] :hv_indN] }];

 

    Attributes can be any of the following:

 

    CHUNKSIZE:   chunk size used to store the LOB value
    DIRECTORY:   name of the DIRECTORY alias for BFILE
    FILEEXISTS:  whether BFILE exists or not
    FILENAME:    BFILE name
    ISOPEN:      whether BFILE with this locate is OPEN or not
    ISTEMPORARY: whether specified LOB is temporary or not
    LENGTH:      Length of BLOBs and BFILE in bytes, CLOBs and NCLOBs
                 in characters.

 

 

 

다음은 LOB를 사용하는 sample을 실행하는 방법입니다.

 

1. 먼저 scott user에서 다음을 실행합니다. (create directory를 할 수 있는 권한이
있어야 하며, directory는 사용하시는 환경에 맞도록 수정해 주십시요.)

 

drop table lob_table;
create table lob_table (key number, a_blob BLOB, a_clob CLOB);

 

drop table lobdemo;
create table lobdemo (key number, a_blob BLOB, a_bfile BFILE);

 

drop directory dir_alias;
create directory dir_alias as '/users/app/oracle/product/8.1.7/precomp/demo/proc';

 

insert into lob_table values(1, utl_raw.cast_to_raw('1111111111'), 'aaaaaaaa');

 

commit;

 

2. 다음 코드는 out.gif 파일을 위에서 지정한 directory에 만들고 lob_table의
내용에 들어있는 BLOB의 내용을 저장하는 sample입니다.

 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

 

/* Define constants for VARCHAR lengths. */
#define     UNAME_LEN      20
#define     PWD_LEN        40

 

/* Declare variables.  No declare section is
   needed if MODE=ORACLE. */
VARCHAR     username[UNAME_LEN];  /* VARCHAR is an Oracle-supplied struct */
varchar     password[PWD_LEN];    /* varchar can be in lower case also. */

 

/* The following 3 lines avoid inclusion of oci.h during precompilation
   oci.h is needed only during compilation to resolve calls generated by
   the precompiler
*/
#ifndef ORA_PROC
#include <oci.h>
#endif
#include <sqlca.h>

 

OCIBlobLocator *blob;
OCIClobLocator *clob;

 

FILE *fp;
unsigned int amt, offset = 1;

 

#define MAXBUFLEN 5000
unsigned char buffer[MAXBUFLEN];
EXEC SQL VAR buffer IS RAW(MAXBUFLEN);

 

/* Declare error handling function. */
void sql_error(msg)
    char *msg;
{
    char err_msg[128];
    size_t buf_len, msg_len;

 

    EXEC SQL WHENEVER SQLERROR CONTINUE;

 

    printf("\n%s\n", msg);
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

 

    EXEC SQL ROLLBACK RELEASE;
    exit(EXIT_FAILURE);
}

 

void main()
{

 

/* Connect to ORACLE--
 * Copy the username into the VARCHAR.
 */
    strncpy((char *) username.arr, "SCOTT", UNAME_LEN);

 

/* Set the length component of the VARCHAR. */
    username.len =
      (unsigned short) strlen((char *) username.arr);

 

/* Copy the password. */
    strncpy((char *) password.arr, "TIGER", PWD_LEN);
    password.len =
      (unsigned short) strlen((char *) password.arr);

 

/* Register sql_error() as the error handler. */
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

 

/* Connect to ORACLE.  Program will call sql_error()
 * if an error occurs when connecting to the default database.
 */
    EXEC SQL CONNECT :username IDENTIFIED BY :password;

 

    printf("\nConnected to ORACLE as user: %s\n", username.arr);

 

/* Allocate the LOB host variables and select the BLOB value */
    EXEC SQL ALLOCATE :blob;
    EXEC SQL ALLOCATE :clob;

 

    EXEC SQL SELECT a_blob INTO :blob FROM lob_table WHERE key=1;

 

/* Open external file to which BLOB value should be written */
    fp = fopen("out.gif", "w");
    EXEC SQL WHENEVER NOT FOUND GOTO end_of_lob;

 

    amt = 5000;
    EXEC SQL LOB READ :amt FROM :blob AT :offset INTO :buffer;

 

    fwrite(buffer, MAXBUFLEN, 1, fp);
    EXEC SQL WHENEVER NOT FOUND DO break;

 

/* Use polling method to continue reading the next pieces */
    while (TRUE)
    {
      EXEC SQL LOB READ :amt FROM :blob INTO :buffer;
      fwrite(buffer, MAXBUFLEN, 1, fp);
    }

 

    end_of_lob:
    fwrite(buffer, amt, 1, fp);

 

    printf("\nG'day.\n\n\n");

 

/* Disconnect from ORACLE. */
    EXEC SQL ROLLBACK WORK RELEASE;
    exit(EXIT_SUCCESS);
}

 

3. 다음 코드는 위에서 만든 out.gif file을 lobdemo에 저장하는 sample입니다.

 

#include <stdio.h>
#include <string.h>
#include <stdlib.h>
#include <sqlda.h>
#include <sqlcpr.h>

 

/* Define constants for VARCHAR lengths. */
#define     UNAME_LEN      20
#define     PWD_LEN        40

 

/* Declare variables.  No declare section is
   needed if MODE=ORACLE. */
VARCHAR     username[UNAME_LEN];  /* VARCHAR is an Oracle-supplied struct */
varchar     password[PWD_LEN];    /* varchar can be in lower case also. */

 

/* The following 3 lines avoid inclusion of oci.h during precompilation
   oci.h is needed only during compilation to resolve call generated by
   the precompiler
*/
#ifndef ORA_PROC
#include <oci.h>
#endif

 

#include <sqlca.h>

 

OCIBlobLocator *blob;
OCIBFileLocator *bfile;
char *alias = "DIR_ALIAS";
char *filename = "out.gif";
unsigned int amt = 50;
unsigned int filelen;

 

/* Declare error handling function. */
void sql_error(msg)
    char *msg;
{
    char err_msg[128];
    size_t buf_len, msg_len;

 

    EXEC SQL WHENEVER SQLERROR CONTINUE;

 

    printf("\n%s\n", msg);
    buf_len = sizeof (err_msg);
    sqlglm(err_msg, &buf_len, &msg_len);
    printf("%.*s\n", msg_len, err_msg);

 

    EXEC SQL ROLLBACK RELEASE;
    exit(EXIT_FAILURE);
}

 

void main()
{

 

/* Connect to ORACLE--
 * Copy the username into the VARCHAR.
 */
    strncpy((char *) username.arr, "SCOTT", UNAME_LEN);

 

/* Set the length component of the VARCHAR. */
    username.len =
      (unsigned short) strlen((char *) username.arr);

 

/* Copy the password. */
    strncpy((char *) password.arr, "TIGER", PWD_LEN);
    password.len =
      (unsigned short) strlen((char *) password.arr);

 

/* Register sql_error() as the error handler. */
    EXEC SQL WHENEVER SQLERROR DO sql_error("ORACLE error--\n");

 

/* Connect to ORACLE.  Program will call sql_error()
 * if an error occurs when connecting to the default database.
 */   
   EXEC SQL CONNECT :username IDENTIFIED BY :password;

 

    printf("\nConnected to ORACLE as user: %s\n", username.arr);

 

/* Allocate the LOB locator */
   EXEC SQL ALLOCATE :blob;
   EXEC SQL ALLOCATE :bfile;

 

/* Initialize the DIRECTORY alias of the BFILE and FILENAME */
   EXEC SQL LOB FILE SET :bfile
     DIRECTORY = :alias, FILENAME = :filename;

 

   EXEC SQL INSERT INTO lobdemo  values (1, EMPTY_BLOB(), :bfile);
   EXEC SQL SELECT a_blob, a_bfile INTO :blob, :bfile FROM lobdemo
     WHERE key = 1;

 

   EXEC SQL LOB OPEN :bfile;

 

/* Get the BFILE length */
   EXEC SQL LOB DESCRIBE :bfile
     GET LENGTH INTO :filelen;
   printf("File length is: %d\n", filelen);

 

   amt = filelen;

 

/* Read BFILE and write to BLOB */
   EXEC SQL LOB LOAD :amt FROM FILE :bfile INTO :blob;

 

   EXEC SQL LOB CLOSE :bfile;

 

    printf("\nG'day.\n\n\n");

 

/* Disconnect from ORACLE. */
    EXEC SQL COMMIT WORK RELEASE;
    exit(EXIT_SUCCESS);
}

 

4. 다음은 실행한 결과 입니다.

 

첫번째 sample :

 

Connected to ORACLE as user: SCOTT

 

G'day.
        

 

두번째 sample :

 

Connected to ORACLE as user: SCOTT
File length is: 10

 

G'day.

출처:한국오라클

[출처] PRO*C에서 LOB DATATYPES에 접근하는 예제|작성자 김재범