How to extract rows to a flat file with ociuldr utility?
Everybody knows that Oracle does not provide tools to unload rows as text file. I use PowerBuilder's data pipe to implement this feature, and then we found there were two utilities on Tom Kyte's site, one by SQL*Plus command, and one by Pro*C program. Later I found that SQL*Plus is a little slower than Pro*C, but Pro*C is version dependent; you need to compile different versions of executable binary on different versions of Oracle client. Several months ago, I was learning to write an OCI program. I try to rewrite Tom's unload tool in OCI. I consider it as my first OCI program, and finally I finished it, and find a way to remove the version dependency. Normally an OCI program will find a dynamic library "libclntsh.<ver>.so". In my ociuldr utility, it will find only the ociuldr.so file on Unix/Linux. I have tried different versions of Oracle client, 8i/9i/10g on Windows, 8i/9i on Solaris. It works quite well without the need to recompile. You can download the Windows/Linux/Solaris(sparc) binary file here. Let's take a look at the command line help:
Usage: ociuldr user=... query=... field=... record=... file=...
(@) Copyright Lou Fangxin 2004/2005, all rights reserved. Notes: -si = enable logon as SYSDBA user = username/password@tnsname sql = SQL file name, one sql per file, do not include ";" query = select statement field = seperator string between fields record= seperator string between records file = output file name(default: uldrdata.txt) read = set DB_FILE_MULTIBLOCK_READ_COUNT at session level sort = set SORT_AREA_SIZE & SORT_AREA_RETAINED_SIZE at session level (UNIT:MB) hash = set HASH_AREA_SIZE at session level (UNIT:MB) serial= set _serial_direct_read to TRUE at session level trace = set event 10046 to given level at session level For field and record, you can use '0x' to specify hex character code, \r=0x0d \n=0x0a |=0x7c ,=0x2c \t=0x09 The read/sort/hash options are for performance tuning. For example, I was required to seperate the column with "#" and rows with new lines (char 0x0a):
C:\ociuldr user=anysql/anysql@prod query="select * from tab" field=# record=0x0a file=anysql_tab.txt
3100 bytes allocated for column TNAME (1) 800 bytes allocated for column TABTYPE (2) 4100 bytes allocated for column CLUSTERID (3) 0 rows exported at 2006-08-03 10:54:38 12 rows exported at 2006-08-03 10:54:38 C:\>type anysql_tab.txt AULEXT#TABLE# A_SEQ_SYN#SYNONYM# A_V#VIEW# OBJD_LIST#TABLE# T#TABLE# TRANSACTION_CACHE_IOT_0#TABLE# T_HASH#TABLE# T_LOB#TABLE# T_LONG#TABLE# T_LONGRAW#TABLE# T_TRUNC2#TABLE# T_TRUNC4#TABLE# ociuldr fetches rows from the database with array size 100. This makes this utility run really fast. Currently the sqlldr template file will not be generated. I will enhance this later. For Linux/Unix, you may need to define LD_LIBRARY_PATH as $ORACLE_HOME/lib32 (9i above) or $ORACLE_HOME/lib (8i). The network speed is very critical to performance. |