In APEX 4, we now have the option to place where the uploaded file will be stored within the development workspace interface. The Storage Type setting of the File Browser item has two options:
- Table WWV_FLOW_FILES stores the uploaded file in the table wwv_flow_files (or APEX_APPLICATION_FILES). This is the default way in the previous versions of APEX. Note that if you still decide to store the uploaded files this way, it is suggested to move record to another custom table after upload, and then clean it up in this table because this is the shared default area (table) where every application will upload files to.
- BLOB column specified in item source attribute will store the uploaded file in a custom table identified in the Automatic Row Processing (DML) process and the column specified in the item source attribute. This column has to be of data type BLOB.
To configure the File Browser with the BLOB column specified in item source attribute.
1. Create a table for storing uploaded files. One of the columns must be BLOB data type.
CREATE TABLE tbl_attach_file
(
attach_id NUMBER PRIMARY KEY,
attach_data BLOB,
attach_mimetype VARCHAR2(255),
attach_filename VARCHAR2(255),
attach_last_update DATE ,
attach_charset VARCHAR2(128),
attach_user VARCHAR(10)
);
CREATE SEQUENCE SQ_ATTACH_FILE start with 1;
CREATE OR REPLACE TRIGGER tr_attach_file_BI
BEFORE INSERT
ON tbl_attach_file
FOR EACH ROW
BEGIN
SELECT SQ_ATTACH_FILE.NEXTVAL
INTO :NEW.attach_id
FROM DUAL;
END;
/
2. Create a File Browser item with the following settings.
If this is just simply for file upload, select No on Display Download Link. Otherwise, it will require additional Automated Row Fetch (DML). See note and comment below.
3. The item’s source points to the BLOB column.
4. Create an Automatic Row Processing (DML) process.
5. Create a button called Upload with Database Action – SQL Insert Action.
Initially I thought this should be sufficient, but when I ran the page, I got this error.
Apparently I got this error because I checked Yes to Display Download Link thus it requires a DML to fetch data. If this is set to No, it should be okay without the following Automated Row Fetch (DML). Thanks Patrick for pointing that out.
To workaround this, I created another DML but for Automated Row Fetch.
For a purpose of blogging, we’re not going to run any row fetch, so the DML Fetch will have it disabled (its condition to Never). I’m still not sure why the Automated Row Fetch (DML) is needed (even being disabled) if we just want to upload files. But if it is not there, I got the ORA-2001: No Corresponding DML process shown in step 5.
A quick test shows that the file is uploaded to our custom table.
According to Oracle APEX document, the character set of the BLOB is not automatically set on upload. We will have to create an additional page item which is bound to the character set column. This will be where users can specify the character set for the file they are uploading.
Hi,
the DML Fetch process is required in your example because you have enabled the “Display Download Link” feature, which requires the fetch process so that it knows where it has to read from. If you set that flag to “No”, the “Automatic Row Processing (DML)” should be sufficient.
Regards
Patrick
Patrick,
It is great to know that. Thanks for clarification.
Ittichai
I created my page following your rules but when I ran the application to save an image it just doesnt Error: ORA-01403:no data found any solution?
Now I run a report to show te file or an omage but it just doesnt appear the rest of the data is ther but the field that contains the blob shows empty – how should i download it to a report
@ Mauricio how to do display data? Some tools might not be capable of display file content or image. Have you confirm with DBMS_LOB.GETLENGTH? I just want to double check.
Thanks ittichai for taking your time finally repeated the report and now it just work know i have another plobem trying to break after a column but i think that may be another thread
Ireally need that help I have a table named items with images shirts, skirts, pants, and another table with sizes and another one with colors, The item table has an image of each item but when i join 2 o 3 of this tables de image of course respeat it self for each characteristic I mean size 32
size 34
size 38
is an image on the table items and i would like to show the image with all sizes but the image one time only like this
size 32
size 34
size 38
Is this possible? How?
Ireally need that help I have a table named items with images shirts, skirts, pants, and another table with sizes and another one with colors, The item table has an image of each item but when i join 2 o 3 of this tables de image of course respeat it self for each characteristic I mean $pants$ size 32
$pants$ size 34
$pants$ size 38
$pants$ is an image on the table items and i would like to show the image with all sizes but the image one time only like this
$pants$ size 32
size 34
size 38
Is this possible? How?
@Mauricio I think you’re probably looking at an inline view for size – something like this select $pants$, (select size from size-table where …) from pants-table. But you have to manage how to display sizes in the 2nd column in the concatenated format.
I tried to do that
Select a.image_id, a.item_image, (Select b.color_desc from colors where a.image_id = b.image_id) from items a
but i got this error:
ORA-12899: value too large for column “APEX_040000″.”WWV_FLOW_WORKSHEET_COLUMNS”.”DB_COLUMN_NAME” (actual: 63, maximum: 30)
Unable to create Dynamic Query page.
what should I do
Your error indicated that data is too big (63 chars) for column size (30 chars).
Note that for the inline view (if one image_id has multiple colors), you’ll need to concatenate to return one row (may be using “,” or “new line”), otherwise it will fail.
tHANK NOW I GOT THE RIGHT QUERY IT JUST RETURN ME VARIOUS ROWS IN ONLY ONE STRING NOW MY PROBLEN IS THAT I NEED TO MAKE A FROM TO ENTER THE DATA FOR THESE 2 TABLES HOW IS IT POSSIBLE IF WHEN I CREATE AN ITEM IM JUST ASKED FOR A COLUMN NAME BUT NO FOR A TABLE MY FOR ALREADY SAVES DE IMAGE, THE NAME FOR THE ITEM TABLE BUT NOW HOW CAN A INTRODUCE THE COLOR AND SAVE
Thank u i just got how to show some rows in in one string thats great but now there anothe problem when i use a third table for example
pants jean blue
black
pink
lino white
black
blue
then the report shows like this
pants blue black pink jean jean jean
I will explain in a more real way
have 4 tables Items, colors, materials, item_definition
items have patch cords, optic fiber, switchs, routers
colors black, white, red, green, gray
material plastic, cuper, alumnium
on table item definition there are many rows with exactly each item color and material
I mean patch cords black plastic
patch cords white plastic
switch white null
switch black null
optic fiber white plastic
white aluminium
i need to show patch cords black, white plastic
switch black, white
optic white plastic, aluminium
but i get patch cords black, white plastic, plastic
switch black, white
optic white, white plastic, aluminium
Hi i need to creat a multi-select fiel in my form but when i try to insert the error is declare
Invalid numeric value 3:1:2 for column level my idea is a form where yo insert which level has a student passed for example
Student Level
Joel 1
Joel 2
Joel 3
so in my form i select the student and in a multi select list select the 3 levels but when i save there goes the error, How can i insert a multirow select list in apex 4
Joel,
Please read the “how to work with multiple select list item” from Oracle documentation site at http://download.oracle.com/docs/cd/E17556_01/doc/user.40/e15517/bldapp.htm#HTMDB25337. It shows a sample on how to write values from multiple select list whose its value is in the colon-delimited string into a table using APEX_UTIL.STRING_TO_TABLE function.
Thank that really helped me, now I need something else when I execute than way to present a multi row registry I get some parameters repeated for example
I have
Item Color Fabric
Jackets yellow jean
Jackets blue jean
Jackets black jean
Jackets black leather
with a query like that i get
Jackets yellow blue black black jean jean jean leather
I need to show yellow blu black jean leather
Is ther a way to make something like a distinct yin that kind of query?
I am sorry about tghis but I cant find any answer about this either any forum Im trying to work with progress lists but when I create a region list type doesnt follow the sequence why, I´ve listened that some people have problems with this in apex on spanish but thats not my problem my apex is on english but still cant get it work help me please .
If this theme is for another forum indicate me where should i post this
Hi, I really need some help, I have a new tabular form based o an empty table for shipping orders, all the fields from this empty orders table are, of course, called from other tables so i make them all select list but there are som fields that i need to be filled when this selecs chage i mean
_________________________________________________________
articule v | seccion v | subseccion v | cuota |
_________________________________________________________
select lists articules, seccion and subseccion
quota is a field on table articules that, when i select and articule the quota should be filled but how i do that
I think you can programmatically assign value of any columns. They are referred to as
document.wwv_flow.fXX (in Javascript)
or apex_application.g_fXX (in PL/SQL)
You can use PL/SQL block for validation or process. (See sample at http://apex.oracle.com/pls/apex/f?p=57043:VALIDATE_TABULAR_COL), or using javascript (http://apex.oracle.com/pls/apex/f?p=57043:TABULAR_READONLY_NO_DUP).
Ittichai
It doesnt work i need that when I choose an articule inmidiately the field quota is filled
For immediate action you will have to use javascript. If there is a need to access database in the background, look into AJAX as well. Google for “APEX javasript AJAX”. There are many articles written about it.
So if i need to multiply this quota with another value I shoul enter Its also an ajax way to do?
I found some code for ajax but dont know how to use it where should i put this code yin the page like a process in the field where
That ajax should be helpful if the code were used foe tabular forms I mean with field but its just explain for textfield on a regular form “PXX_#######” that really doesnt help me a need to use de code for report fields on a tabular form
I have the same file upload. If my form validation fails returning the user to the form, the file upload field is empty…and my user has to re-select a file to be uploaded. Any idea how to avoid this? Thank you.
Matt,
I’m not sure how to do that. At least the file browser will stay at the directory where you last selected. My suggestion is that you could try a dynamic action where you save the text every time the selection is changed.
Thanks for introducing a little rtanoiality into this debate.
Pingback: Create an Application to Upload Files Using Oracle APEX, In Less Than 10 Minutes (Video) » Eddie Awad's Blog
I have followed the same steps but the file I upload gets stored in the wwv_flow_files table and not in the custom table I have defined. Any suggestions to resolve this would be very helpful.
I assume you choose “BLOB column specified in item source attribute”?
Yes, I chose “BLOB column specified in item source attribute”. However, it always gets stored in the WWV_FLOW_FILES table instead of the custom table I have created.
Does any setting in APEX prevent us from using custom tables?
I would wonder how I would share the application as its in the intranet. I would try to replicate the same in the Oracle’s Apex instance on the Internet so that you can help me debug the same.
You can use apex.oracle.com where the workspace can be requested for free. Most people are using it to share samples or collaborate. But keep in mind, with my busy schedule, I might not have a chance to take a look for awhile.
Hi,
I am a biz user and not very technical (but am learning as I go.)This was a very easy to understand article. Thank you for laying it out as you did.
Is there a part 2… How to create the download section so that users and get the uploaded attachments?
thanks,
ray
Not really since the download part has been well documented on the Oracle web site. http://download.oracle.com/docs/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#insertedID6
Once you have the procedure and proper grant to public, you can just call that procedure in the a href by referring to the procedure “download_my_file?p_file=” with proper parameter.
Hi,
This is kishore chepuri. would u please tell the steps for sending the blob data to a pdf in apex with sales force.
You can look into this post for file “upload” https://ittichaicham.com/2011/03/file-browser-in-apex-4-with-blob-column-specified-in-item-source-attribute/. This will automatically upload into the BLOB of your defined table. For “download”, please look at Oracle document http://download.oracle.com/docs/cd/E14373_01/appdev.32/e13363/up_dn_files.htm#insertedID6. This is pretty much viewing file via browser.
hi,
I want to display the pdf as reports . As we generated in salesforce. I am unale to convert into pdf format. Please help for this.
I believe this has to do with http header information. Please try to add this line and see.
OWA_UTIL.mime_header (NVL (l_mime, ‘application/pdf’), FALSE);
I am getting “NO Data Error”..How to Resolve this
I would recommend you to debug your codes using Toad or SQL Developer. Just simply set the group security ID in the tool so you can access APEX from external tool.
Hi,
I have tried your example trying to load a .csv file into the Blob column of your example table, but have not been successful. One question I had was, it was not clear to me the source for the Primary key for the DML process definition. It appears to be the Item name for the File Browse item according to your screen shot, but the Item name of the File browse item has a source of the ‘attach_data’ column which is not the primary key of the table.
Pat
hi ,
I have a java exception stack trace which I have stored in the DB as BLOB. Now, I want to display the BLOB data as text on the oracle apex page/popup.
I am using a DB link and it oracle apex throws an error if I create a display item for the remote DB.
Can you please help me here,
I want to upload excel and csv file in apex temp table througt file browser is it possible ?
Yes, from APEX 4.1+, you can use wizard to create “data upload” pages where you can use the file browser to load the existing CSV (or other delimiters) file and load into a database table. For excel upload, it is a bit tricky. But Denes has written a good post and sample on how to do it at http://deneskubicek.blogspot.com/2013/12/apex-listener-and-excel-upload.html. (Note that it requires APEX listener.)
Hi,
I have a process which creates files of consolidated configuration data from many machines on our college campus. I now want to automate an upload of this consolidated file from the machine which holds the file. Can this be done using an Apex application which runs a process to upload the file, parse it and then insert into a table?
Pat
If those files are visible to database server, you can do external table or sqlloader. Either can be scripted very easily.
Hi,
Urgent….!!!
Currently i am working on File upload using BLOB Content.
I am facing an issues with upload button. After browsing my upload file (ex: test.csv) then click on upload button the upload button got disappears. i need another help, can we restrict the upload files only csv. please provide sample code.
Regards,
Raj
Button disappearing may be an issue with your current APEX. Try some other APEX installation or apex.oracle.com
You can restrict only .csv just simply by validating the file extension.