High Performance For Oracle Text Unload Or Export With Ociuldr
High performance for Oracle text unload or export with ociuldr

Links: http://www.dbatools.net/mytools/ociuldr_perf_tuning.html

ociuldr is a free utility used to unload Oracle rows to text file, which can be used to prepare data for data warehouse, or do the data migration from Oracle to other databases, or moving data between different character set Oracle databases. To get the maximum performance, take the following steps.

  • Tuning the query

is the most important step, for massive data unload, you can use oracle parallel query to speed up the query. Else please check the execution plan, make sure it use the best plan. I will not tell you how to do tuning here.

  • Tuning the network speed.

You can run ociuldr at a machine with good connection to the database host. Or tuning Oracle network TDU and SDU parameter to use large network packet size. Run on the database server always get the best performance of unloading, but it may add extra load to the database server.

  • Setting the proper performance parameters value.

You can set the multiple block read parameter of Oracle by "read=blocks" option, and set the sort area size for sorting with "sort=MB" option, or set the hash area size for hash join with "hash=MB" option, and the serial direct read option (tell oracle use direct path read method when accessing full table) with "serial=1" option, direct path read can avoid Oracle read the block into database buffer cache and flush existing blocks out to avoid the performance impacting of other sessions.

Choose the right array fetch size with "array=rows" option. Usually large array size will help to improve the unload performance.

source:

#ifdef RCSID
static char *RCSid =
   "$Header: ociuldr.c 2005.05.19 Lou Fangxin, http://www.anysql.net $ ";
#endif /* RCSID */
 
/*
   NAME
     ociuldr.c - Using OCI to rewrite the unload script.
 
   MODIFIED   (MM/DD/YY)
    Lou Fangxin    2005.05.19 -  Initial write.
    Lou Fangxin    2005.05.22 -  Add File Option to command
    Lou Fangxin    2005.05.25 -  Enable login as sysdba
*/
 
#include <stdio.h>
#include <ctype.h>
#include <string.h>
#include <stdlib.h>
#include <time.h>
 
#include <oratypes.h>
#include <ocidfn.h>
#ifdef __STDC__
#include <ociapr.h>
#else
#include <ocikpr.h>
#endif
#include <ocidem.h>
 
#include <oci.h>
 
/* Constants used in this program. */
#define MAX_SELECT_LIST_SIZE    1024
#define MAX_ITEM_BUFFER_SIZE    33
#define PARSE_NO_DEFER           0
#define PARSE_V7_LNG             2
#define MAX_BINDS               12
#define MAX_SQL_IDENTIFIER      31
#define DEFAULT_BUFFER_SIZE     524288
#define ROW_BATCH_SIZE          500000
 
#if defined(_WIN32)
#define STRNCASECMP memicmp
#else
#define STRNCASECMP strncasecmp
#endif
 
#define  MIN(a,b) ((a) > (b) ? (b) : (a))
 
/*
#define  MAX(a,b) ((a) < (b) ? (b) : (a))
*/
 
struct COLUMN
{
    /* Describe */
    sb4             dbsize;
    sb2             dbtype;
    sb1             buf[MAX_ITEM_BUFFER_SIZE];
    sb4             buflen;
    sb4             dsize;
    sb2             precision;
    sb2             scale;
    sb2             nullok;
 
    /*+ Fetch */
    ub1             *colbuf;
    sb2             *indp;
    ub2             *col_retlen;
    ub2             *col_retcode;
 
    /*+ Point to next column */
    struct          COLUMN *next;
};
 
ub4     DEFAULT_ARRAY_SIZE =   50;
ub4     DEFAULT_LONG_SIZE = 32768;
ub4     hda[HDA_SIZE/(sizeof(ub4))];
 
FILE *fp_log = NULL;
int return_code = 0;
 
sword LogonDB(Lda_Def *lda,text *username, text *password,ub4 mode)
{
    sword n;
 
    if (olog(lda, (ub1 *)hda, username, -1, password, -1,
                 (text *) 0, -1, mode))
    {
        fprintf((fp_log == NULL?stdout:fp_log),"Cannot connect as %s.\n", username);
    }
    else
    {
        return 0;
    }
    fprintf((fp_log == NULL?stdout:fp_log),"Connection failed.  Exiting...\n");
    return -1;
}
 
void SQLError(Lda_Def *lda, Cda_Def *cda)
{
    text msg[512];
    sword n;
 
    n = oerhms(lda, cda->rc, msg, (sword) sizeof (msg));
    fprintf((fp_log == NULL?stdout:fp_log),"%.*s", n, msg);
}
 
sword CreateSQL(Lda_Def *lda, Cda_Def *cda)
{
    if (oopen(cda, lda, (text *) 0, -1, -1, (text *) 0, -1))
    {
        fprintf((fp_log == NULL?stdout:fp_log),"Error opening cursor [OOPEN].\n");
        return -1;
    }
    return 0;
}
 
sword ParseSQL(Lda_Def *lda, Cda_Def *cda, text *sql_statement)
{
    if (oparse(cda, (text *) sql_statement, (sb4) -1,
               (sword) PARSE_NO_DEFER, (ub4) PARSE_V7_LNG))
    {
        SQLError(lda,cda);
        return -1;
    }
    return 0;
}
 
sword BindValue(Lda_Def *lda, Cda_Def *cda, text *vname, ub1 *val)
{
 
  if (obndrv(cda, vname, -1, 
            (ub1 *)val,  -1,
            VARCHAR2_TYPE,-1, (sb2 *) 0, (text *) 0, -1, -1))
  {
     SQLError(lda,cda);
     return -1;
  }
  return 0;
}
 
sword executeStatement(Lda_Def *lda, Cda_Def *cda)
{
     if (oexec(cda))
     {
        SQLError(lda,cda);
        return -1;
     }
     return 0;
}
 
sword getColumns(FILE *fpctl, Lda_Def *lda,Cda_Def *cda, struct COLUMN *collist)
{
    sword col;
    struct COLUMN *tempcol;
    struct COLUMN *nextcol;
    ub1 *buf;
 
    nextcol = collist;
 
    /* Describe the select-list items. */
    if(fpctl != NULL) fprintf(fpctl,"(\n");
    for (col = 0; col < MAX_SELECT_LIST_SIZE; col++)
    {
        tempcol = (struct COLUMN *) malloc(sizeof(struct COLUMN));
        tempcol-> indp        = (sb2 *)malloc(DEFAULT_ARRAY_SIZE * sizeof(sb2));
        tempcol-> col_retlen  = (ub2 *)malloc(DEFAULT_ARRAY_SIZE * sizeof(ub2));
        tempcol-> col_retcode = (ub2 *)malloc(DEFAULT_ARRAY_SIZE * sizeof(ub2));
 
        tempcol->next = NULL;
        tempcol->colbuf = NULL;
 
        tempcol->buflen = MAX_ITEM_BUFFER_SIZE;
        if (odescr(cda, col + 1, &(tempcol->dbsize),
                   &(tempcol->dbtype), &(tempcol->buf[0]),
                   &(tempcol->buflen), &(tempcol->dsize),
                   &(tempcol->precision), &(tempcol->scale),
                   &(tempcol->nullok)))
        {
            if(fpctl != NULL) fprintf(fpctl,"\n");
            free(tempcol);
            /* Break on end of select list. */
            if (cda->rc == VAR_NOT_IN_LIST)
                break;
            else
            {
                SQLError(lda,cda);
                return -1;
            }
        }
 
        if(col) 
        {
          if(fpctl != NULL) fprintf(fpctl,",\n");
        }
 
        nextcol->next = tempcol;
        nextcol=tempcol;
 
        nextcol->buf[nextcol->buflen]='\0';
 
        switch(nextcol->dbtype)
        {
            case DATE_TYPE:
                nextcol->dsize=20;
                nextcol->dbtype=STRING_TYPE;
                if(fpctl != NULL)
                   fprintf(fpctl,"  %s DATE \"YYYY-MM-DD HH24:MI:SS\"", nextcol->buf );
                break;
            case 180: /* TIMESTAMP */
                nextcol->dsize=30;
                nextcol->dbtype=STRING_TYPE;
                if(fpctl != NULL)
                   fprintf(fpctl,"  %s TIMESTAMP \"YYYY-MM-DD HH24:MI:SSXFF\"", nextcol->buf );
                break;
            case 181: /* TIMESTAMP WITH TIMEZONE */
                nextcol->dsize=36;
                nextcol->dbtype=STRING_TYPE;
                if(fpctl != NULL)
                   fprintf(fpctl,"  %s TIMESTAMP WITH TIME ZONE \"YYYY-MM-DD HH24:MI:SSXFF TZH:TZM\"", nextcol->buf );
                break;
            case 24:  /* LONG RAW */
            case 113: /* BLOB */
               nextcol->dsize=DEFAULT_LONG_SIZE;
               nextcol->dbtype=24;
               if(fpctl != NULL)
                  fprintf(fpctl,"  %s CHAR(%d) ", nextcol->buf, 2 * DEFAULT_LONG_SIZE);
               break;
            case ROWID_TYPE:
                nextcol->dsize=20;
                nextcol->dbtype=STRING_TYPE;
                if(fpctl != NULL)
                  fprintf(fpctl, "  %s CHAR(%d)", nextcol->buf,20);
                break;
            case NUMBER_TYPE:
               nextcol->dsize=40;
               nextcol->dbtype=STRING_TYPE;
               if(fpctl != NULL)
                  fprintf(fpctl,"  %s CHAR(%d)", nextcol->buf,40);
               break;
            case 112: /* CLOB */
            case 114: /* BFILE */
               nextcol->dsize=DEFAULT_LONG_SIZE;
               nextcol->dbtype=STRING_TYPE;
               if(fpctl != NULL)
                  fprintf(fpctl,"  %s CHAR(%d)", nextcol->buf,DEFAULT_LONG_SIZE);
               break;
            default:
               nextcol->dbtype=STRING_TYPE;
           if (nextcol->dsize>4000) nextcol->dsize = 4000;
           if (nextcol->dsize==0)  nextcol->dsize = 4000;
               if(fpctl != NULL)
                  fprintf(fpctl,"  %s CHAR(%d)", nextcol->buf,(nextcol->dsize==0?DEFAULT_LONG_SIZE:nextcol->dsize));
               break;
        }
        /* Set for long type column */
        if (nextcol->dsize > DEFAULT_LONG_SIZE || nextcol->dsize == 0)  
            nextcol->dsize = DEFAULT_LONG_SIZE;
        /* add one more byte to store the ternimal char of string */
        nextcol->dsize ++;
 
        fprintf((fp_log == NULL?stdout:fp_log),"%8u bytes allocated for column %s (%d) \n",
                 DEFAULT_ARRAY_SIZE * nextcol->dsize,nextcol->buf,col+1);
 
        nextcol->colbuf = malloc(DEFAULT_ARRAY_SIZE * nextcol->dsize);
        memset(nextcol->colbuf,0,DEFAULT_ARRAY_SIZE * nextcol->dsize);
 
        if (odefin(cda, col + 1,
                   nextcol->colbuf, nextcol->dsize, nextcol->dbtype,
                   -1, nextcol->indp, (text *) 0, -1, -1,
                   nextcol->col_retlen,nextcol->col_retcode))
        {
            SQLError(lda,cda);
            return -1;
        }
    }
    if(fpctl != NULL)
       fprintf(fpctl,")\n");
    fputs("\n",(fp_log == NULL?stdout:fp_log));
    return col;
}
 
void  printRowInfo(ub4 row)
{
    time_t now = time(0);
    struct tm *ptm = localtime(&now);
    fprintf((fp_log == NULL?stdout:fp_log),"%8u rows exported at %04d-%02d-%02d %02d:%02d:%02d\n",
                row,
        ptm->tm_year + 1900,
        ptm->tm_mon + 1,
        ptm->tm_mday,
        ptm->tm_hour,
        ptm->tm_min,
        ptm->tm_sec);
        fflush((fp_log == NULL?stdout:fp_log));
}
 
FILE *openFile(const text *fname, text tempbuf[], int batch)
{
   FILE *fp=NULL;
   int i, j, len;
   time_t now = time(0);
   struct tm *ptm = localtime(&now);   
 
   len = strlen(fname);
   j = 0;
   for(i=0;i<len;i++)
   {
      if (*(fname+i) == '%')
      {
          i++;
      if (i < len)
      {
            switch(*(fname+i))
            {
              case 'Y':
              case 'y':
                j += sprintf(tempbuf+j, "%04d", ptm->tm_year + 1900);
        break;
              case 'M':
              case 'm':
                j += sprintf(tempbuf+j, "%02d", ptm->tm_mon + 1);
        break;
              case 'D':
              case 'd':
                j += sprintf(tempbuf+j, "%02d", ptm->tm_mday);
        break;
              case 'W':
              case 'w':
                j += sprintf(tempbuf+j, "%d", ptm->tm_wday);
        break;
              case 'B':
              case 'b':
                j += sprintf(tempbuf+j, "%d", batch);
        break;
              default:
                tempbuf[j++] = *(fname+i);
        break;
            }
          }
      }
      else
      {
         tempbuf[j++] = *(fname+i);
      }
   }
   tempbuf[j]=0;
   if (tempbuf[0] == '+')
       fp = fopen(tempbuf+1, "ab+");
   else
       fp = fopen(tempbuf, "wb+");
   return fp;
}
 
void printRow(text *fname, Lda_Def *lda,Cda_Def *cda,struct COLUMN *col, text *field, int flen,text *record, int rlen, int batch, int header)
{
    int bcount=1,j=0;
    sword r,rows,colcount,c;
    struct COLUMN *p;
    ub4 trows;
    FILE *fp;
    text tempbuf[512];
 
    struct COLUMN *cols[1024];
 
    trows=0;
    colcount = 0;
 
    p = col->next;
    while(p != NULL)
    {
        cols[colcount] = p;
        p=p->next;
        colcount ++;
    }
 
    memset(tempbuf,0,512);
 
    if((fp = openFile(fname,tempbuf,bcount)) == NULL) 
    {
       fprintf((fp_log == NULL?stdout:fp_log),"ERROR -- Cannot write to file : %s\n", tempbuf);
       return_code = 6;
       return;
    }
 
    if (header)
    {
       for(c=0;c<colcount;c++)
       {
          fprintf(fp,"%s",cols[c]->buf);
          if (c < colcount - 1) 
             fwrite(field,flen,1,fp);
       }
       fwrite(record,rlen,1,fp);
    }
 
    printRowInfo(trows);
    for (;;)
    {
        rows = DEFAULT_ARRAY_SIZE;
        if (ofen(cda,DEFAULT_ARRAY_SIZE))
        {
            if (cda->rc != NO_DATA_FOUND)
            {
                 return_code = 7;
                 SQLError(lda,cda);
            }
            rows = cda->rpc % DEFAULT_ARRAY_SIZE;
        }
        for(r=0;r<rows;r++)
        {
           for(c=0;c<colcount;c++)
           {
              if (*(cols[c]->indp+r) >= 0)
              {
                 if (cols[c]->dbtype != 24)
                 {
                    fwrite(cols[c]->colbuf+(r* cols[c]->dsize),*(cols[c]->col_retlen+r),1,fp);
                 }
                 else
                 {
                    /* fprintf(fp, "%010d", 2 * *(cols[c]->col_retlen+r)); */
                    /* fwrite(cols[c]->colbuf+(r* cols[c]->dsize),*(cols[c]->col_retlen+r),1,fp); */
                    for(j=0;j < *(cols[c]->col_retlen+r); j++)
                    {
                       fprintf(fp, "%02x", cols[c]->colbuf[r * cols[c]->dsize + j]);
                    }
                 }
              }
              if (c < colcount - 1)
                  fwrite(field,flen,1,fp);
           }
           fwrite(record,rlen,1,fp);
           trows ++;
           if (trows % ROW_BATCH_SIZE  == 0)
           {
              printRowInfo(trows);
              if(batch && ((trows / ROW_BATCH_SIZE) % batch) == 0)
              {
                 fprintf((fp_log == NULL?stdout:fp_log),"         output file %s closed at %u rows.\n", tempbuf, trows);
                 fclose(fp);
                 bcount ++;
                 memset(tempbuf,0,512);
                 if((fp = openFile(fname,tempbuf,bcount)) == NULL) 
                 {
                   fprintf((fp_log == NULL?stdout:fp_log),"ERROR -- Cannot write to file : %s\n", tempbuf);
                   return_code = 6;
                   return;
                 }
                 if (header)
                 {
                    for(c=0;c<colcount;c++)
                    {
                       fprintf(fp,"%s",cols[c]->buf);
                       if (c < colcount - 1)
                          fwrite(field,flen,1,fp);
                    }
                    fwrite(record,rlen,1,fp);
                 }
                 trows = 0;
              }
           }
        }
        if (rows < DEFAULT_ARRAY_SIZE) break;
    }
    if (trows % ROW_BATCH_SIZE != 0)
       printRowInfo(trows);
    fclose(fp);
    fprintf((fp_log == NULL?stdout:fp_log),"         output file %s closed at %u rows.\n\n", tempbuf, trows);
    fflush((fp_log == NULL?stdout:fp_log));
}
 
void freeColumn(struct COLUMN *col)
{
   struct COLUMN *p,*temp;
   p=col->next;
 
   col->next = NULL;
   while(p!=NULL)
   {
     free(p->colbuf);
     free(p->indp);
     free(p->col_retlen);
     free(p->col_retcode);
     temp=p;
     p=temp->next;
     free(temp);
   }
}
 
ub1  getHexIndex(char c)
{
   if ( c >='0' && c <='9') return c - '0';
   if ( c >='a' && c <='f') return 10 + c - 'a';
   if ( c >='A' && c <='F') return 10 + c - 'A';
   return 0;
}
 
int convertOption(const ub1 *src, ub1* dst, int mlen)
{
   int i,len,pos;
   ub1 c,c1,c2;
 
   i=pos=0;
   len = strlen(src);
 
   while(i<MIN(mlen,len))
   {
      if ( *(src+i) == '0')
      {
          if (i < len - 1)
          {
             c = *(src+i + 1);
             switch(c)
             {
                 case 'x':
                 case 'X':
                   if (i < len - 3)
                   {
                       c1 = getHexIndex(*(src+i + 2));
                       c2 = getHexIndex(*(src+i + 3));
                       *(dst + pos) = (ub1)((c1 << 4) + c2);
                       i=i+2;
                   }
                   else if (i < len - 2)
                   {
                       c1 = *(src+i + 2);
                       *(dst + pos) = c1;
                       i=i+1;
                   }
                   break;
                 default:
                   *(dst + pos) = c;
                   break;
             }
             i = i + 2;
          }
          else
          {
            i ++;
          }
      }
      else
      {
          *(dst + pos) = *(src+i);
          i ++;
      }
      pos ++;
   }
   *(dst+pos) = '\0';
   return pos;
}
 
int main(int argc, char *argv[])
{
 
    struct COLUMN col;
 
    sword n,i,argcount=0;
    Lda_Def conn;
    Cda_Def stmt;
 
    ub1 *iobuf;
    text tempbuf[1024];
    text username[132]="";
    text ctlfname[256]="";
    text tabname[132]="";
    text tabmode[132]="INSERT";
    text query[32768]="";
    text field[132]=",";
    text logfile[256]="";
    text record[132]="\n";
    text sqlfname[255]="";
    text fname[255]="uldrdata.txt";
    text argname[128][20];
    ub1  argval [128][20];
 
    int  buffer= 16777216;
    int  hsize = 0;
    int  ssize = 0;
    int  bsize = 0;
    int  serial= 0;
    int  trace = 0;
    int  batch = 0;
    int  header= 0;
 
    ub4  lmode;
 
    int flen,rlen;
 
    FILE *fp;
    FILE *fpctl;
 
    flen = rlen = 1;
    col.next=NULL;
    lmode = OCI_LM_DEF;
 
    for(i=0;i<argc;i++)
    {
      if (STRNCASECMP("user=",argv[i],5)==0)
      {
          memset(username,0,132);
          memcpy(username,argv[i]+5,MIN(strlen(argv[i]) - 5,131));
      }
      else if (STRNCASECMP("query=",argv[i],6)==0)
      {
          memset(query,0,8192);
          memcpy(query,argv[i]+6,MIN(strlen(argv[i]) - 6,8191));
      }     
      else if (STRNCASECMP("field=",argv[i],6)==0)
      {
          memset(field,0,132);
          flen=convertOption(argv[i]+6,field,MIN(strlen(argv[i]) - 6,131));
      }     
      else if (STRNCASECMP("sql=",argv[i],4)==0)
      {
          memset(sqlfname,0,132);
          memcpy(sqlfname,argv[i]+4,MIN(strlen(argv[i]) - 4,254));
      }
      else if (STRNCASECMP("record=",argv[i],7)==0)
      {
          memset(record,0,132);
          rlen=convertOption(argv[i]+7,record,MIN(strlen(argv[i]) - 7,131));
      }     
      else if (STRNCASECMP("file=",argv[i],5)==0)
      {
          memset(fname,0,132);
          memcpy(fname,argv[i]+5,MIN(strlen(argv[i]) - 5,254));
      }     
      else if (STRNCASECMP("log=",argv[i],4)==0)
      {
          memset(logfile,0,256);
          memcpy(logfile,argv[i]+4,MIN(strlen(argv[i]) - 4,254));
      }     
      else if (STRNCASECMP("table=",argv[i],6)==0)
      {
          memset(tabname,0,132);
          memcpy(tabname,argv[i]+6,MIN(strlen(argv[i]) - 6,128));
      }     
      else if (STRNCASECMP("mode=",argv[i],5)==0)
      {
          memset(tabmode,0,132);
          memcpy(tabmode,argv[i]+5,MIN(strlen(argv[i]) - 5,128));
      }     
      else if (STRNCASECMP("head=",argv[i],5)==0)
      {
          memset(tempbuf,0,132);
          memcpy(tempbuf,argv[i]+5,MIN(strlen(argv[i]) - 5,128));
          header = 0;
          if (STRNCASECMP(tempbuf,"YES",3) == 0) header = 1;
          if (STRNCASECMP(tempbuf,"ON",3) == 0) header = 1;
      }     
      else if (STRNCASECMP("sort=",argv[i],5)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+5,MIN(strlen(argv[i]) - 5,254));
          ssize = atoi(tempbuf);
          if (ssize < 0) ssize = 0;
          if (ssize > 512) ssize = 512;
      }     
      else if (STRNCASECMP("buffer=",argv[i],7)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+7,MIN(strlen(argv[i]) - 7,254));
          buffer = atoi(tempbuf);
          if (buffer < 8) buffer = 8;
          if (ssize > 100) buffer = 100;
          buffer = buffer * 1048576;
      }     
      else if (STRNCASECMP("long=",argv[i],5)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+5,MIN(strlen(argv[i]) - 5,254));
          DEFAULT_LONG_SIZE = atoi(tempbuf);
          if (DEFAULT_LONG_SIZE < 100) DEFAULT_LONG_SIZE = 100;
          if (DEFAULT_LONG_SIZE > 32767) DEFAULT_LONG_SIZE = 32767;
      }
      else if (STRNCASECMP("array=",argv[i],6)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+6,MIN(strlen(argv[i]) - 6,254));
          DEFAULT_ARRAY_SIZE = atoi(tempbuf);
          if (DEFAULT_ARRAY_SIZE < 5) DEFAULT_ARRAY_SIZE = 5;
          if (DEFAULT_ARRAY_SIZE > 2000) DEFAULT_ARRAY_SIZE = 2000;
      }
      else if (STRNCASECMP("hash=",argv[i],5)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+5,MIN(strlen(argv[i]) - 5,254));
          hsize = atoi(tempbuf);
          if (hsize < 0) hsize = 0;
          if (hsize > 512) hsize = 512;
      }     
      else if (STRNCASECMP("read=",argv[i],5)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+5,MIN(strlen(argv[i]) - 5,254));
          bsize = atoi(tempbuf);
          if (bsize < 0) bsize = 0;
          if (bsize > 512) bsize = 512;
      }     
      else if (STRNCASECMP("batch=",argv[i],6)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+6,MIN(strlen(argv[i]) - 6,254));
          batch = atoi(tempbuf);
          if (batch < 0) batch = 0;
          if (batch == 1) batch = 2;
      }     
      else if (STRNCASECMP("serial=",argv[i],7)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+7,MIN(strlen(argv[i]) - 7,254));
          serial = atoi(tempbuf);
      }     
      else if (STRNCASECMP("trace=",argv[i],6)==0)
      {
          memset(tempbuf,0,1024);
          memcpy(tempbuf,argv[i]+6,MIN(strlen(argv[i]) - 6,254));
          trace = atoi(tempbuf);
      }     
      else if (STRNCASECMP("-si",argv[i],3) == 0 && strlen(argv[i])==3)
      {
          lmode = lmode | OCI_SYSDBA;
          memset(username,0,132);
          username[0]='/';
      }
    }
 
    if (strlen(sqlfname) > 0)
    {
       fp = fopen(sqlfname,"r+");
       if (fp != NULL)
       {
           while(!feof(fp))
           {
               memset(tempbuf,0,1024);
               fgets(tempbuf,1023,fp);
               strcat(query,tempbuf);
               strcat(query," ");
           }
           fclose(fp);
       }
    }
 
    if (strlen(username)==0 || strlen(query)==0)
    {
       printf("Usage: %s user=... query=... field=... record=... file=...\n",argv[0]);
       printf("(@) Copyright Lou Fangxin 2004/2005, all rights reserved.\n");
       printf("Notes:\n");
       printf("       -si   = enable logon as SYSDBA\n");
       printf("       user  = username/password@tnsname\n");
       printf("       sql   = SQL file name\n");
       printf("       query = select statement\n");
       printf("       field = seperator string between fields\n");
       printf("       record= seperator string between records\n");
       printf("       file  = output file name(default: uldrdata.txt)\n");
       printf("       read  = set DB_FILE_MULTIBLOCK_READ_COUNT at session level\n");
       printf("       sort  = set SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE at session level (UNIT:MB) \n");
       printf("       hash  = set HASH_AREA_SIZE at session level (UNIT:MB) \n");
       printf("       serial= set _serial_direct_read to TRUE at session level\n");
       printf("       trace = set event 10046 to given level at session level\n");
       printf("       table = table name in the sqlldr control file\n");
       printf("       mode  = sqlldr option, INSERT or APPEND or REPLACE or TRUNCATE \n");
       printf("       log   = log file name, prefix with + to append mode\n");
       printf("       long  = maximum long field size\n");
       printf("       array = array fetch size\n");
       printf("       buffer= sqlldr READSIZE and BINDSIZE, default 16 (MB)\n");
       printf("\n");
       printf("  for field and record, you can use '0x' to specify hex character code,\n");
       printf("  \\r=0x%02x \\n=0x%02x |=0x%0x ,=0x%02x \\t=0x%02x\n",'\r','\n','|',',','\t');
       exit(0);
    }
 
    if (DEFAULT_ARRAY_SIZE * DEFAULT_LONG_SIZE > 104857600)
    {
        DEFAULT_ARRAY_SIZE = 104857600/DEFAULT_LONG_SIZE;
        if (DEFAULT_ARRAY_SIZE < 5) DEFAULT_ARRAY_SIZE=5;
    }
 
    if (strlen(logfile))
    {
       fp_log = openFile(logfile,tempbuf,0);
    }
 
    if(LogonDB(&conn,username,NULL,lmode))
    {
       if (fp_log != NULL) fclose(fp_log);
       exit(1);
    }
 
    if(CreateSQL(&conn,&stmt) == 0)
    { 
 
        ParseSQL(&conn,&stmt,"ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH24:MI:SS'");
        executeStatement(&conn,&stmt);
        ParseSQL(&conn,&stmt,"ALTER SESSION SET NLS_TIMESTAMP_FORMAT='YYYY-MM-DD HH24:MI:SSXFF'");
        executeStatement(&conn,&stmt);
        ParseSQL(&conn,&stmt,"ALTER SESSION SET NLS_TIMESTAMP_TZ_FORMAT='YYYY-MM-DD HH24:MI:SSXFF TZH:TZM'");
        executeStatement(&conn,&stmt);
 
        if (bsize)
        {
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET DB_FILE_MULTIBLOCK_READ_COUNT=%d",bsize);
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
        }
        if (hsize)
        {
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET HASH_AREA_SIZE=%d",hsize * 1048576);
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET \"_hash_multiblock_io_count\"=128");
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
        }
        if (serial)
        {
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET \"_serial_direct_read\"=TRUE");
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
        }
        if (ssize)
        {
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET SORT_AREA_SIZE=%d",ssize * 1048576);
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET SORT_AREA_RETAINED_SIZE=%d",ssize * 1048576);
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET \"_sort_multiblock_read_count\"=128");
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
        }
        if (trace)
        {
       memset(tempbuf,0,1024);
           sprintf(tempbuf,"ALTER SESSION SET EVENTS='10046 TRACE NAME CONTEXT FOREVER,LEVEL %d'", trace);
           ParseSQL(&conn,&stmt,tempbuf);
           executeStatement(&conn,&stmt);
        }
        if(ParseSQL(&conn,&stmt,query) == 0)
        {
            /*
               Pass parameter value here
            */
            for(i=0;i<argc;i++)
            {
               if (STRNCASECMP("arg:",argv[i],4) == 0)
               {        
                  memset(argname[argcount],0,128);
                  memset(argval[argcount] ,0,128);
                  memset(tempbuf,0,1024);
                  memcpy(tempbuf,argv[i]+4,MIN(strlen(argv[i]) - 4,254));
                  for(n=0;n<strlen(tempbuf);n++) if (tempbuf[n]=='=') break;
                  memcpy(argname[argcount], tempbuf, n);
                  if(n+1<strlen(tempbuf)) memcpy(argval[argcount], tempbuf+n+1, strlen(tempbuf) - n - 1);
                  if(strlen(argname[argcount])>0)
          {
            BindValue(&conn,&stmt,argname[argcount],argval[argcount]);
            argcount ++;
          }
          if (argcount >= 20) break;
               }
            }
 
            if(executeStatement(&conn,&stmt) == 0)
            {
                if(strlen(tabname))
                {
                    memset(ctlfname,0,256);
                    sprintf(ctlfname,"%s_sqlldr.ctl",tabname);
                    fpctl = fopen(ctlfname,"wb+");
 
                    if(fpctl != NULL)
                    {
                       fprintf(fpctl,"--\n");
                       fprintf(fpctl,"-- Generated by OCIULDR\n");
                       fprintf(fpctl,"--\n");
                       if (!header)
                          fprintf(fpctl,"OPTIONS(BINDSIZE=%d,READSIZE=%d,ERRORS=-1,ROWS=50000)\n", buffer, buffer);
                       else
                          fprintf(fpctl,"OPTIONS(BINDSIZE=%d,READSIZE=%d,SKIP=1,ERRORS=-1,ROWS=50000)\n", buffer, buffer);
                       fprintf(fpctl,"LOAD DATA\n");
                       fprintf(fpctl,"INFILE '%s' \"STR X'", fname);
                       for(i=0;i<strlen(record);i++) fprintf(fpctl,"%02x",record[i]);
                       fprintf(fpctl,"'\"\n");
                       fprintf(fpctl,"%s INTO TABLE %s\n", tabmode, tabname);
                       fprintf(fpctl,"FIELDS TERMINATED BY X'");
                       for(i=0;i<strlen(field);i++) fprintf(fpctl,"%02x",field[i]);
                       fprintf(fpctl,"' TRAILING NULLCOLS \n");
                    }
                }
                if(getColumns(fpctl,&conn,&stmt,&col) > 0)
                {
                      /*
                      iobuf = (text *)malloc(DEFAULT_BUFFER_SIZE);
                      memset(iobuf,0,DEFAULT_BUFFER_SIZE);
                      setvbuf(fp,iobuf,_IOFBF,DEFAULT_BUFFER_SIZE);
                      printRow(fp,&conn,&stmt,&col,field,flen,record,rlen);
                      fclose(fp);
                      free(iobuf);
                      */
                      printRow(fname,&conn,&stmt,&col,field,flen,record,rlen,batch, header);
                }
                else
                {
                      return_code = 5;
                }
                if (fpctl != NULL) fclose(fpctl);
             }
             else
             {
                return_code = 4;
             }
        }
        else
        {
            return_code = 3;
        }
          oclose(&stmt);
    } 
    else
    {
    return_code = 2;
    }
 
    freeColumn(&col);
    ologof(&conn);
    if (fp_log != NULL) fclose(fp_log);
 
    return return_code;
}