unload data tools--ociuldr3

最近工作中需要从ORACLE中导出数据到文本文件，找到ociuldr2觉得不错。ociuldr2源码下载地址：http://www.anysql.net/tools/ociuldr2_source_code.html

ociuldr2是使用v8 OCI function对ociuldr的改进版。ORACLE现在推荐使OCIStmtFetch2和OCILobRead2取数据，对ociuldr2进行了重写，改名为ociuldr3。

代码使用gcc34编译，并在ORACLE11R2执行通过。gcc4编译的执行有问题，有兴趣修改优化的可以加qq群 71322761 讨论。

持续优化中。。。

ociuldr3.c

/*
   NAME
     ociuldr3.c - Using OCIStmtFetch2 etc function to rewrite unload script.

   MODIFIED   (MM/DD/YY) 
     Zhu Yi         2013.04.30 -  Initial rewrite.
*/

#include <stdio.h>
#include <stdlib.h>
#include <string.h>
#include <ctype.h>
#include <time.h>

#include <oratypes.h>
#include <oci.h>
#include <ocidfn.h>
#include <ocidem.h>

/* Constants used in this program. */
#define MAX_ITEM_BUFFER_SIZE    33
#define MAX_SELECT_LIST_SIZE    1024
#define MAXBUFLEN               4000
#define ROW_BATCH_SIZE          500000

#if defined(_WIN32)
#define STRNCASECMP memicmp
#else
#define STRNCASECMP strncasecmp
#endif

#define  MIN(a,b) ((a) > (b) ? (b) : (a))

struct COLUMN
{
  /* Describe */
  //text            colname[MAX_ITEM_BUFFER_SIZE];
  text            *colname;
  ub4             colname_len;
  ub4             colwidth;
  ub2             coltype;
  ub4             buflen;
  ub2             precision;
  ub2             scale;

  /*+ Fetch */
  OCIDefine       *dfnhp;
  OCILobLocator   *blob; //blob locator
  OCILobLocator   *clob; //clob locator
  ub1             *colbuf;    //output variable
  sb2             *indp;
  ub2             *col_retlen;
  ub2             *col_retcode;

  /*+ Point to next column */
  struct          COLUMN *next;
};

/*Defined functions*/
void  checkerr(OCIError *errhp,sword status);
void  initialize ();
void  logon (char *v_user,char *v_pass,char *v_host);
void  logout();
void  cleanup();
void  freeColumn(struct COLUMN *col);
void  printRow(text *fname,OCISvcCtx *svchp,OCIStmt *stmhp,struct COLUMN *col,text *field, int flen,text *record, int rlen, int batch, int header);
void  printRowInfo(ub4 row);
void  destr(char *src,char *v_user,char *v_pass,char *v_host);
void stream_read_clob(OCILobLocator *lobl, FILE *fp);
void stream_read_blob(OCILobLocator *lobl, FILE *fp);
sword preparSql(OCIStmt *stmhp, text *sql_statement);
sword executeSql(OCISvcCtx *svchp,OCIStmt *stmhp,ub4 execount);
sword getColumns(FILE *fpctl,OCIStmt *stmhp, struct COLUMN *collist);
int   convertOption(const ub1 *src, ub1* dst, int mlen);
ub1   getHexIndex(char c);
FILE  *openFile(const text *fname, text tempbuf[], int batch);

static void describe_table (FILE *fpctl,text *tabname);
static void describe_column(FILE *fpctl,OCIParam *parmp, ub4 parmcnt);

/*global env variables*/
static OCIEnv       *envhp  = (OCIEnv *)0;
static OCIServer    *srvhp  = (OCIServer *)0;
static OCIError     *errhp  = (OCIError *)0;
static OCISvcCtx    *svchp  = (OCISvcCtx *)0;
static OCIStmt      *stmhp  = (OCIStmt *)0;
static OCIDescribe  *dschp  = (OCIDescribe *)0;
static OCISession   *sesshp = (OCISession *)0;

ub4     DEFAULT_ARRAY_SIZE = 1000;
ub4     DEFAULT_LONG_SIZE = 32768;
text    DATE_FORMAT[32]="YYYY-MM-DD HH24:MI:SS";
int     return_code = 0;
FILE    *fp_log = NULL;

int main(int argc, char *argv[])
{
  sword n,i,argcount=0;
  int   v_help=0;
  
  struct COLUMN col;

  text tempbuf[1024];
  text user[132]="";
  text query[32768]="";
  text sqlfname[255]="";
  text tabname[132]="";
  text tabmode[132]="INSERT";
  text fname[255]="uldrdata.txt";
  text ctlfname[256]="";
  text field[132]=",";
  text logfile[256]="";
  text record[132]="\n";
  
  int  flen,rlen;
  int  buffer= 16777216;
  int  hsize = 0;
  int  ssize = 0;
  int  bsize = 0;
  int  serial= 0;
  int  trace = 0;
  int  batch = 0;
  int  header= 0;

  
  char *p_user=malloc(50);
  char *p_pass=malloc(50);
  char *p_host=malloc(20);
  
  FILE *fp;
  FILE *fpctl;
  
  flen = rlen = 1;
     
  for(i=0;i<argc;i++)
  {
    if (STRNCASECMP("user=",argv[i],5)==0)
    {
        memset(user,0,132);
        memcpy(user,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("sql=",argv[i],4)==0)
    {
        memset(sqlfname,0,132);
        memcpy(sqlfname,argv[i]+4,MIN(strlen(argv[i]) - 4,254));
    }
    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("field=",argv[i],6)==0)
    {
        memset(field,0,132);
        flen=convertOption(argv[i]+6,field,MIN(strlen(argv[i]) - 6,131));
    }
    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("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)=