This discussion is archived
14 Replies Latest reply: Mar 20, 2013 8:12 AM by Sky13 RSS

Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL

Sky13 Newbie
Currently Being Moderated
I am trying to have a column in my report link to a document on my network when the column in the report is clicked. I have a table that contains a row for each document I want to link to. The URL to the document is stored in a column named NDDL.DOC_LINK. I would like the text "Linked" to appear as the value in the report column and to be a hyper link to the document.

The report is a "Classic Report" based on PLSQL retuning a SQL stament. I am on Apex version 4.2.1.00.08

I have been fighting with the '''''' stuff and can not get it worked out. have pasted the line in question but I can not get it to display correctly so I am hoping someone can help out.
...
  q := q || '  NDDL.DOC_TITLE as FILE_NAME, '; 
  q := q || '  NDDL.DOC_DESC, ';
  q := q || '  ''<a href="http:'' || NDDL.DOC_LINK || ''target="_blank"> </a>'' as LINKED , ';
  q := q || '  NDDL.MOD_DATE, ';  
...
The # in the line above should be:
{code}
http:'' || NDDL.DOC_LINK || ''target="
Thanks!

Edited by: Sky13 on Mar 7, 2013 12:45 PM                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                            
  • 1. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I think the whole code is required. Put it between:

    &#91;code&#93;

    &#91;/code&#93;

    and it should display.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 2. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    Hi Denes,
    I tried it and it seemed to make no difference in the display.
  • 3. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I meant you should post the entire code in this thread.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 4. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    One more cup of coffee and I would have caught that one :)
    declare
      q varchar2(4000);
    begin
      q := 'select * from ( ';
      q := q || 'select ';
      q := q || '  NDDC.NDDC_UID, ';
      q := q || '  NDDC.NDND_UID, ';
      q := q || '  NDDC.LSVL_DOC_CAT_UID, ';
      q := q || '  NDDC.DATE_POSTED, ';
      q := q || '  NDDC.CREATE_DATE, ';
      q := q || '  NDDC.CREATE_BY, ';
      q := q || '  NDDC.MOD_DATE, ';
      q := q || '  NDDC.MOD_BY, ';
      q := q || '  NDDC.MIME_TYPE, ';
      q := q || '  NDDC.FILE_NAME, ';
      q := q || '  NDDC.CHARACTER_SET, ';
      q := q || '  NDDC.BLOB_MOD_DATE, ';
      q := q || '  NULL as DOC_DESC, ';
      q := q || '  NULL as DOC_LINK, ';  
      if :P24_DOC_CONTAINS is not null then
        q := q || '  score(1) as SCORE ';
      else
        q := q || ' NULL as SCORE ';
      end if;
      q := q || 'from '; 
      q := q || '  NDDC_NODE_DOC NDDC ';
      q := q || 'where ( ';
      q := q || ' INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0  or ';
      q := q || ' INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0  or ';
      q := q || ' INSTR(UPPER(MIME_TYPE),UPPER(NVL(:P24_SEARCH,MIME_TYPE))) > 0  or ';
      q := q || ' INSTR(UPPER(FILE_NAME),UPPER(NVL(:P24_SEARCH,FILE_NAME))) > 0 '; 
      q := q || ') ';
      if :P24_DOC_CONTAINS is not null then
        q := q || '  and CONTAINS(document, :P24_DOC_CONTAINS,1) > 0 ';
      end if;
      q := q || 'union all ';
      q := q || 'select ';
      q := q || '  NULL as NDDC_UID, ';
      q := q || '  NDDL.NDND_UID, ';
      q := q || '  NDDL.LSVL_DOC_CAT_UID, ';
      q := q || '  NDDL.DATE_POSTED, ';
      q := q || '  NDDL.CREATE_DATE, ';
      q := q || '  NDDL.CREATE_BY, ';
      q := q || '  NDDL.MOD_DATE, ';
      q := q || '  NDDL.MOD_BY, ';
      q := q || '  NULL as MIME_TYPE, '; 
      q := q || '  NDDL.DOC_TITLE as FILE_NAME, '; 
      q := q || '  NULL as CHARACTER_SET, ';
      q := q || '  NDDL.MOD_DATE as BLOB_MD_DATE, ';  
      q := q || '  NDDL.DOC_DESC, ';
      q := q || '  '''<a href="http:' || NDDL.DOC_LINK || '-.htm" target="_blank"> </a>''' as DOC_LINK , ';
      q := q || '  NULL as SCORE '; 
      q := q || 'from ';
      q := q || '  NDDL_NODE_DOC_LINK NDDL ';
      q := q || 'where ';
      q := q || '  ( INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0 '; 
      q := q || '  or INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0 ';
      q := q || '  or INSTR(UPPER(DOC_TITLE),UPPER(NVL(:P24_SEARCH,DOC_TITLE))) > 0 ';
      q := q || '  or INSTR(UPPER(DOC_DESC),UPPER(NVL(:P24_SEARCH,DOC_DESC))) > 0 ) ';
      q := q || '  ) ';
      --htp.p(q);
      return q;
    
    end;
  • 5. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    This should work:
    DECLARE
       q   VARCHAR2 (4000);
    BEGIN
       q := 'select * from ( ';
       q := q || 'select ';
       q := q || '  NDDC.NDDC_UID, ';
       q := q || '  NDDC.NDND_UID, ';
       q := q || '  NDDC.LSVL_DOC_CAT_UID, ';
       q := q || '  NDDC.DATE_POSTED, ';
       q := q || '  NDDC.CREATE_DATE, ';
       q := q || '  NDDC.CREATE_BY, ';
       q := q || '  NDDC.MOD_DATE, ';
       q := q || '  NDDC.MOD_BY, ';
       q := q || '  NDDC.MIME_TYPE, ';
       q := q || '  NDDC.FILE_NAME, ';
       q := q || '  NDDC.CHARACTER_SET, ';
       q := q || '  NDDC.BLOB_MOD_DATE, ';
       q := q || '  NULL as DOC_DESC, ';
       q := q || '  NULL as DOC_LINK, ';
    
       IF :p24_doc_contains IS NOT NULL
       THEN
          q := q || '  score(1) as SCORE ';
       ELSE
          q := q || ' NULL as SCORE ';
       END IF;
    
       q := q || 'from ';
       q := q || '  NDDC_NODE_DOC NDDC ';
       q := q || 'where ( ';
       q :=
             q
          || ' INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0  or ';
       q := q || ' INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0  or ';
       q :=
             q
          || ' INSTR(UPPER(MIME_TYPE),UPPER(NVL(:P24_SEARCH,MIME_TYPE))) > 0  or ';
       q := q || ' INSTR(UPPER(FILE_NAME),UPPER(NVL(:P24_SEARCH,FILE_NAME))) > 0 ';
       q := q || ') ';
    
       IF :p24_doc_contains IS NOT NULL
       THEN
          q := q || '  and CONTAINS(document, :P24_DOC_CONTAINS,1) > 0 ';
       END IF;
    
       q := q || 'union all ';
       q := q || 'select ';
       q := q || '  NULL as NDDC_UID, ';
       q := q || '  NDDL.NDND_UID, ';
       q := q || '  NDDL.LSVL_DOC_CAT_UID, ';
       q := q || '  NDDL.DATE_POSTED, ';
       q := q || '  NDDL.CREATE_DATE, ';
       q := q || '  NDDL.CREATE_BY, ';
       q := q || '  NDDL.MOD_DATE, ';
       q := q || '  NDDL.MOD_BY, ';
       q := q || '  NULL as MIME_TYPE, ';
       q := q || '  NDDL.DOC_TITLE as FILE_NAME, ';
       q := q || '  NULL as CHARACTER_SET, ';
       q := q || '  NDDL.MOD_DATE as BLOB_MD_DATE, ';
       q := q || '  NDDL.DOC_DESC, ';
       q := q || '  ''<a href="#" target="_blank"> </a>'' as DOC_LINK , ';
       q := q || '  NULL as SCORE ';
       q := q || 'from ';
       q := q || '  NDDL_NODE_DOC_LINK NDDL ';
       q := q || 'where ';
       q :=
          q
          || '  ( INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0 ';
       q := q || '  or INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0 ';
       q :=
             q
          || '  or INSTR(UPPER(DOC_TITLE),UPPER(NVL(:P24_SEARCH,DOC_TITLE))) > 0 ';
       q :=
             q
          || '  or INSTR(UPPER(DOC_DESC),UPPER(NVL(:P24_SEARCH,DOC_DESC))) > 0 ) ';
       q := q || '  ) ';
       --htp.p(q);
       RETURN (q);
    END;
    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 6. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    Hi Denes,
    Thanks for the help but I can not see the line in question correctly.
  • 7. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    ???

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 8. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    For some reason the forum is not rendering my test correctly. I have tried to view it in Fierfox and IE and nether show me the detail of what is on the line in question.
    q := q || ' ''<a href=The *http:' || NDDL.DOC_LINK ||* part is replaced with a *#* when I view it.

    I can email you the true code.
  • 9. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    O.K. do it.

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 10. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    Just sent it... Thanks!!!!!!
  • 11. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    I anwered your email. The PL/SQL block is like this:
    /* Formatted on 2013/03/07 23:05 (Formatter Plus v4.8.8) */
    DECLARE
       q   VARCHAR2 (4000);
    BEGIN
       q := 'select * from ( ';
       q := q || 'select ';
       q := q || '  NDDC.NDDC_UID, ';
       q := q || '  NDDC.NDND_UID, ';
       q := q || '  NDDC.LSVL_DOC_CAT_UID, ';
       q := q || '  NDDC.DATE_POSTED, ';
       q := q || '  NDDC.CREATE_DATE, ';
       q := q || '  NDDC.CREATE_BY, ';
       q := q || '  NDDC.MOD_DATE, ';
       q := q || '  NDDC.MOD_BY, ';
       q := q || '  NDDC.MIME_TYPE, ';
       q := q || '  NDDC.FILE_NAME, ';
       q := q || '  NDDC.CHARACTER_SET, ';
       q := q || '  NDDC.BLOB_MOD_DATE, ';
       q := q || '  NULL as DOC_DESC, ';
       q := q || '  NULL as DOC_LINK, ';
    
       IF :p24_doc_contains IS NOT NULL
       THEN
          q := q || '  score(1) as SCORE ';
       ELSE
          q := q || ' NULL as SCORE ';
       END IF;
    
       q := q || 'from ';
       q := q || '  NDDC_NODE_DOC NDDC ';
       q := q || 'where ( ';
       q :=
             q
          || ' INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0  or ';
       q := q || ' INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0  or ';
       q :=
             q
          || ' INSTR(UPPER(MIME_TYPE),UPPER(NVL(:P24_SEARCH,MIME_TYPE))) > 0  or ';
       q := q || ' INSTR(UPPER(FILE_NAME),UPPER(NVL(:P24_SEARCH,FILE_NAME))) > 0 ';
       q := q || ') ';
    
       IF :p24_doc_contains IS NOT NULL
       THEN
          q := q || '  and CONTAINS(document, :P24_DOC_CONTAINS,1) > 0 ';
       END IF;
    
       q := q || 'union all ';
       q := q || 'select ';
       q := q || '  NULL as NDDC_UID, ';
       q := q || '  NDDL.NDND_UID, ';
       q := q || '  NDDL.LSVL_DOC_CAT_UID, ';
       q := q || '  NDDL.DATE_POSTED, ';
       q := q || '  NDDL.CREATE_DATE, ';
       q := q || '  NDDL.CREATE_BY, ';
       q := q || '  NDDL.MOD_DATE, ';
       q := q || '  NDDL.MOD_BY, ';
       q := q || '  NULL as MIME_TYPE, ';
       q := q || '  NDDL.DOC_TITLE as FILE_NAME, ';
       q := q || '  NULL as CHARACTER_SET, ';
       q := q || '  NDDL.MOD_DATE as BLOB_MD_DATE, ';
       q := q || '  NDDL.DOC_DESC, ';
       q :=
             q
          || '  ''<a href="http:'''
          || '||nddl.doc_link||'''
          || '-.htm" target="_blank"> </a>'' as DOC_LINK , ';
       q := q || '  NULL as SCORE ';
       q := q || 'from ';
       q := q || '  NDDL_NODE_DOC_LINK NDDL ';
       q := q || 'where ';
       q :=
          q
          || '  ( INSTR(UPPER(CREATE_BY),UPPER(NVL(:P24_SEARCH,CREATE_BY))) > 0 ';
       q := q || '  or INSTR(UPPER(MOD_BY),UPPER(NVL(:P24_SEARCH,MOD_BY))) > 0 ';
       q :=
             q
          || '  or INSTR(UPPER(DOC_TITLE),UPPER(NVL(:P24_SEARCH,DOC_TITLE))) > 0 ';
       q :=
             q
          || '  or INSTR(UPPER(DOC_DESC),UPPER(NVL(:P24_SEARCH,DOC_DESC))) > 0 ) ';
       q := q || '  ) ';
       --htp.p(q);
       DBMS_OUTPUT.put_line (q);
    END;
    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 12. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    Hi Denes,
    Thanks for all the help but for some reason I can not get this working. Let me step back a bit.

    I created a document library that allows users to upload documents allowing access to the rest of the users on our internal network. Now I want to allow them to simply record a link to a document out on the network that will let other users access the document. This will mainly be used for large video files and other very large files we do not want in the database. I have tried this on IE and FireFox.

    Am I going about this the right way or is there a better approach?

    Thanks!
  • 13. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Denes Kubicek Oracle ACE Director
    Currently Being Moderated
    You have had several problems if I remember it right. First, your PL/SQL didn't run properly. Then it didn't show what you expected to see. At the last, it didn't open the location you expected to open. I solved at least two of those three issues. Now I see that you have a third issue and trying to use that report to directly give access to some ressources within your network. Something like:

    \\kgi002.kgisystems.com\development\dba\dba_support\DBA_Support_Schedule.xls

    Actually, you can't do it that way and wrap this path within &lt;a href="" tags to get it working. You are probably expecting the click to that link to open the referenced document. :) This is a security issue and this problem has been discussed many times in this forum - searching on three words "open document path" would lead you to many answers:

    Re: Open Windows Explorer with path from IR? (allegedly it could work)
    open document from interactive report

    Denes Kubicek
    -------------------------------------------------------------------
    http://deneskubicek.blogspot.com/
    http://www.apress.com/9781430235125
    http://apex.oracle.com/pls/apex/f?p=31517:1
    http://www.amazon.de/Oracle-APEX-XE-Praxis/dp/3826655494
    -------------------------------------------------------------------
  • 14. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
    Sky13 Newbie
    Currently Being Moderated
    Hi Denes,
    You are right. I did have a bit of scope creep on this one. I did not form the question correct in the first place. Thanks for all the help you have moved me forward in my efforts a great deal. I will research the leads you have provided me. Thanks!

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points