Posts

Showing posts from August, 2010

CSV Upload into Oracle Table

The first thing you need is a page with a file type item, and a submit button. When you submit the page, the selected file is uploaded into the apex_application_files (or wwv_flow_files) view. The actual blob contents of the file is uploaded into the column BLOB_CONTENT but since a CSV file is just plain text, it made sense to me to convert that file to a CLOB, so that you can perform regular string functions on the data. The first reference to converting a blob to a clob, was in the OTN forums  - confirmed by referring to the oracle documentation . After converting it to a clob, it is simply a matter of looping over each row of the data, and then populating into an apex collection (or some other temp table). This is an intermediary step just so you can review the data before committing the changes. Finally, you just move the data from the collection into the table. So the code for copying the blob into a clob and populating the collection can be seen below. declare --variables t