3 Replies Latest reply on Jan 5, 2013 3:29 AM by Yong Huang

    How to display download link with get_blob or get_blob_file_src ?

      Hello there,

      I am struggling to display a download link in a SQL query report.
      Based on the requirement, I have to create a sql query report that the source contains a series of union select query.

      Example here
      WITH current_engagement AS (
      SELECT defendant_id, engagement_id, pn, ethnicity, date_joined_aodt_court, case_manager_name
      , participation_conditions, discharge_conditions
      FROM engagement
      WHERE defendant_id = :PXX_DEF
      AND date_joined IS NOT NULL
      AND ( date_terminated IS NULL OR to_date(date_joined,'DD/MM/YYYY') > to_date(sysdate,'DD/MM/YYYY'))
      AND active = 1 AND ROWNUM = 1
      ORDER BY defendant_id DESC
      SELECT def.first_name, def.middle_name, def.surname, to_char(def.pn) AS "PN", to_char(def.nhi_number) AS "NHI"
      ,ce.ethnicity, to_char(ce.date_joined) as "DATE_JOINED ", ce.case_manager_name, ce.participation_conditions, ce.discharge_conditions
      FROM defendant def INNER JOIN current_engagement ce
      ON def.def_id = ce.defendant_id

      select ' <b>2. Phase Number</b>' , '<b>Date Started</b>' , '<b>Date Finised</b>' , '<b>Notes</b>' , null
      ,null , null , null , null , null
      from dual
      SELECT TO_CHAR(NVL(ph.phase_number,'No Phase Found')), TO_CHAR(ph.date_started, 'DD/MM/YYYY'), TO_CHAR(ph.date_finished, 'DD/MM/YYYY'), ph.notes, null
      , null , null , null , null , null
      FROM phase_membership ph INNER JOIN current_engagement ce
      ON ph.mpm_eng_id = ce.engagement_id
      WHERE to_date(ph.date_started,'DD/MM/YYYY') <= TO_DATE(sysdate,'DD/MM/YYYY')
      AND ( ph.date_finished IS NULL
      OR TO_DATE(ph.date_finished, 'DD/MM/YYYY') >= TO_DATE(sysdate,'DD/MM/YYYY') )
      AND ph.active = 1
      --more selects
      -- then the last select is to list all related documents with download links
      select '<b>6. Document Date</b>' , '<b>Document Type</b>' , '<b>Description</b>' , '<b>Download</b>'
      , null ,null , null , null , null , null
      from dual

      SELECT TO_CHAR(doc.date_received), doc.document_type, doc.comments, TO_CHAR(dbms_lob.getlength(doc.UPLOADED_FILE))
      , null ,null , null , null , null , null
      FROM document doc INNER JOIN current_engagement ce
      ON doc.d_eng_id = ce.engagement_id
      WHERE doc.active = 1

      This report is generated fine.
      But the requirement also ask to display a download link. And because it is union selects, all following selects must be the same as the first select column data types.
      In this case, it has to be CHAR. Otherwise, it will generate error as below
      ORA-01790: expression must have same datatype as corresponding expression

      So I follow the GET_BLOB_FILE_SRC Function step from
      I turn my last select into
      SELECT TO_CHAR(doc.date_received), doc.document_type, doc.comments,
      CASE WHEN NVL(dbms_lob.getlength(doc.UPLOADED_FILE),0) = 0
      , null ,null , null , null , null , null
      FROM document doc INNER JOIN current_engagement ce
      ON doc.d_eng_id = ce.engagement_id
      WHERE doc.active = 1

      Which the result inside the hreg is

      while if I use Interactive report the link is
      and it works well.

      I do a research and know that get_blob_file_source link to the source of the column / field name located in the first parameter.
      So I had put the UPLOADED_FILE column, DOCUMENT table in the source of the column PN.
      But it still does not show anything that close to the correct one that is generated by interactive report.

      If anyone has any ideas, please help. If I need to manually write using get_blob , please give me an example.
      I urgently need to do this report.
      The version of APEX we use is Application Express
      And I cannot ask for an upgrade to 4.2 till the end of next year.

      Please help.
      Thanks a lot in advance.