3 Replies Latest reply: Jan 4, 2013 9:29 PM by Yong Huang RSS

    How to display download link with get_blob or get_blob_file_src ?

    Ann586341
      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
      UNION ALL

      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
      UNION ALL
      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
      UNION ALL
      --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

      UNION ALL
      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
      http://docs.oracle.com/cd/E14373_01/apirefs.32/e13369/apex_util.htm#AEAPI129
      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
      THEN NULL
      ELSE
      'Download'
      END
      , 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
      http://app_address_here/f?p=208:48:1303335952329758:::::

      while if I use Interactive report the link is
      http://app_address_here/apex_util.get_blob?s=1303335952329758&a=208&c=66645817090447568&p=48&k1=12&k2=&ck=A18D20D407435BD649EA3399EC27BC00&rt=CR
      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 4.0.2.00.07
      And I cannot ask for an upgrade to 4.2 till the end of next year.

      Please help.
      Thanks a lot in advance.
      Ann.