These days I needed to export some tables to CSV files, and usually I could finish such task using SQLcl, but this time the table is too big (more than 120G) so although I still could export it with SQLcl, I also want to find a more efficient tool.
SQLULDR2 was the first tool I wanted to have a try, and I did try to make it work but got some errors about ORA-24345, and finally I decided to find other tool.
I found below webpage from Tom:
How Can I unload data to a flat file?From this page I got the source code, while I did spend some time to make it work, and that is why I want to share my experience here.
- find your library path, usually under /usr/lib/gcc/x86_64-redhat-linux for RHEL/CentOS/Oracle Linux systems, for me it is /usr/lib/gcc/x86_64-redhat-linux/4.8.2/include.
- Add the above path to the $ORACLE_HOME/precomp/admin/pcscfg.cfg
- Add below line to the head of the Pro*C source code: #include <stdlib.h>
- Try to compile it to C source code
[oracle@oradump orajson]$ proc unload.pc Pro*C/C++: Release 12.2.0.1.0 - Production on Thu Apr 27 18:24:14 2017 Copyright (c) 1982, 2017, Oracle and/or its affiliates. All rights reserved. System default option values taken from: /oracle/base/12cR2/precomp/admin/pcscfg.cfg
- Try to build the C code to execute file
gcc -I/oracle/base/12cR2/precomp/public -I/oracle/base/12cR2/xdk/include $ORACLE_HOME/lib/libclntsh.so unload.c -o sqluldr
- Unload the data as below example
./sqluldr userid=system/xxxxxxxx "sqlstmt=select USERID,to_char(ENDTIME),to_char(DATAVALUE,'fm99990.999999999999999999'),STATUSID from test" arraysize=100
- Please check your statement carefully, and convert date and double to char using to_char function.
- Compare some sample rows first to make sure you will not miss any valid data
