I am trying to create a report on a table with BLOB data so that the report displays a "download" link for the BLOB data. I am trying to give users the ability to download files from a website.
I have been following the instructions in OBE "Defining and Viewing BLOB Data in Oracle Application Express 3.1".
In the report, for the BLOB column, I have specified values for the following fields in Report Attributes->Column Formatting->Number/Date Format->BLOB Download Format Mask:
Primary Key Column 1
MIME Type Column
BLOB Last Updated Column
However, when I run the report, I get the following error, even thought there is data loaded into the table:
ORA-01403: no data found
When I remove the BLOB Download Format Mask information for the BLOB column, then the data displays in the report, but without the "download" link for the BLOB data (which is expected):
Title Author Created BLOB_CONTENT
Siebel & RAC Best Practices Erik.Peterson 17-SEP-08 [datatype]
Siebel Grid Case Studies Mark.MacDonald 17-SEP-08 [datatype]
Siebel CRM Applications protected by Oracle Clusterware Kelly.Tan 17-SEP-08 [datatype]
Oracle Grid: The Optimal Platform for Siebel Solutions Mark.MacDonald 17-SEP-08 [datatype]
Any ideas why I'm seeing the ORA_01403 when I specify the BLOB Download Format Mask?
Edited by: user716599 on Sep 18, 2008 12:31 AM
Here is how I have solved this problem.
1. The select statement in the sql for the report should not include the BLOB column. I decided to select only 2 columns, the column that has the key and the column with the filename.
2. On the first column ( the primary key ) I put in the format statement that was simply DOWNLOAD:TABLENAME:BLOB_COLUMN:PRIMARY_KEY
This works. I believe that the Oracle error I was getting was because I was trying to apply this format statement to the actual BLOB column.
So, it appears that you can use any of the columns in the report to hold the DOWNLOAD format statement since in the format statement, you are defining the BLOB table, BLOB column and the primary key into that column.
Hope this helps,
I have the same problem. I have the following query:
select name, description,
It ran fine and return the records back. But when I put the blob format on, it returned with the "ORA-01403: no data found". But if I take the format of DOWNLOAD off, the data came back.
Any suggestion? Thanks!
That query doesn't look like it has a primary key in it to put into the format field. Or is name the unique key to your blob record? What exactly did you put as the format string?
The format statement does NOT go on the BLOB column. In fact, in your select statement for the report, you do not even need to select the BLOB column. You only have to identify the BLOB within the format statement.
So based on the data you supplied, your select statement for the report should probably be:
SELECT ID, NAME, DESCRIPTION, DBMS_LOB.GETLENGTH("CONTENTS") "CONTENTS"
WHERE ID = 100;
Then, put the FORMAT statement on the ID column of your report.
where: DOCUMENT = Table Name,
CONTENTS = The BLOB column
ID = The primary key for the DOCUMENT table.
That should be all the information you need to make the download work.