developers

    Forum Stats

  • 3,874,064 Users
  • 2,266,670 Discussions
  • 7,911,712 Comments

Discussions

Loading CSV file using external table

partlycloudy
partlycloudy Member Posts: 8,176 Silver Trophy
edited Nov 15, 2010 5:32PM in APEX Discussions
Here is a generic example of loading a CSV file using APEX and external tables.

Using the external table feature is orders of magnitude faster than parsing the BLOB stream!

1. Create a generic csv-style external table definition like the following. This can read any CSV file (upto 50 columns)
CREATE TABLE ADHOC_CSV_EXT
(
C1 VARCHAR2(4000),
C2 VARCHAR2(4000),
C3 VARCHAR2(4000),
...
C50 VARCHAR2(4000)
)
ORGANIZATION EXTERNAL
( TYPE ORACLE_LOADER
DEFAULT DIRECTORY SOME_DIR
ACCESS PARAMETERS
(records delimited BY newline
fields
terminated BY ','
optionally enclosed BY '"'
lrtrim
missing field VALUES are NULL
)
LOCATION ('foo.csv')
);
2. Create some utility APIs to read/write the files.
CREATE OR REPLACE PACKAGE htmldb_file_util
AS
PROCEDURE blob_to_file (
p_blob IN BLOB,
p_directory IN VARCHAR2,
p_filename IN VARCHAR2
);

PROCEDURE file_to_collection (
p_directory IN VARCHAR2,
p_filename IN VARCHAR2,
p_collection IN VARCHAR2,
p_delete_file IN BOOLEAN DEFAULT TRUE
);
END htmldb_file_util;
/

CREATE OR REPLACE PACKAGE BODY htmldb_file_util
AS
PROCEDURE blob_to_file (
p_blob IN BLOB,
p_directory IN VARCHAR2,
p_filename IN VARCHAR2
)
IS
l_fp utl_file.file_type;
l_amt INTEGER DEFAULT 32000;
l_offset INTEGER DEFAULT 1;
l_length INTEGER DEFAULT NVL(dbms_lob.getlength(p_blob),0);
BEGIN
l_fp := utl_file.fopen(upper(p_directory),p_filename,'w',32760);

WHILE (l_offset < l_length)
LOOP
utl_file.put_raw(l_fp,dbms_lob.substr(p_blob,l_amt,l_offset),TRUE);
l_offset := l_offset + l_amt;
END LOOP;

utl_file.fclose(l_fp);
END blob_to_file;

PROCEDURE file_to_collection (
p_directory IN VARCHAR2,
p_filename IN VARCHAR2,
p_collection IN VARCHAR2,
p_delete_file IN BOOLEAN DEFAULT TRUE
)
IS
c_lock_name CONSTANT VARCHAR2(100) := 'HTMLDB';
l_lock_handle VARCHAR2(100);
l_retval NUMBER;
l_sql VARCHAR2(1000);
l_collection VARCHAR2(100) := upper(p_collection);
BEGIN
-- The adhoc_csv_ext external table can only "read" one file
-- at a time. If multiple APEX sessions try to upload files at
-- the same time, they would step on each other's toes.
--
-- So use DBMS_LOCK to "serialize" access to the shared resource by
-- requesting a exclusive named lock.
--
-- Ensure that the lock is released as soon as possible.
dbms_lock.allocate_unique(c_lock_name,l_lock_handle);
l_retval := dbms_lock.request(l_lock_handle,dbms_lock.x_mode,1);
IF (l_retval != 0)
THEN
raise_application_error(-20000,'Error uploading file. Please try again in a few moments');
END IF;

l_sql := 'alter table adhoc_csv_ext location ('||upper(p_directory)||':'||''''||p_filename||''')';
EXECUTE IMMEDIATE l_sql;

IF (apex_collection.collection_exists(l_collection))
THEN
apex_collection.delete_collection(l_collection);
END IF;

apex_collection.create_collection_from_query(l_collection,'select c1, c2, c3, c4, c5, c6, c7, c8, c9, c10, c11, c12, c13, c14, c15, c16, c17, c18, c19, c20, c21, c22, c23, c24, c25, c26, c27, c28, c29, c30, c31, c32, c33, c34, c35, c36, c37, c38, c39, c40, c41, c42, c43, c44, c45, c46, c47, c48, c49, c50 from adhoc_csv_ext');

l_retval := dbms_lock.release(l_lock_handle);
IF (l_retval != 0)
THEN
raise_application_error(-20000,'Unexpected internal error occured');
END IF;

IF (p_delete_file)
THEN
utl_file.fremove(upper(p_directory),p_filename);
END IF;

EXCEPTION WHEN OTHERS THEN
l_retval := dbms_lock.release(l_lock_handle);
COMMIT;
RAISE;
END file_to_collection;
END htmldb_file_util;
/
3. On an APEX page with a File Browse page item, create the following After Submit process conditional upon some Upload button being pressed
declare
l_blob blob;
l_filename varchar2(500);
begin
select blob_content,filename
into l_blob,l_filename
from apex_application_files
where name=:P1_FILENAME;

htmldb_file_util.blob_to_file(l_blob,'TMP_DIR',l_filename);
htmldb_file_util.file_to_collection('TMP_DIR',l_filename,'MY_COLLECTION');

delete apex_application_files
where name=:P1_FILENAME;

exception when others then
delete apex_application_files
where name=:P1_FILENAME;

commit;

raise;
end;
4. That's it. MY_COLLECTION should now contain the contents of the file in the corresponding columns.

Hope this helps.
Tagged:
1052638

Comments

This discussion has been closed.
developers