Insert multi line data using sqlldr

Hello Everyone,

I just answered an OTN question about inserting multi line data using sqlldr and simply wanted to share it here (https://community.oracle.com/thread/4278952)

so if have a data like this in your text file:

1,”this is

two line data”, 10

2,”this is

three line

data”, 11

3,”this is one line data”, 12

4,this, 13

you need to do some extra work to accomplish this problem. In my example I have 4 rows but 2 of them includes multi line character data. By default sqlldr uses “newline” character (actually maybe saying enter character could be more appropriate) and every new line will be accepted as new row! To solve this problem you need to change your “row delimiter/terminator” but this will bring some extra burden because your current data file (csv, txt etc) is needed to be changed and your “new” row delimiter must be added to end of every row!

this is my table:

This row delimiter could be more than one character and it should be because row delimiter must not be seen in your original data. for example if you have comma in your data you can not use comma as row delimiter because every comma will be count as end of row. In my example I will use $%&/| as row delimiter.

First change your data file and add new delimiter to your data:

1,”this is

two line data”, 10$%&/|

2,”this is

three line

data”, 11$%&/|

3,”this is one line data”, 12$%&/|

4,this, 13$%&/|

secondly, we need to specify this new delimiter in our control file:

probably you already saw “STR X’2425262F7C'”. this is specifying new row delimiter. X means this is an hex code and 2425262F7C is the hex code of my delimiter $%&/|

how to produce this:

select UTL_RAW.cast_to_raw(‘$%&/|’) from dual;
————————
2425262F7C

so you can use any combination for your delimiter and just simply use utl_raw to generate hex code and use this in controlfile.

rest is same. just run sqlldr command with this control file and it will load the rows as expected.

select * from tmp_x;

please consider that your data must be in another file than control file! you can not use “infile *” and BEGINDATA in this structure.

I hope this helps.

How to call a Web Soap Service or API from PLSQL

Hello Everyone,

I would like to share one of my codes which allows you to call a web service (soap service) or API through PLSQL. I modified it time to time whenever something new comes up and I still do. I hope it helps you too.