If you have access to the database server, you MIGHT want to look at using an external table versus the wizard. Or you could do a combination of the two.. Have your file on the server as an external table, and then have a wwizard like form built to allow users to select the table to import the external table into...
No real idea, but what you are trying to do is NOT exactly the way the tool was meant to be used... Not that complex of a procedure would be needed to have users using an external table.. Is it always going to be same table updated?
Simple form to prompt them through process, making sure they have uploaded file to server or form allows them to upload to mapped oracle directory..
I am curious how you 'split file into 2' and still get all the data into the same table -- or did you do 2 separate tables then joined by a unique key?
I have exactly the same issue. I had worked on a different process I had read about -- uploading file to BLOB or CLOB and then parsing, but never got it to work.
I'd appreciate a description of how you got the 2 file uploads to work.
So currently we are using the following approach
1. Split the file into two files (file 1 and file 2) with primary key included in both the files.
2. We have two temp tables where these files will be uploaded resp, and it will also capture the username in the tables.
3. When the 2nd file is done uploading ,we match the # of rows in both the tables by that user if it matches we run a sql to insert/update data in the actual table.
4. If the record count doesn't match (means if file 1 and file 2 are not in sync) we delete the data from the temp tables just for that user and then user can reload it.
5. Once the insert/update is done and when the user tries to load the file again , we delete old data from the temp tables just for that user.
So every time user loads data ,it will maintain data only for that load.
So this way multiple users are able to upload files.
Also currently we finished development of alternate method to load entire file at once.In this approach we are not using upload utility, we have option where user can upload the file (1st page of upload utility) and then procedure is kicked of to get the file from wwwv_flow_files (APEX table) from a BLOB column.
Data is parsed using a procedure and then inserted/updated into the original table.
I hope this helps. Kindly let me know if you need more details.
Thanks and Regards,
Is this the example you were using for your upload ? from:http://avdeo.com/2008/05/21/uploading-excel-sheet-using-oracle-application-express-apex/
I could not figure out how to set up the File Browse page item and the Button attributes to get it to work. Would you be willing to share the details of how you set the page up?
Hi, a couple other questions.
Your file browse item -- besides setting Storage type to 'Table WWV_FLOW_FILES' was there any other attribute set other than defaults -- like Source (left at 'Only when current value in session state is null' and Static Assignment?)
For Processing -- no conditions?
Did you set a Branch back to same page after processing?
I understand the External Table part, but what happens if 2 or more people need to run the upload process simultaneously?
You could make use of dbms_lock to serialize the process - the others users would wait until the 'external-table-to-internal-table' process completed.