8 Replies Latest reply on Nov 17, 2017 3:21 PM by pawuyulan

    How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)

    pawuyulan

      I create this thread in hope that Google list it for the people searching it in the future, because the information I found was fragmentary, outdated and confusing.

       

      I don't know much of SQL or APEX, so I welcome advice and improvements.

       

      This is based on this webpage, which is outdated and doesn't works in APEX 5.

      IMPORTANT: you don't need any of this if you have permission to access the database. SQL Developer lets you upload images directly on your table as BLOB.

       

      EDIT: The easier method is probably using automated row processing, which is described here

      Below is explained the method using the APEX_APPLICATION_TEMP_FILES table.

       

      To upload an image from file to a table

       

      An application should be created for the uploading. First, we need a table to store the images:

       

      We will create a table named "MYIMAGES_TBL"

       

      CREATE TABLE  "MYIMAGES_TBL" 
         ( "ID" NUMBER NOT NULL ENABLE, 
           "IMAGE_NAME" VARCHAR2(400), 
           "FILENAME" VARCHAR2(350), 
           "MIME_TYPE" VARCHAR2(255), 
           "DOC_SIZE" NUMBER,
           "CHARSET" VARCHAR2(128), 
           "LAST_UPDATE_DATE" DATE,
           "CONTENT" BLOB, 
      CONSTRAINT "MYIMAGES_TBL_PK" PRIMARY KEY ("ID")
        USING INDEX  ENABLE
         )
      /
      CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_MYIMAGES_TBL" 
        before insert on "MYIMAGES_TBL"               
        for each row  
      begin   
        if :NEW."ID" is null then 
          select "MYIMAGES_TBL_SEQ".nextval into :NEW."ID" from sys.dual; 
        end if; 
      end; 
      /
      ALTER TRIGGER  "BI_MYIMAGES_TBL" ENABLE
      /
      

       

      Then we need to create a page in APEX's App Builder to upload the images

      On this page we add 3 items, of type "File Browse..." to pick the files, "Text" to write a name for the image, and "Display Image", to show a preview

       

      Untitled.png

      Note that "File Browse..." item is named FILE_BROWSER, and his property "Settings>Storage type" is set to "Table APEX_APPLICATION_TEMP_FILES".

      "Table APEX_APPLICATION_TEMP_FILES" is a table where the uploaded files will be temporarily stored. (In older APEX versions. it used to be named WWV_FLOW_FILES, so you will find useless information with that name which cause confusion and no more works).

       

      As shown in the former image, the FILE_BROWSER is set

      • Properties>Label>Label="Pick an image file"

       

      We need a name for the image, so we will name the "Text" item "FILE_NAME", to allow the user to edit the name.

      We will use "FILE_NAME" text, as name, to store the image on the table, so, to be sure that it is filled, we will add a dynamic action to FILE_BROWSER, whose action is "Execute JavaScript Code", and his code is

       

      //Extract filename from full path
      var filename = $v('FILE_BROWSER').split('\\').pop().split('/').pop();
      //Copy file name
      $s("FILE_NAME",filename);
      

       

      Untitled.png

       

      The Dynamic action:

      • Will be named “New file chosen”
      • Fire When Event Result Is: True
      • Set to trigger with the setting "When>Event=change"

      Untitled.png

       

      To display the preview of the image

       

      We add a "Display Only" item named "IMAGE", set to:

      • Properties>Settings>Based On ="Image URL stored in Page Item Value"
      • Source>Type=Static Value=""
      • Label>Label="Image preview:"

       

      The image will not be uploaded to the database until the page is submitted, so, to show a preview, it is necessary to fetch the image from the user computer.

      To do that, we add a javascript code in "Page Name>Properties>JavaScript>Function and Global Variable Declaration"

      Untitled.jpg

       

      We introduce this code:

       

      function readURL(input) {
          if (input.files &&
              input.files[0] &&
              input.files[0].type.match('image.*')) {
              var reader = new FileReader();
      
              reader.onload = function (e) {
                  $('#IMAGE').attr('src', e.target.result);
              }
      
              try {
                  reader.readAsDataURL(input.files[0]);
              }
              catch(err) {
                  alert("error: " + err.message);
              } 
          }
      }
      
      $("#FILE_BROWSER").change(function(){
          readURL(this);
      });
      

       

       

      To copy the data to the table

       

      The FILE_BROWSER item uploads the image file to APEX_APPLICATION_TEMP_FILES, after the page is submitted. Then we need to copy it to "MYIMAGES_TBL".

      To do that, we make a procedure named Insert_imageBLOB_to_Table set to:

      • "Identification>type" = PL/SQL code
      • "Execution options>Point"= After submit

      Untitled.jpg

      As PL/SQL code, we write:

       

      declare
        doc_size integer;
        Upload_blob blob;
      begin
         --Copy BLOB to Upload_blob variable
         select blob_content
         into   Upload_blob
         from   APEX_APPLICATION_TEMP_FILES
         where  name = :FILE_BROWSER;
      
         --Get BLOB size
         doc_size := dbms_lob.getlength(Upload_blob);
      
      --Copy data to table MyIMAGES_TBL
      if doc_size <= 1000000 then
         insert into MyIMAGES_TBL (
                IMAGE_NAME, FILENAME,
                MIME_TYPE, DOC_SIZE,
                CONTENT )
         select :FILE_NAME, filename,
                mime_type, doc_size,
                blob_content
         from   APEX_APPLICATION_TEMP_FILES
         where  name = :FILE_BROWSER;
           
         --Delete temp files  
         delete from APEX_APPLICATION_TEMP_FILES where name = :FILE_BROWSER;
      else
         delete from APEX_APPLICATION_TEMP_FILES where name = :FILE_BROWSER;
         commit;
         raise_application_error(-20001,'Cannot upload pictures bigger than 1MB!');
      end if;
      exception
       when others then
           raise_application_error(-20001,'Error when uploading image!');    
      end;
      

       

      Note that at the end, the file in APEX_APPLICATION_TEMP_FILES is deleted. This is probably unnecessary, because the FILE_BROWSER item has a setting to “Purge files after submission”, and we had set this code to run at submission.

       

       

      To run this code, we simply trigger submission with a button. We will add a button which we may label "Upload", set to "Behavior>Action=Submit page"

       

      Untitled.jpg

       

      To load the images, we simply run it, choose the file, edit his name if we want, and press "upload"

       

      Untitled.jpg

       

      We can check that the images are stored on "MYIMAGES_TBL", by going to "SQL Workshop>MYIMAGES_TBL>Data"

       

      Untitled.png

        • 1. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
          pawuyulan

          How to display an image BLOB into a "Display Image" item dynamically (For Dummies)

           

          We have the image stored in the table "MYIMAGES_TBL". As I understand, to display it, we need a procedure to convert that data to HTML.

           

          How to do that, is undocumented here, and you are supposed to use this undocumented function.

           

          We will implement the advice explained here in Joel Kallman's blog

           

          First, we need a way for the user to select the image to be displayed, from the BLOBs stored in  "MYIMAGES_TBL"

           

          So we create 2 items, one of type "Select List", and one hidden to store the ID of the image on "MYIMAGES_TBL"

           

          The "Select List" item is set to

          • Properties>Identification>Name=LIST_OF_UPLOADED_IMAGES
          • Properties>List of Values>Type=SQL query
          • SQL query is
          SELECT  IMAGE_NAME,
                  ID
          FROM MYIMAGES_TBL
          ORDER BY IMAGE_NAME
          

          This would make a list of values of stored IMAGE_NAME.

          The Value of the item LIST_OF_UPLOADED_IMAGES will be ID of the selected name, and the IMAGE_NAME would be displayed on the Item, as is poorly documented here.

           

          1.png

           

          The second (hidden) item is set to:

          • Properties>Identification>Name=IMAGE_ID
          • Properties>Identification>Type=Hidden

          Instead of "Hidden" we can use "Display Only" if we want to see his value on screen

           

          We will store on IMAGE_ID the table ID, of the image selected from the user with a dynamic action.

          To do that we add a Dynamic action to the item LIST_OF_UPLOADED_IMAGES

          We set it to

          • Properties>Identification>Name=Change
          • Properties>When>Event=Change

          So, it will trigger when the user select a value from the list of values.

           

          5.png

          For that Dynamic action, we add a TRUE action set to

          • Properties>Identification>Action=Execute JavaScript Code
          • Properties>Settings>Code is
          $s("IMAGE_ID", $v('LIST_OF_UPLOADED_IMAGES') );
          

           

          This code writes in IMAGE_ID, the ID of the selected IMAGE_NAME, which as I explained before, is the "Value" selected on LIST_OF_UPLOADED_IMAGES

           

          4.jpg

           

          We add another item named IMAGE_STORED_IN_TABLE to render the selected image. It is set to:

          • Properties>Identification>Name=IMAGE_STORED_IN_TABLE
          • Properties>Identification>Type=Display Image
          • Properties>Settings>Based On=Image URL stored in Page Item Value
          • Properties>Source>Type=Static Value=""

           

          So, this image will be based on an URL, and we need to update the URL to display the selected image

          6.png

           

          To update the URL, we add another TRUE Dynamic action to LIST_OF_UPLOADED_IMAGES

          The Dynamic action is set to

          • Properties>Identification>Action=Execute JavaScript Code
          • Properties>Settings>Code is
          var URL='f?p=&APP_ID.:0
          :&APP_SESSION.:APPLICATION_PROCESS=MyImages_TBL_BLOB _To_URL:::IMAGE_ID:' + $v('IMAGE_ID');
          
           $('#IMAGE_STORED_IN_TABLE').attr('src', URL); 
          

          The last code line updates the URL on the source ("src") attribute of the item IMAGE_STORED_IN_TABLE

          7.png

          The URL is undocumented here

          Note that it call a process named MyImages_TBL_BLOB _To_URL, and it passes a parameter named IMAGE_ID

           

          That process downloads the BLOB data from the table, and converts it to an URL format.

          To create it, we go to "Application #number#>Shared Components>Application Processes>Create"

           

          8.png

           

          9.png

          We set it to

          • Name=MyImages_TBL_BLOB _To_URL
          • Point=Ajax Callback: run this application process ...

           

           

          10.png

          We click "Next", insert this code, and click "Next":

          Declare
              c1 MYIMAGES_TBL%ROWTYPE;
          begin
              select *
              into c1
              from MYIMAGES_TBL
              where id = :IMAGE_ID;
              
              --Starts generating HTML
              sys.htp.init;
              sys.owa_util.mime_header( c1.MIME_TYPE, FALSE );
              sys.htp.p('Content-length: ' || sys.dbms_lob.getlength( c1.CONTENT));
              sys.htp.p('Content-Disposition: attachment; filename="' || c1.FILENAME || '"' );
              sys.htp.p('Cache-Control: max-age=3600');  -- tell the browser to cache for one hour, adjust as necessary
              sys.owa_util.http_header_close;
              sys.wpg_docload.download_file( c1.CONTENT );
          
              --Stop generation of HTML
              apex_application.stop_apex_engine;
          end;
          

           

          <rant>

          Note that this code has hardcoded MYIMAGES_TBL, and MYIMAGES_TBL%ROWTYPE

           

          That is absurd, because every time we need to display an image, stored on a different table, (with different name, columns and structure), we would need to duplicate this code, and every time we need to debug or upgrade it, we would need to update every copy.

           

          There are workarounds:

          • We can pass the table and column names as multiple parameters in the URL (which are undocumented).
          • We are forced to make a Dynamic query (using the EXEC command,-which is undocumented here-, instead of SELECT).

          EXEC introduces security risks (SQL injection), so we would need also to check that the table and column names are valid and exist, and do not include suspicious code, for which we need regular expressions and an expertise level on SQL which you probably not have if you are reading this.

           

          Sorry, but the situation is this precarious, and nobody seems to care.

          </rant>

          We continue, on the next screen setting:

          • "Condition type=Always"

          Then we click "Create process"

           

          11.png

          Then we run the application, and it should show the image when we select the image name:

           

          12.jpg

           

          We may add custom attributes to the displayed image by editing the Display Image Item

          • Properties>Advanced>CustomAttributes=style="max-width:100%;height:auto"

          It sets max-width and height to automatically adapt to the container size

           

          here we see (down) the properties applied to the uploaded image, which doesn't fits the screen (up)

          • 2. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
            Bernhard FW

            pls take a look at the Packaged Application "Sample Data loading" - everything is explained there in detail.

            good luck !!

            Bernhard

            • 3. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
              Pavel_p

              Hi,

              no offense but I'm not sure if

              I don't know much of SQL or APEX

              is the best premise for writing guides for dummies. There is a declarative support for uploading files in APEX, so I would suggest to study the Sample File Upload and Download packaged application, rather than reading some obscure blog-posts, even the Sample Database Application comes with a nice example how to handle images. It's not obvious why you're doing all these things but some general ideas how to upload BLOBs to the database.

              You either want to store files permanently, in such a case you probably want to upload them to some table or you can use the predefined view APEX_APPLICATION_TEMP_FILES as a temporary storage. In both cases it's not necessary to write a single line of code, just set File Upload item's attributes. Also to upload the file to APEX_APPLICATION_TEMP_FILES first, then check its size and eventually copy it to the target table and delete it from the temp table is inefficient and does not make any sense. It's a complete misunderstanding how transactions work in APEX and exception handling is something exceptional as well (the second message overrides the first one).

              If you want to validate the file size, just use the built-in validation (that's why we have them after all). Some validation like this (type PL/SQL Function Returning Error Message) does the trick.

              declare
                K_MAX_FILE_LENGTH constant number := 1024;--adjust according to your needs
                l_img_blob blob;
              begin
                select f.blob_content into l_img_blob from apex_application_temp_files f where f.name = :p1_new;
                if dbms_lob.getlength(l_img_blob) > K_MAX_FILE_LENGTH then
                  return 'Uploaded file exceeds the maximum allowed file size. Max file length = ' || K_MAX_FILE_LENGTH || ', uploaded file length = ' || dbms_lob.getlength(l_img_blob);
                end if;
              end;
              

              If the validation fails, the transaction is rolled back (no code at all is needed) and the user gets a nice red error message next to the appropriate item.

              Regards,

              Pavel

              • 4. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
                pawuyulan

                Pavel_p wrote:

                no offense but I'm not sure if

                I don't know much of SQL or APEX

                is the best premise for writing guides for dummies.

                How could I be offended. I totally agree.

                 

                The only reason I wrote this is because it was impossible to find it when I needed it.

                It should had been done already.

                There is a declarative support for uploading files in APEX, so I would suggest to study the Sample File Upload and Download packaged application, rather than reading some obscure blog-posts

                Ok. I check it. I start running, to see what it does. It takes long to find where the actual "file upload" item is. When I find it, I need to know where his code is. The only way is to hack it with browser inspect tools. I find his name is P12_FILE_BLOB. There is no search tool in APEX to find where the control is, and there are 13 pages to search in. But when I find it, I have no way to know what other code is related to it, or where is that code stored.

                 

                The running page has some instructions

                Look at it from a newbie viewpoint.

                • I need to make a "hello world" application.
                • I need an image for it.
                • I need to make an upload application to upload the image, just to make a "hello world" application.
                • It says "Add an unconditional page rendering page process". It should be an hyperlink to the explanation of what is an "unconditional page rendering page process".
                • It says "Choose Data Manipulation and click Next". How do I do that? Where is the "Data Manipulation" option to be chosen?

                At this point, I'm deadly lost.

                 

                the Sample Database Application comes with a nice example how to handle images.

                No. Not at all.

                • Sample Database Application shows images inside an "Interactive report" item, which doesn't works like an "Display image" item.
                • It is too large to be didactic.
                • it does not even has instructions like the Sample File Upload and Download application.
                • It doesn't comes with any documentation at all, explaining what it does, how it works,and why.
                • If apex had a step by step debugger, then I could run it step by step, and see how it works. It doesn't even has breakpoints.If I run it, I have absolutely no information on what code is running and why.
                • The only way to learn anything from those samples, is to use hacking tools to reverse engineering it.

                 

                You do not realize that for a newbie, Apex looks like a bunch of very different tools, programming languages, and code randomly spread in many different places, very badly linked together in a very amateur and clunky way. And I suspect that for an expert it looks the same.

                There is not obvious at all, what code is related to what control, or when it runs in the browser or the database, and there are too many different places where the same code could be placed.

                 

                For example, I lost weeks believing that the File Browse control uploaded the files to APEX_APPLICATION_TEMP_FILES before page submission, because that important aspect is not clearly documented. I wasted a lot of time trying to figure why showing an image preview didn't worked at all.

                 

                Pavel_p wrote:

                 

                You either want to store files permanently, in such a case you probably want to upload them to some table or you can use the predefined view APEX_APPLICATION_TEMP_FILES as a temporary storage. In both cases it's not necessary to write a single line of code,

                Where is that documented? I spent weeks searching information,and found nothing explaining it.

                Where is explained how to show a preview image without writing a single line of code?

                Showing a preview image is basic functionality, and the Sample File Upload and Download app doesn't even attempt to do it.

                 

                Pavel_p wrote:

                 

                It's a complete misunderstanding how transactions work in APEX

                Of course. Check the documentation of the file browse item. What does it says about transactions? Nothing.

                I have absolutely no idea on how how transactions work in APEX.

                 

                All I want to do is to show an image, and I need to be an expert in everything before I learn anything.

                 

                Pavel_p wrote:

                 

                Also to upload the file to APEX_APPLICATION_TEMP_FILES first, then check its size and eventually copy it to the target table and delete it from the temp table is inefficient and does not make any sense.

                I smelled that something was fishy, but as I warned at the start, I barely know anything, so, as I explained, I implemented the code posted in the source, which appears to target to an older version of APEX.

                 

                I didn't even found any documentation about size limits in BLOB data, how the validation should work, or how the file is disposed.

                If this were Visual Studio, I would trust garbage collection and not even care about it.

                 

                 

                Pavel_p wrote:

                 

                If you want to validate the file size, just use the built-in validation (that's why we have them after all). Some validation like this (type PL/SQL Function Returning Error Message) does the trick.

                declare 
                  K_MAX_FILE_LENGTH constant number := 1024;--adjust according to your needs    l_img_blob blob;  begin    select f.blob_content into l_img_blob from apex_application_temp_files f where f.name = :p1_new;    if dbms_lob.getlength(l_img_blob) > K_MAX_FILE_LENGTH then      return 'Uploaded file exceeds the maximum allowed file size. Max file length = ' || K_MAX_FILE_LENGTH || ', uploaded file length = ' || dbms_lob.getlength(l_img_blob);    end if;  end; 

                if the validation fails, the transaction is rolled back (no code at all is needed) and the user gets a nice red error message next to the appropriate item.

                 

                Thank you for telling me. Next thing I need to learn is how to validate data.

                • 5. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
                  InoL

                  You do not realize that for a newbie, Apex looks like a bunch of very different tools, programming languages, and code randomly spread in many different places, very badly linked together in a very amateur and clunky way. And I suspect that for an expert it looks the same.

                  There is not obvious at all, what code is related to what control, or when it runs in the browser or the database, and there are too many different places where the same code could be placed.

                   

                  For example, I lost weeks believing that the File Browse control uploaded the files to APEX_APPLICATION_TEMP_FILES before page submission, because that important aspect is not clearly documented. I wasted a lot of time trying to figure why showing an image preview didn't worked at all.

                   

                  Before you start using Apex, you also have to have knowledge about HTML, CSS and Javascript. A "file browse" element in HTML only selects a file, that's it. The Apex manual doesn't have to document that, it's standard HTML:

                  <input> elements with type="file" let the user choose one or more files from their device storage. Once chosen, the files can be uploaded to a server usinghttps://developer.mozilla.org/en-US/docs/Learn/HTML/Formsform submission, or manipulated using JavaScript code and the File API.

                  See:

                  https://developer.mozilla.org/en-US/docs/Web/HTML/Element/input/file

                   

                  IMHO, if you are by yourself with no knowledge about a new product, and there is nobody around you for help, get some proper training first. That counts for any development environment: Java, .Net, PHP...

                  • 6. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
                    pawuyulan

                    InoL wrote:

                     

                    The Apex manual doesn't have to document that

                    It needs to be documented similarly to an inherited class. It needs to link to the base class documentation.

                     

                    It actually needs to say that it IS a standard HTML file item. It doesn't says that.

                    It just says "Displays a text field with a Browse... button". It cannot be taken for granted that a "text field with a Browse... button" is a standard HTML "file" item.

                     

                    It needs to document what are the differences compared to a standard HTML file item.

                    A standard HTML file item doesn't have all the properties of the APEX "File Browse..." item, and that's not documented.

                     

                    It needs to link to pages explaining the properties, and how they work, specially "Properties>settings>storage type".

                     

                    It needs to provide working examples.

                     

                    And that is the minimum thing it should be doing.

                    • 7. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
                      Pavel_p

                      Ok. I check it. I start running, to see what it does. It takes long to find where the actual "file upload" item is. When I find it, I need to know where his code is. The only way is to hack it with browser inspect tools. I find his name is P12_FILE_BLOB. There is no search tool in APEX to find where the control is, and there are 13 pages to search in. But when I find it, I have no way to know what other code is related to it, or where is that code stored.

                      Yes, it takes some time to get familiar with any tool/framework you work with and APEX is no exception. There are tools and documented APEX views (App Builder => Workspace Utilities => Application Express Views) where you can quite comfortably find what you need.

                      select * from apex_application_page_items where application_id = :app_id and display_as_code = 'NATIVE_FILE'
                      

                      It's not fair to blame the tool that it lacks some features just because you don't know how to use it and if you spend weeks to figure out how something works (or does not work), it also cannot be considered as the tool's fault. Learning curve is usually steep no matter what is the tool/framework of your choice and you cannot expect that you'll get everything served on a gold-plate, so yes, it's easy to lose few weeks to figure out how something works or does not work.

                      I need to make an upload application to upload the image, just to make a "hello world" application.

                      There's a contradiction in your statement. Hello world application is the simplest possible application usually with one line of code while to upload image, validate its size and then show it is a quite complex task in anything I've ever worked with. It just looks like a trivial task in APEX.

                      It says "Add an unconditional page rendering page process". It should be an hyperlink to the explanation of what is an "unconditional page rendering page process".

                      Um... Yes, agreed, this is pretty confusing and this formulation probably remained there from previous versions, however if you open the page in the page designer, I think it's pretty obvious what it means.

                      Sample Database Application shows images inside an "Interactive report" item, which doesn't works like an "Display image" item.

                      No, but you can make a classic report look very similar to the display image item, but I did not mean the displaying part, I meant the file upload.

                      And the rest... Sorry, I don't have enough time to discuss every point in more detail, so just a general note... I would prefer as well to have just one language for both client and server side programming. Since you mentioned MS VS (however I'm not sure why you mentioned garbage collection - it's something completely different), from this point of view was Silverlight (and LightSwitch) great - just VB/C# both client and server side, pure beauty compared to HTML+JS+CSS+bunch_of_other_technologies mess but such is life - browsers do not understand anything else.

                      • 8. Re: How to upload an image (file) into a table (BLOB) in APEX 5 (for dummies)
                        pawuyulan

                        How to upload an image (file) into a table using Automatic Row Processing (DML) (For Dummies)

                         

                        The alternative way to using the table APEX_APPLICATION_TEMP_FILES is to automate the insertion of the image file from the File Browse item directly to the images table.

                        pawuyulan wrote:

                        .... First, we need a table to store the images:

                         

                        We will create a table named "MYIMAGES_TBL"

                        CREATE TABLE  "MYIMAGES_TBL"   
                           ( "ID" NUMBER NOT NULL ENABLE,   
                             "IMAGE_NAME" VARCHAR2(400),   
                             "FILENAME" VARCHAR2(350),   
                             "MIME_TYPE" VARCHAR2(255),   
                             "DOC_SIZE" NUMBER,  
                             "CHARSET" VARCHAR2(128),   
                             "LAST_UPDATE_DATE" DATE,  
                             "CONTENT" BLOB,   
                        CONSTRAINT "MYIMAGES_TBL_PK" PRIMARY KEY ("ID")  
                          USING INDEX  ENABLE  
                           )  
                        /  
                        CREATE OR REPLACE EDITIONABLE TRIGGER  "BI_MYIMAGES_TBL"   
                          before insert on "MYIMAGES_TBL"                 
                          for each row    
                        begin     
                          if :NEW."ID" is null then   
                            select "MYIMAGES_TBL_SEQ".nextval into :NEW."ID" from sys.dual;   
                          end if;   
                        end;   
                        /  
                        ALTER TRIGGER  "BI_MYIMAGES_TBL" ENABLE  
                        /  
                        

                         

                         

                        We create a "File Browse" item named FILE_BROWSER

                        This time, for FILE_BROWSER properties we set:

                        • Properties>Settings>Storage type=BLOB column specified in Item Source attribute

                        A continuation, is necessary to describe the column names of "MYIMAGES_TBL"

                        • Properties>Settings>MIME Type Column=MIME_TYPE
                        • Properties>Settings>Filename Column=FILENAME
                        • Properties>Settings>Character Set Column=CHARSET
                        • Properties>Settings>BLOB Last Updated Column=LAST_UPDATE_DATE

                        And we restrict the file type to images only

                        • Properties>Settings>File Types=image/*
                        • Properties>Validation>Value Required

                         

                         

                        Still it needs to have specified the name of the BLOB column in "MYIMAGES_TBL":

                        • Properties>Source>Type=Database Column
                        • Properties>Source>Database Column=CONTENT

                        The selected file will be uploaded at submission time, so we add a button named "Upload" set to

                        • Properties>Behavior>Action=Submit page
                        • Properties>Behavior>Database Action=SQL INSERT action
                        • Properties>Server-side Condition>Type=Item is NOT NULL
                        • Properties>Server-side Condition>Item=FILE_BROWSER

                         

                        Note that we had not yet specified the name of the table.

                         

                        To insert the file at submission time, we will need two auxiliary items to pass parameters. We create two items of type "hidden" named FILE_ID, and STORED_IMAGE_ID

                         

                         

                         

                        Now, we create a process named Upload_File_To_Table, and set it to

                        • Properties>Identification>Name=Upload_File_To_Table
                        • Properties>Identification>Type=Automatic Row Processing (DML)
                        • Properties>Identification>Settings>Table Name=MYIMAGES_TBL

                         

                        This is the column name of the primary key in MyIMAGES_TBL

                        • Properties>Identification>Primary Key Column=ID

                         

                        This is one of the items we created before

                        • Properties>Identification>Primary Key Item=FILE_ID
                        • Properties>Identification>Return Key Into Item=STORED_IMAGE_ID

                        I guess that FILE_ID is the temporary ID on the APEX_APPLICATION_TEMP_FILES table.

                        I guess that STORED_IMAGE_ID is the new ID in MyIMAGES_TBL, (but it doesn't seems to work).

                         

                        pawuyulan wrote:

                         

                        To display the preview of the image

                         

                        We add a "Display Only" item named "IMAGE", set to:

                        • Properties>Settings>Based On ="Image URL stored in Page Item Value"
                        • Properties>Source>Type=Static Value=""
                        • Properties>Label>Label="Image preview:"

                        The image will not be uploaded to the database until the page is submitted, so, to show a preview, it is necessary to fetch the image from the user computer.To do that, we add a javascript code in "Page Name>Properties>JavaScript>Function and Global Variable Declaration"Untitled.jpg

                        We introduce this code:

                        function readURL(input) {  
                            if (input.files &&  
                                input.files[0] &&  
                                input.files[0].type.match('image.*')) {  
                                var reader = new FileReader();  
                          
                                reader.onload = function (e) {  
                                    $('#IMAGE').attr('src', e.target.result);
                                    document.getElementById("IMAGE").style= "max-width:100%;height:auto";  
                                }  
                          
                                try {  
                                    reader.readAsDataURL(input.files[0]);  
                                }  
                                catch(err) {  
                                    alert("error: " + err.message);  
                                }   
                            }  
                        }  
                          
                        $("#FILE_BROWSER").change(function(){  
                            readURL(this);  
                        });