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; /