DBA Tools

    One of my friends need to unload CLOB values to flat file, he tried OCI 7 based ociuldr and sqluldr, both failed. For LOB columns, OCI 7 does have some prolems, it reports "ORA-32255" errors when retrieving NULL lobs or empty lobs for 10g database, and hang on for 9i database without latest patchs. I did search the metalink and google, but cannot find out the solution. Following is the error messages of OCI 7 lob access.

D:\>sqluldr parfile=testpar.txt

      0 rows exported at 2009-03-27 19:18:19
ORA-32255: Message 32255 not found;  product=RDBMS; facility=ORA
      9 rows exported at 2009-03-27 19:18:19
        output file uldrdata.txt closed at 9 rows.

    On 9i database, it may hange when try to get the first row, it proved to be an Oracle bug.

D:\>sqluldr parfile=testpar.txt

      0 rows exported at 2009-03-27 19:18:19

    So I rewrite sqluldr with OCI 8 interface, and I renamed it as sqluldr2, it can unload LOB values (maximum 1 megabytes) correctly.

D:\>sqluldr2 parfile=testpar.txt

      0 rows exported at 2009-03-27 19:18:13
      9 rows exported at 2009-03-27 19:18:13
        output file uldrdata.txt closed at 9 rows.

    It seems the new version has some performance improve compared to the previous version. For delimited text file.

D:\>sqluldr2 parfile=testpar.txt array=500

      0 rows exported at 2009-03-27 19:19:12
  500000 rows exported at 2009-03-27 19:19:15
1000000 rows exported at 2009-03-27 19:19:18
1500000 rows exported at 2009-03-27 19:19:22
2000000 rows exported at 2009-03-27 19:19:25
2500000 rows exported at 2009-03-27 19:19:29
3000000 rows exported at 2009-03-27 19:19:32
3500000 rows exported at 2009-03-27 19:19:35
3670044 rows exported at 2009-03-27 19:19:37
        output file uldrdata.txt closed at 3670044 rows.

    For fixed length record text file.

D:\>sqluldr2 parfile=testpar.txt array=500 field=0x20

      0 rows exported at 2009-03-27 19:19:45
  500000 rows exported at 2009-03-27 19:19:48
1000000 rows exported at 2009-03-27 19:19:51
1500000 rows exported at 2009-03-27 19:19:55
2000000 rows exported at 2009-03-27 19:19:59
2500000 rows exported at 2009-03-27 19:20:03
3000000 rows exported at 2009-03-27 19:20:06
3500000 rows exported at 2009-03-27 19:20:10
3670044 rows exported at 2009-03-27 19:20:11
        output file uldrdata.txt closed at 3670044 rows.

    It took me two hours to rewrite it, not complex as I predicated before. You can download them for test and compare.