This content has been marked as final. Show 10 replies
I have also used this method for a while. If you look carefully in the code, you will find the 50 column limit.
In the "parse_file" procedure I changed the 50 column limit to 80.....
if (l_num_columns > 80) then
raise_application_error(-20000,'Max. of 80 columns allowed, id='||p_file_name);
This works fine, but I have now changed my approach and written a procedure to put the uploaded file onto the server's filesystem and I use an external table to access the data - better for my application and also handles uploading images to the server without clogging up the database and using up the precious 4Gb of data allowed in XE!
It took me absolutely ages to perfect, so I thought I'd save you (and others) all the heartache!! ;)
1. In the page where you're uploading include an anonymous PL/SQL block :-
IF ( :P13_FILE_NAME is not null ) THEN
DELETE from APEX_APPLICATION_FILES WHERE name = :P13_FILE_NAME;
Notice I clean up - deleting the uploaded file after its on the server filesystem..
2. Create a database directory pointing to the filesystem diretory where you will store the file.
3. Now for the tricky bit - write a procedure to take the uploaded image and put it onto the filesystem.
CREATE OR REPLACE procedure SAVE_IMG_TO_FILE( p_image_name in varchar,
p_image_save_name in varchar)
l_amount BINARY_INTEGER := 32767;
l_read_amount BINARY_INTEGER :=0;
l_pos INTEGER := 1;
from FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ ai
where ai.NAME = p_image_name;
l_blob_len := DBMS_LOB.getlength(l_blob);
-- Open the destination file.
l_file := UTL_FILE.fopen('NAME_OF_DIRECTORY',p_image_save_name,'wb', 32767);
-- Read chunks of the BLOB and write them to the file
-- until complete.
WHILE l_pos < l_blob_len LOOP
l_read_amount := least(l_amount,l_blob_len - l_pos);
DBMS_LOB.read(l_blob, l_read_amount, l_pos, l_buffer);
UTL_FILE.put_raw(l_file, l_buffer, TRUE);
l_pos := least(l_pos + l_amount, l_blob_len);
-- Close the file.
WHEN OTHERS THEN
-- Close the file if something goes wrong.
IF UTL_FILE.is_open(l_file) THEN
4. Add a "Save file to Server" button that calls the Anonymous PL/SQL block
5. Build a database External Table that references the 'FILESYSTEM.CSV' in the directory
6. Write whatever SQL code you want to reference the CSV file as a table....
This works much better for my application as I also use this to save images to the filesystem.
It is approriate if you have a fixed structure of the CSV file and you want to reference the data for processing - and in this case tou don't need the first two lines of the CSV to have the column names and the column definitions.
Simple but very effective.
Mmmm, this is interesting. I am using the parse_file procedure to upload to a table with 49 columns currently. I had wanted to cope for potential expansion by increasing the 50 column limit but as I had not used collections before I read the documentation. This suggested that 50 was the limit for collections. I guessed that I was ok with 50 given that it would probably be rare that a user would want to provide data for all 49 columns in an upload and so didn't test it any further than 50.
I am interested to know if you actually implemented the 80 column version and had it working so that I may build this into my Developer's documentation as a potential future solution.
As You menitoned that You have tried for 80 columns..and It worked well also.. I too did the same in parse file procedure instead of 50 I have mentioned now 80 but still this code is not working for 80 columns.. It is restricting to 50 columns only and I am getting the same error .... So please can you assist me in this case....
I dont have any fix structure for the csv file so i cannot use External table concept.... So please suggest me in the above code itself.
Thanks & Regards
Please give me solutions is we have 100 columns using collection methis.. not by external tables concept.
Thanks in Advance
Seems that the 50 limit is there then for the collections. So unless you can split your uploaded file in some way to accommodate that I'm not sure how else you would do it using collections. I guess one way would be to split the file so that you can create more than one collection and store the primary key in each collection, do an insert to the table on the first collection and an update on the second and subsequent collections. Otherwise, parse all of the file into a PL/SQL table and insert to the destination table directly.
Hope this makes sense.
I used 2 collection to solve the 100 col's problem. I made little changes in the code.....
l_seq_id := htmldb_collection.add_member(p_collection_name,'dummy');
FOR i IN 1..49
p_seq => l_seq_id,
p_attr_number => i,
p_attr_value => l_record(i)
l_seq_id1 := htmldb_collection.add_member(p_collection_name1,'dummy1');
FOR i IN 50..l_record.count
p_seq => l_seq_id1,
p_attr_number => i,
p_attr_value => l_record(i)
But still i am getting the same error the collection will not allow more than 50 columns.
i tried to access 49 col's in 1st collection. still i am facing the same error.
could you guide me,
how to change the above code
or how use second collection in the code.
If col = <100 and col > 50
My plan is to store 49 col's in one table remain col in another table then merging
2 tables in one table.
Looking forward for quick reply.......
Santhosh Kumar T
I am trying to install the files from your link, but the first one tells me that it cannot because they were exported from a different workspace.
I am new to Apex and would greatly appreciate any assistance.
I was just looking over this thread. Are you having problems with the 50 column limit? ARe you loading the files yourself or are the users of an application? How often will the files be loaded?
Has any found a resolution to this issue? I am facing the same issue.
Does Apex 4.0 offer anything that would help with this limitation?
We have an application that needs to upload from a csv file as well.
the url http://htmldb.oracle.com/pls/otn/f?p=38131:1:3738150661527672
can not be open
Unexpected error, unable to find item name at application or page level.
Error ERR-1002 Unable to find item ID for item "P0_AFTER_SUBMIT" in application "38131".