SQLULDR2 :: Tips 1 – Customize the Field and Record Separators for Flexible Format

When exchanging data between different databases (Oracle / MySQL / PostgreSQL / Hadoop / Spark) with text file, it’s important to customize the field separator for different fields value of a row and the record separator for different rows within a text file.

Here is an example of the Comma-separated values for table data.

ACT_RU_TASK,TABLE,
HPISLEM,TABLE,
TEST1,TABLE,
T_TEST,TABLE,
T_TEST2,TABLE,
VIEWTEST,TABLE,

The above data is generated from Oracle by “select * from tab”, it contains theree columns, each column value is splitted by comma char, and it contains six rows, each row data is splitted by a newline char. The most important thing is that the field / record separators should not appear in the column value.

You can use set field and record separators to any characters, including visible or invisible characters. For example:

$ sqluldr2 test/test query=tab field=#col# record=#row#0x0a file=tab.txt
           0 rows exported at 2016-01-30 09:58:55, size 0 MB.
           6 rows exported at 2016-01-30 09:58:55, size 0 MB.
         output file tab.txt closed at 6 rows, size 0 MB.

$ cat tab.txt
ACT_RU_TASK#col#TABLE#col##row#
HPISLEM#col#TABLE#col##row#
TEST1#col#TABLE#col##row#
T_TEST#col#TABLE#col##row#
T_TEST2#col#TABLE#col##row#
VIEWTEST#col#TABLE#col##row#

For invisible characters you can use “0x<XX>” for Ascii Code Table in hex mode. By the way you can put the command options in a text file and then run SQLULDR2 with “parfile” command option.

$ sqluldr2 parfile=temp.txt
ACT_RU_TASK#col#TABLE#col##row#
HPISLEM#col#TABLE#col##row#
TEST1#col#TABLE#col##row#
T_TEST#col#TABLE#col##row#
T_TEST2#col#TABLE#col##row#
VIEWTEST#col#TABLE#col##row#

$ cat temp.txt
user=test/test
query=select *
   from tab
field=#col#
record=#row#0x0a
file=-

For “query” option, if the SQL text contains multiple lines, prefix them with at lease one white space, it’s hard to pass complex querys in command lines.