CODEWITHSUNDEEP
oracle-apex
Pablo
Pablo

Reputation: 29519

Is it possible to create download link for Left Joined classic report?

I have classic report in the page, which has the following SQL query:

SELECT CELLS.ID, CELLS.NAME, CELLS.NUM, CELLS.AC_ID, AC.SERIAL,AC.FILE_NAME, AC.FILE_DATA
FROM CELLS 
LEFT JOIN AC
ON CELLS.AC_ID = AC.ID
WHERE CELLS.AC_ID IS NOT NULL
ORDER BY CELLS.NUM

I would like to have download link for AC.FILE_DATA, which is BLOB. So in Attributes FILE_DATA column I set the following:

Type: Download BLOB
Table Name: AC
BLOB Column: FILE_DATA
Primary Key Column 1: ID

The page then is generating error in the place of classic report region:

Error: ORA-06502: PL/SQL: numeric or value error: character to number conversion error

Looking in Debug log shows some more:

Exception in "AC Region": Sqlerrm: ORA-06502: PL/SQL: numeric or value error: character to number conversion error Backtrace: ORA-06512: at "APEX_050100.WWV_RENDER_REPORT3", line 7965

Without AC.FILE_DATA in left join there is no exception. So can I actually have blob download column when using joins in report query?

Upvotes: 3

Views: 1233

Answers (1)

Littlefoot
Littlefoot

Reputation: 143023

As far as I can tell, it has nothing to do with (left) join but the way you create the download link. It should NOT be the BLOB column name, but this:

dbms_lob.getlength(ac.file_data) download

Or, applied to your query,

SELECT CELLS.ID, 
       CELLS.NAME, 
       CELLS.NUM, 
       CELLS.AC_ID, 
       AC.SERIAL,
       AC.FILE_NAME, 
       --
       dbms_lob.getlength(AC.FILE_DATA) download  --> this
FROM CELLS 
LEFT JOIN AC
ON CELLS.AC_ID = AC.ID
WHERE CELLS.AC_ID IS NOT NULL
ORDER BY CELLS.NUM

"Download" column settings:

  • Type: Download BLOB
  • Table name: AC
  • BLOB Column: FILE_DATA
  • Primary Key Column 1: ID

Save, Run - should be just fine.

Upvotes: 2

Related Questions