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.

Commit Time for an Inserted Row in Oracle

Hello everyone,

This week, one of my customer have experienced a problem. They provide some Web Service to their suppliers and one of those web services creates some rows and then returns the response as successful operation but just after that, supplier queries the newly created those rows but they get “no rows”! When analyze and development team came to me I simply look at the logs and fortunately they are storing their responses in a table with timestamp values too. After checking some tables, we find out that rows are created first and then responses are sent to caller. Also business logic is in that way. there is no way to return the response before the rows created.

So simply I asked about “commit time” because this is the only things that we can not measure in this case. rows might be created before sending the response but they might not committed yet so caller can not see them when querying with another session. Development team can not answer my question exactly because of very complicated framework logic. Somehow I need to solve this.

Basically I needed the commit time of that rows. while inserting a row you might use “sysdate” to understand when this row inserted but actually this is not enough and accurate because I might insert the row at 22:00:00 and write this date along with the row but might commit it at 22:01:00! Commit is 1 minute later. so when I check the row few hours later, I will “think” that this row is exist at 22:00:25 but actually it is not because it wasn’t committed yet but I don’t know that.

How can we check that? you might heard about “ora_rowscn” if not you can find some details here: https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/ORA_ROWSCN-Pseudocolumn.html#GUID-8071AAB0-F656-4C93-B926-0BCE1439F121

This is a pseudo column which means it is stored without your action in your table. ora_rowscn stores scn (system change number) information and by default table stores this data per your table blocksĀ not per row! that means a bunch of rows has same ora_rowscn value because they are in the same block but you can change this behave and force Oracle to store ora_rowscn per row and this will give you an idea about the “commit time” of every row. let’s demonstrate:

I created a table with “ROWDEPENDENCIES” clause which allows Oracle to store ora_rowscn per row. then I inserted and committed 2 rows but after inserting the second row I am waiting for 10 seconds. when we check the rows they both have same inserted_date but they have different ora_rowscn values and ora_rowscn almost shows the “commit time”.

I said almost because Oracle does not guarantee that. ora_rowscn will show at least the “commit time” not before that but might show a little bit after the exact commit time. even tough, it gave us a clue. in my example I am inserting second row at 22:48:26 but commit has arrived at 22:48:36.

as a con, “row dependency” property can not altered after the creation of the table which means you can not change your existing table you need to create a new table. As you guess my customer’s table doesn’t store ora_rowscn per row so rhat did I do to solve it? Simply created a new table with the Id of base table and with rowdependencies, then added a trigger to base table. Whenever an insert runs against the table, I also inserted it to my new table with the ID of the row and then check the ora_rowscn from new table.

Also a reminder. storing ora_rowscn per row requires more space than per block(by default).

I hope that helps.