14 Replies Latest reply: Mar 20, 2013 10:12 AM by Sky13 RSS

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

    Sky13
      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
          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
            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
              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
                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
                  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
                    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
                      ???

                      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
                        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
                          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
                          -------------------------------------------------------------------
                          • 11. Re: Need a Little Help embedding a Hyper Link in Report  based on Dynamic SQL
                            Denes Kubicek
                            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
                              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
                                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
                                  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!