Hi folks,
this might be an easy question for somebody who uses files and clobs more frequently.
I have a table, which stores some text files within CLOB column. Text files includes some basic data which I would like to load into other table - one text row = one new insert for me. Is there any "trick" how to do it effectively?
Here is my model:
Table OS_IMPORT_DOCS, stores complete files as CLOB
SQL> desc OS_IMPORT_DOCS
Name Type Nullable Default Comments
------------- -------------- -------- ------- --------
OBJECT_ID NUMBER
DATUM_ZMENY DATE sysdate
FILE_PATH VARCHAR2(4000) Y
FILE_NAME VARCHAR2(4000) Y
FILE_SIZE NUMBER Y
LAST_MODIFIED DATE Y
DOCUMENT CLOB Y
STATUS VARCHAR2(15) Y 'NEW'
Sample of data from OS_IMPORT_DOCS
SQL> select *
2 from os_import_docs d
3 order by d.last_modified desc
4 ;
OBJECT_ID DATUM_ZMENY FILE_PATH FILE_NAME FILE_SIZE LAST_MODIFIED DOCUMENT STATUS
---------- ----------- ------------------------------ --------------- ---------- ------------- ----------- ---------------
1815043 13.8.2012 1 d:\data\C120813.DAT C120813.DAT 16800 13.8.2012 16: <<CLOB>> NEW
1815042 13.8.2012 1 d:\data\C120812.DAT C120812.DAT 3600 12.8.2012 22: <<CLOB>> NEW
1815041 13.8.2012 1 d:\data\C120811.DAT C120811.DAT 1800 11.8.2012 13: <<CLOB>> NEW
Sample of CLOB file - data stored in text (select d.document from os_import_docs d where d.object_id = 1815042;)
061053120820120000AGT000002Osoby 0000000042301000000017210632
062322120820120000AGT000002Osoby 0000000012301000000017197566
063526120820120001AGT000002Osoby 0000000012301000000017197566
064234120820120001AGT000002Osoby 0000000103301000000162218777
Above text sample includes "columns" in plain text:
timestamp - position 1 - 14, format SSMIHH24DDMMYYYY
flag - position 15 -18
company code - position 19 - 27
etc ...
How can I query data stored within OS_IMPORT_DOCS.DOCUMENT column, split it into necessary columns and insert into other table?
Do I need to read this file "row-by-row " method?
Many thanks,
Tomas