can SQL*LOADER load multiple lines into one column ?

[MikeHendy]

Hi,

I'd like to use sqlldr to load a multi-lined file into a single column, single record table. Is it possible ?

Thanks in Advance,
Mike.

 

[MikeJones]

I think you would have to cheat but yeah it's possible.

Load the data into a table that will be able to accept the biggest line, this is NOT the final table you want the data stored in. place a trigger on this table so that for each row inserted it sets the value of the first row in the table you want the data to end up in (rownum = 1) to equal itself concatonated with the new value just inserted via the SQL Loader. You'll need an excpetion and unless you are using CLOBS the biggest file you will be able to load will have to be less that 32K as that's the biggest you can define a VARCHAR2 as.

Here's an example

Trigger on temp_table
ON INSERT
FOR EACH row
UPDATE final_table
SET data_column := data_column || :new.data_column
WHERE rownum = '1'

Come to think of it I don't think you need the rownum = 1

You would also need to start the table with 1 null row to start it off. you could add this to your trigger test for a %FOUND and if it's not there insert a null row first