11 Replies Latest reply: Aug 28, 2014 3:27 PM by NewApexCoder RSS

    Creating a 'Download Media' Link on an Apex form

    NewApexCoder

      Apex 4.2

       

      I have a form based on a PERSONS table:

       

       

      Create table PERSONS
      (
      Person_Id int,
      Name varchar2(100),
      Occupation varchar2(100),
      Photo_id int
      );
      

       

      The "PHOTO_ID" field links to our PHOTO_MEDIA table

       

      Create table PHOTO_MEDIA
      (
      Photo_id int,
      File_name varchar2(100),
      Mime_type varchar2(100),
      Short_desc varchar2(200),
      Media blob
      );
      

       

      The form lists the persons information. In apex, I have an automated fetch process to fetch data from the PERSONS table. I am having trouble building a 'Download Media' link on this page. It's simple enough to build a link on the page, but getting the link to prompt a popup to download/save the media document is another issue. Since I have the PHOTO_ID field from the PERSONS table on the form, I tried querying the MEDIA field from the PHOTO_MEDIA table, but there is a conflict in data types. Get an error along the lines of...."Inconsistent data types, expecting number, got blob....". I have also looked into using apex_util.get_blob_file but nothing seems to be working correctly. Any help on this issue would be greatly appreciated. Thanks in advance.

        • 1. Re: Creating a 'Download Media' Link on an Apex form
          jrimblas

          If I understand your problem what you need is a new page whose sole purpose is to download the media.

          Keep your current page as is, build your link to go to this new page, say p900 and pass the photo_id to this new page.

          f?p=&APP_ID.:900::::P900_ID:&Pn_PHOTO_ID.

           

          Then the new page you create a Before Headers Process where you download your media and all the parameters you need.

          The code you need will look something like this:

           

           

            htp.init; 
            owa_util.mime_header(l_mime_type,false);
            htp.p('Content-Length: ' || dbms_lob.getlength(l_blob_content));
            htp.p('Content-Disposition: attachment; filename=' || l_filename || ';');
            owa_util.http_header_close;
          
            wpg_docload.download_file(l_blob_content);
            apex_application.stop_apex_engine;
          

           

          Even though you link to this page, your app will NOT redirect to it as that page only fetches and downloads the media.  Line 6 handles pushing your blob out.

          It is possible to do all this on the same page, but to my liking it gets a little messy plus it looks like you're reloading the page.

           

          Hope this helps.

           

          Thanks

          -Jorge

          • 2. Re: Creating a 'Download Media' Link on an Apex form
            NewApexCoder

            Thank you for the reply. I will give this a shot and see what happens. From your example, if I'm not necessarily redirecting to a new page, I'm assuming I will remain on my current page and not a random blank page, correct?

            • 3. Re: Creating a 'Download Media' Link on an Apex form
              jrimblas

              Your app will remain on the page you are. The download page will not even show or open. It's an interesting effect.  Basically you only use APEX to process and deliver the download, but not to render anything.

               

              Yes try it. The other benefit of this method is that the download page can have it's own page security (or be public), it's up to you.

               

              Thanks

              -Jorge

              • 4. Re: Creating a 'Download Media' Link on an Apex form
                NewApexCoder

                I'm trying to implement your example above. I'm a little confused as when I plugged in your code, several variables need to be declared, i.e., L_MIME_TYPE, L_BLOB_CONTENT, L_FILE_NAME. I realize I need to plug in my own fields from my PHOTO_MEDIA table, however, won't I need a fetch process on this page also to pull the data from the PHOTO_MEDIA table? Or do I just declare them in the process (Assuming its a pl/sql process, correct?)?

                 

                Also, since I'm passing my photo_id variable to this new page, won't I also need a page item to hold the value I'm passing?

                And I don't quite see a "Line 6"

                 

                Hope my questions made sense

                • 5. Re: Re: Creating a 'Download Media' Link on an Apex form
                  jrimblas

                  Yes you're correct.  You'll need to create a region (the template doesn't matter as it will not display), you'll need the item P900_ID to receive the value.

                   

                  In the process you do need to declare the variables and do your own select.  You don't need or want an auto fetch process.

                  Your process code will probably look like this:

                   

                  declare
                    l_file_name   photo_media.file_name%type;
                    l_mime_type   photo_media.mime_type%type;
                    l_media       photo_media.media%type;
                  begin
                    select file_name
                         , mime_type
                         , media
                      into l_file_name
                         , l_mime_type
                         , l_media
                      from photo_media
                     where photo_id = :P900_ID;
                  
                  
                    htp.init;  
                    owa_util.mime_header(l_mime_type,false); 
                    htp.p('Content-Length: ' || dbms_lob.getlength(l_media)); 
                    htp.p('Content-Disposition: attachment; filename=' || l_file_name || ';'); 
                    owa_util.http_header_close; 
                  
                    wpg_docload.download_file(l_media);
                    apex_application.stop_apex_engine;
                  end;
                  
                  

                  Thanks

                  -Jorge

                  • 6. Re: Creating a 'Download Media' Link on an Apex form
                    NewApexCoder

                    THis has been a huge help. I'm almost to the finish line. After testing, there were some minor issues.

                     

                    (1) There's something wrong with the download link. It's not passing the value of the PHOTO_ID to page 900. When I hover over the link I look to the bottom toolbar to see what the link contains, I get the following:

                    h ttp://ServerNameStuff/f?p=&APP_ID.:900:::::P900_ID: (blank)

                    So the ID is not being passed properly. Minor syntax issue that shouldn't be too tough to handle. As a temporary substitute, in the query, I set the photo_id to a numeric value instead of the passed in value.

                     

                    (2) I changed the types of the variables declared in the process to varchar2, varchar2, and blob. It wasn't recognizing the other types (photo_media.filenmae%type,...but thats ok for now

                     

                    (3) When I click the link, it takes me to the page that makes me login. Once I login, I am prompted to download the media document. Again, this is another security issue on the pages that I have that is fixable, or should be fixable (I hope) in my grand scheme of things.

                     

                    Overall, with a little more tweaking, things will be working perfectly. Thanks for all your help

                    • 7. Re: Creating a 'Download Media' Link on an Apex form
                      jrimblas

                      NewApexCoder wrote:

                       

                      THis has been a huge help. I'm almost to the finish line. After testing, there were some minor issues.

                       

                      (1) There's something wrong with the download link. It's not passing the value of the PHOTO_ID to page 900. When I hover over the link I look to the bottom toolbar to see what the link contains, I get the following:

                      h ttp://ServerNameStuff/f?p=&APP_ID.:900:::::P900_ID: (blank)

                      So the ID is not being passed properly. Minor syntax issue that shouldn't be too tough to handle. As a temporary substitute, in the query, I set the photo_id to a numeric value instead of the passed in value.

                       

                      (2) I changed the types of the variables declared in the process to varchar2, varchar2, and blob. It wasn't recognizing the other types (photo_media.filenmae%type,...but thats ok for now

                       

                      (3) When I click the link, it takes me to the page that makes me login. Once I login, I am prompted to download the media document. Again, this is another security issue on the pages that I have that is fixable, or should be fixable (I hope) in my grand scheme of things.

                       

                      Overall, with a little more tweaking, things will be working perfectly. Thanks for all your help

                      Regarding the link, depending on where you're building it (region, report, etc...) you need to use different substitution methods. If it's in a report you need the hash (#) notation. For example #PHOTO_ID# otherwise you want the ampersand-dot notation for substitution. i.e. &Pn_PHOTO_ID.

                      For the link you also need to include the SESSION_ID after the page number.  This is why you have to log in again.  Sorry, I missed that.  It should look like this (use the correct photo_id notation):

                      f?p=&APP_ID.:900:&SESSION.::::P900_ID:&Pn_PHOTO_ID.

                       

                      For the declares tying it to the table name is best because then if you change the table you don't have code out of sync.  So check my syntax, the table name must match yours as do the column names.

                       

                      Glad it's almost working.

                       

                      Thanks

                      -Jorge

                      • 8. Re: Creating a 'Download Media' Link on an Apex form
                        NewApexCoder

                        Right Right,..the &APP_SESSION variable. Yea I missed that too. Whoops. Now that thats working, its really just the link. I've tried this on a button and as post element text. I'm using the '&' notation. My other concern is that, if you're passing the value to a page item on page 900, but the process is running before headers load, how can the query in the process run correctly? The query includes the page item value that you're passing, but the page item value is recognized after the process. Or this shouldn't make a difference?

                         

                        It's either that or me not passing the variable correctly in the link thats giving me my ORA-01403: no data found error.

                         

                        ==============================

                         

                        It's giving me an error saying "Unable to find item ID for item "17629".

                         

                        17629 is the value of the photo_id

                        • 9. Re: Creating a 'Download Media' Link on an Apex form
                          jrimblas

                          The error "Unable to find item ID for item "17629"" means that you're missing a colon in the link because 17629 is probably a photo_id right? One more colon before P900_ID will push them both to the right positional location.

                           

                          Even with the process running before headers your item will have a value because APEX sets that onto session state via the link. Now the item never renders, but it does get set in session state.

                           

                          Thanks

                          -Jorge

                          • 10. Re: Creating a 'Download Media' Link on an Apex form
                            NewApexCoder

                            Sigh...adding that colon worked perfectly. All I coould do was laugh because I was banging on my keyboard because I was missing a colon. Thanks for everything. It is working and fully operational to my liking.

                            • 11. Re: Creating a 'Download Media' Link on an Apex form
                              jrimblas

                              Ha! It's the little details right? 

                              Awesome.