Re: utl_file: to fflush or not to fflush?

From: Joost Mulders <mail_at_j-mulders.demon.nl>
Date: Tue, 06 Nov 2001 00:04:40 +0100
Message-ID: <3BE71B08.960B045A@j-mulders.demon.nl>

> Chapter 18 shows how to do this with C
> external procedures, and Chapter 19 does
> it with Java.

Done this below. Thanks for the suggestion. I stil think utl_file !$*ks and that it is not properly documented that utl_file is not appropriate for binary files.

Note the "Sighs" in the comments.

Thanks, Joost

/*


 * file_io.c: My First OCI for doing file I/O from whithin the database.

 *            It's goal is to provide an alternative to the freakin' 

 *            utl_file package which refuses write large blob's.

 */

#include <oci.h>
#include<stdio.h>
#include<string.h>
#include<sys/types.h>
#include<sys/stat.h>
#include<fcntl.h>

#include<unistd.h>
#include<errno.h>

#define NullValue -1
#define CHUNKSIZ (1048576)

/*

• The access list for files created by creat: rw rw r */ #define ACCESS_MODE (S_IRUSR|S_IWUSR|S_IRGRP|S_IWGRP|S_IROTH)

/*

• Create a new, or truncate an existing file. */ long fiocreat (context, path, path_ind) OCIExtProcContext *context; char *path; short path_ind; { int handle; char *initial_msg; text *error_msg;

 /*
  * open the file. creat if it does not exist and trunc if it exists.
  */

 handle = open ((const char*) path, O_RDWR|O_CREAT|O_TRUNC,ACCESS_MODE);  if (handle > 0) {
  close (handle);
 }
 else {
  initial_msg = strerror (errno);
  error_msg = OCIExtProcAllocCallMemory(context, strlen(initial_msg) + 1);   strcpy ((char *) error_msg, initial_msg);   OCIExtProcRaiseExcpWithMsg (context, 20002, error_msg, 0);  }
}

/*

• write to a file in append mode. The file should exist here. *
• Sigh. The utl_file package cannot be used to write large binary files,
• that's why we're using OCI in the first place.
• Then, with OCI, it is not allowed to pass a pointer or lob locator as an
• OCI parameter. *
• Two solutions:
• 1. In PL/SQL, select the blob, convert to varchar2 by rawtohex(blob) and
• then again decode to binary in the OCI function. Sloooowww.
• 2. Select the lob locator in the OCI function and write -without converion-
• to a file. That's the approach here. This will introduce table and column
• names in the C code. Again. Sigh. No. We'll pass an sql string as a
• parameter to keep the column and table names out of the OCI functions. *
• See: $ORACLE_HOME/rdbms/demo/cdemlb.c * */

long fiowrite (context, path, sql_select_lob)  OCIExtProcContext *context;
 char *sql_select_lob;
{
 int handle;
 int i ;
 unsigned char byte0, byte1;
 char *initial_msg;
 text *error_msg;

 OCIEnv *envhp = (OCIEnv *) 0;                /* env. handle */
 OCISvcCtx *svchp = (OCISvcCtx *) 0;          /* service handle */
 OCIError *errhp = (OCIError *) 0;            /* error handle */
 OCIStmt *stmthp = (OCIStmt *) 0;             /* statement handle */


 /*

• Magically, get some environment handles from our context. */ OCIExtProcGetEnv (context, &envhp, &svchp, &errhp);

 /*
  * Open the file. It should exist here
  */

 if ((handle = open(path, O_RDWR|O_APPEND)) < 0) {   initial_msg = (text *)"Cannot open file ";   error_msg = OCIExtProcAllocCallMemory(context,     strlen(path) + strlen(initial_msg) + 1);   strcpy((char *)error_msg, (char *)initial_msg);   strcat((char *)error_msg, path);
  OCIExtProcRaiseExcpWithMsg(context, 20001, error_msg, 0);  }
 else {
  OCILobLocator *lobloc;
  OCIDefine *defnp1;
  ub1 buffer [CHUNKSIZ];
  ub4 amount, position,length;
  sword status;

  /*
   * Allocate a lob locator.
   */
  status = OCIDescriptorAlloc ((dvoid*) envhp, (dvoid**) &lobloc, 
                               (ub4) OCI_DTYPE_LOB, (size_t) 0, (dvoid**) 0);

  if (status) {
   printf ("OCIDescriptorAlloc: %d\n", status);    return status;
  }

  /*
   * Select the lob into the locator. This seems to be a four step approach !
   *
   * 0. Allocate a statement handle (?)
   */
  status = OCIHandleAlloc((dvoid *) envhp, (dvoid **)&stmthp,
                          (ub4) OCI_HTYPE_STMT, (size_t) 0, (dvoid **)0);

  if (status) {
   printf ("OCIHandleAlloc: %d\n", status);    return status;
  }

  /*
   * 1. This call is magic. I think it wakes up the oracle.
   */
  status = OCIStmtPrepare (stmthp, errhp, sql_select_lob, 
                          (ub4) strlen ( (char *) sql_select_lob),
                          (ub4) OCI_NTV_SYNTAX, (ub4) OCI_DEFAULT);

  if (status) {
   printf ("OCIStmtPrepare: %d\n", status);    return status;
  }  

  /*
   * 2. Associate column 0 with our lob locator
   */ 
  status = OCIDefineByPos (stmthp, &defnp1, errhp, (ub4) 1,
                                  (dvoid *)&lobloc, (sb4)0, 
                                  (ub2) SQLT_BLOB,(dvoid *) 0, 
                                  (ub2 *) 0, (ub2 *) 0, (ub4) OCI_DEFAULT);

  if (status) {
   printf ("OCIDefineByPos: %d\n", status);    return status;
  }  

  /*
   * 3. Execute the select and fetch one row 
   */
  status = OCIStmtExecute (svchp, stmthp, errhp, (ub4) 1, (ub4) 0,
                                 (CONST OCISnapshot*) 0, (OCISnapshot*) 0,  
                                 (ub4) OCI_DEFAULT);

  if (status) {
   printf ("OCIStmtExecute: %d\n", status);    return status;
  }

  /*
   * We now have a blob in lobloc. Get the totol length of the freakin' thing, 
   * read in the chunks and append those to a file. Sigh^16.
   */

  status = OCILobGetLength (svchp, errhp, lobloc, &length);   if (status) {
   printf ("OCILobGetLength: %d\n", status);   }   

  for (position = 1; position < length; position += amount) {    if ( (length - position) > CHUNKSIZ)
    amount = CHUNKSIZ;
   else
    amount = length - position + 1;

   /*
    *  I don't quite understand the streaming mechanism.. and I would
    *  be quite happy if this just works. 
    */
   status = OCILobRead (svchp, errhp, lobloc, &amount, position, 
                       (dvoid *) buffer, sizeof (buffer), (dvoid *)0, 
                       (sb4 (*)(dvoid *, dvoid *, ub4, ub1)) 0,
                       (ub2) 0, (ub1) SQLCS_IMPLICIT);

   if (status) {
    printf ("OCILobRead: %d\n", status);     return status;
   }

   write (handle, buffer, amount);
  } /* for (pos.. */

  /* 
   * Done (hopefully), clean up the mess..
   */

  close(handle);
  (void) OCIDescriptorFree((dvoid *) lobloc, (ub4) OCI_DTYPE_LOB);   (void) OCIHandleFree((dvoid *) stmthp, (ub4) OCI_HTYPE_STMT);

 } /* elze file is open ..*/
} Received on Mon Nov 05 2001 - 17:04:40 CST