1 2 Previous Next 22 Replies Latest reply on Feb 7, 2012 12:32 AM by TexasApexDeveloper

    Confusion with get_blob_file_src

      Simple requirement: I have photos stored in a BLOB column in a table and I want them displayed
      thumbnail size in an interactive-report page.
      Having read round this area, it seems that I am duty bound to call apex_util.get_blob_file_src from
      the report query's SELECT statement (so that I can force the image size to be 75 by 75).
      Here's where the problems start.

      Having read the advice, it seems that my select query should have a column along the lines
      of: '<img src="'||APEX_UTIL.GET_BLOB_FILE_SRC('P2_ATTACHMENT',:P2_EMPNO)||'" />'

      (I copied this from the documentation).
      All looks fine: it's the return of the package APEX_UTIL's function called "GET_BLOB_FILE_SRC" wrapped
      inside bookends of "<img src=" and "/>" essentially.

      So this gets me thinking... the GET_BLOB_FILE_SRC looks like it takes two parameters. The
      first parameter is obviously an item name, and the second is an item value.
      But when I edit my interactive report page there is a single region for the interactive report and
      there are no explicit items in the "Items" section underneath that.

      Back to the API manual: I'm reading the description of the first parameter of the GET_BLOB_FILE_SRC
      function and it says "Name of valid application page ITEM that with type FILE that contains the source type of DB column."

      Hmmm... "Name of valid application page ITEM" ... I don't have one, so maybe I should create one for
      use here. What to call it for this page three... how about "P3_IMAGE_HIDDEN" (because I'm thinking that its acting as
      some sort of Placeholder box but although I want to use it I don't want to see it on its own).

      Next bit of manual: "with type FILE"... does that mean this item's "DISPLAY AS" attribute has to be
      set to "FILE"? Lets see... there's only a "File Browse..." entry that looks similar so I'm forced to choose
      I wonder if the option "FILE" is available through any other attributes... can't see any so let's persevere.

      Then I read "... that contains the source type of DB column".
      Gosh: what on earth does that mean? Does it mean that the contents of this Item is somehow "sourced" against
      the specific blob column in the database?
      So I set the "Source Type" to "Database Column" and then fill in the Source box with the name of the blob column
      from the table.
      (incidental aside: I find it amusing when I read some bits of the manual which explain that some words are case
      sensitive when it might be more useful to state that upper case is the only one that will work! Look at the help
      for "Source Type" and at the end of the "Database Column" entry it says "The database column name in that field is case sensitive."
      The entry is not wrong, it's just that it could be more helpful methinks!)

      Quick recap: The call to function GET_BLOB_FILE_SRC seems to take two parameters: the name of a legitimate Item
      that is linked to the database table column, and the primary key value of one row from the table in question.
      How this system works out which table holds the important blob column is a mystery to me.

      When I set the report field to "Display as text" I get to see the end product of the query: a field that looks like
      the following copy-and-paste:
      <img src="apex_util.get_blob_file?a=900&s=3134319599265367&p=3&d=&i=1721302456803667&p_pk1=115&p_pk2=&p_ck=B7851B908E84506147F19B155D36C5F3" height="75" width="75" />

      But when I change the report field to "Standard Database Column", all I see is a broken link.
      Blast (and similar expletives).
      I've had a quick look at the copy-and-pasted thing above and I can make guesses at the parameters such
      as "a=900" (I've numbered this application 900), "p=3" it's page three, "p_pk1=115" looks like the first
      primary key value etc. But what's the "d=" for ? Is that the source of my troubles?

      Time to re-throw myself on the mercy of the group. Help requested please; thanks in advance.

      Mungo Henning
        • 1. Re: Confusion with get_blob_file_src

          Have you read through this bit of documentation ?


          • 2. Re: Confusion with get_blob_file_src
            Hi Varad,
            Yup: I've browsed my way through it (absorbing what I can). Methinks the paragraph at the foot
            of that page is the only bit of interest: where it talks about "Working With BLOBs Procedurally".

            By the very nature of your post I'm getting the feeling that I've missed something, so I'm being
            cautious in this reply...


            Mungo :-)
            • 3. Re: Confusion with get_blob_file_src

              If you did read the part about displaying blobs within an interactive I suppose you had done your homework before posting here :)
              I merely wished to point your attention to the fact that blobs could be displayed in a interactive report without having to the use the 'apex_util.get_blob_file' API. Whether this approach will suffice for your needs I do not know at this juncture.

              • 4. Re: Confusion with get_blob_file_src
                Hi Varad,
                Whilst ultimately I would admit my human fallibility, I have devoted some hours to resolving this
                puzzle and the page that you kindly mentioned does not help me in any way.

                I appreciate your response, but I'm getting nowhere fast.

                In the vain hope that someone can help, can I add further information to this problem:

                1. In an interactive report, how is it sensible for the SQL select statement to return a value
                from "dbms_lob.getlength()" and somehow apex shows a picture?
                I just don't get it: that function returns an integer yet there is some underlying magic that suddenly
                produces an image when I change the format mask to "IMAGE:..." .
                If the APEX system can magic up a picture from a single integer then I'm worried what else it can do,
                and that makes me concerned that the creators might throw further illogical surprises at me later on
                in my struggles with apex, hence it might be more productive for me to abandon all apex development
                to avoid future grief.

                2. Even with this incredulous capability of showing a picture, my photos are quite large yet I want a 75 by 75
                thumbnail to appear in the report. The only way that I've read from the manual is to go down the
                apex_util.get_blob_file_src route (not "apex_util.get_blob_file" - these are two different beasts).
                First of all, just what on earth is this function's name meant to imply: "Get Blob File Source" ?
                The "Get" links up with its return value of varchar2; the "Blob" is obvious; the "File Source" means what exactly?
                I wouldn't want to admit this publically, but having struggled with the name I'm starting to think that the problems
                are not mine but lie with the creator of this function. Ultimately, the manual entry is inadequate. And that's me
                being polite too.
                Since this function returns a varchar2, it cannot return the blob value. Instead it must do a bit of assigning the blob's
                bytes into the receptacle whose name is given as parameter one, and then return a URL reference to it somehow.

                3. Methinks my problem is that the get_blob_file_src is returning a reference string and in that string the parameter "d"
                is not being set. Seemingly the "d=" bit is something to do with a "DML process ID"
                (see Scott Spendolini's reply in thread Re: Inconsistent Links w/ GET_BLOB_FILE_SRC )
                What on earth an interactive report needs with "DML" is beyond me - it's only a report, it only needs to do Select, there's no
                requirement for Insert, Update, Delete or Merge.

                4. I stumbled upon the thread Issue with BLOB support
                where Dan says "I had to have a page process DML_PROCESS_ROW that had the table name."
                Where in the documentation does it tell me this is a requirement? And is there any advice anywhere on how to create
                such a process - can I just add a process and take the defaults for its value and hope for the best?

                Overall I find it totally frustrating when my simple desire to show a picture as a thumbnail on a page cannot be
                easily achieved.
                In the Advanced Programming Techniques section you mentioned, the bit I am interested in starts with:

                "About BLOB Support in Reports

                Oracle Application Express includes BLOB support for both classic and interactive reports. If you use a wizard to create a report and include a column of type BLOB, basic support will be included. Additional information should be added after generation to make the download capability more user friendly."

                I guess the caveat is that these words live under the "ADVANCED" banner, hence my conclusion that the "basic support" mentioned above
                really means "absolutely of no effect whatsoever" might seem churlish. The real salt in the wound is "Additional information should be
                added" without elaborating on how to achieve this unambiguously... a big thankyou to whomever wrote those words (not).

                And all for the sake of a bunch of little thumbnails... if this is so difficult to create what will it be like when times get tougher?

                Yours in despair borne of frustration

                • 5. Re: Confusion with get_blob_file_src
                  Lutz Hartmann as sysdba
                  there is no point in iomposing on new users here.
                  You have not responded to any point of Mungo's very much in detail questions.
                  All you do is tell him is RTFM.
                  This is not helpful at all.
                  It would be much more helpful to explain where in Mungo's post the error is in the code.
                  Can you do this?

                  I have a similar problem by the way and have read through a couple of posts and had to read all these nasty unhelpful replies while I was hoping for a fast helping hand.

                  Think it over man!

                  • 6. Re: Confusion with get_blob_file_src
                    Very interesting interpretation, Lutz. I hadn't read anything particularly nasty in Varad's responses at all, but these differences of interpretation are what makes life interesting.

                    By the way, I'm also struggling with working with blobs. It seems like it should be trivial but it's not. I appreciate hearing about any documentation that might shed some light on this.

                    • 7. Re: Confusion with get_blob_file_src
                      No need for anybody to get nasty here. I think I know what needs to get the images/blob download working. I'll post my findings soon.

                      • 8. Re: Confusion with get_blob_file_src
                        Hi Lutz,
                        Hoping to get this Reply in before Varad reads the thread, I have no gripe against anyone that
                        volunteers their time to help others that are struggling. I just tried to write up my woes with
                        sufficient emotion (i.e. exasperation with the inadequate documentation) in the hope that it would make things better for everyone.

                        Having spent about a day and a half struggling with these Blobs, I've decided to give it a rest for
                        24 hours to see if a fresh approach will bear fruit. Radio silence then for the next wee while from

                        Whilst here, can I also add that I find it frustrating to read (admittedly rare) threads on this forum where a
                        legitimate problem is posed and either the person answering it doesn't give sufficiently clear
                        instructions/suggestions that solves the problem, or the original poster finishes off the thread
                        along the lines of "never mind, solved it myself, thanks for the replies" without taking the time and
                        trouble to elaborate on how they solved it!
                        I mean, if you take the time to fire off a Reply then surely it is worthwhile to state sufficient detail to
                        make the whole reply worthwhile.

                        You can still detect the faintest trace of despair here... off to watch some television and grab a beer!
                        Catch you Thursday (please?)


                        Mungo :-)
                        • 9. Re: Confusion with get_blob_file_src

                          OK. This is my understanding of how the 'get_blob_file_src ' API works. This API is a wrapper over the procedure 'get_blob_file'. The API returns a mod/plsql URL that calls out to 'apex_util.get_blob_file'.
                          So in order to call 'get_blob_file' we need values for the following parameters (see end of post for a desc of get_blob_file)
                          s Apex Session Id
                          a Apex Application Id
                          p APEX page id
                          d Process-id of the ARP page process in page p
                          p_pk1 Primary key value
                          p_ck1 Checksum

                          However, get_blob_file_src is called in the report query with just 2 parameters. These are
                          p_item_name - Name of page item of type FILE and source type of 'Database Column' and the column name being the name of the column that has the BLOB data
                          p_v1 - Value of the primary key column of the table that has the BLOB column

                          Now, how are the values for the parameters required by 'get_blob_file' obtained by get_blob_file_src ?

                          s -> is available from session state
                          a -> is available from session state
                          p -> is obtained by looking up the APEX meta data for the page-id of the page-item specified as the first parameter to get_blob_file_src
                          d -> This is the interesting one. A hack at best to obtain the table_name and PK column of the table containing the BLOB  from APEX meta data!
                          On the page specified in 'p' there must exist a ARP DML process with the following specs.
                          owner -> owner of table from where the blob column is to be retrieved
                          table_name -> name of the table that has the blob column to be retrieved
                          Item Containing PK -> Specify any string
                          Primary Key Column -> PK column of the table that has the blob column to be retrieved
                          Process Point -> After Submit..
                          p_pk1 -> is the value specified as the 2nd parameter to get_blob_file_src
                          p_ck1 -> presumably generated

                          Given the above and a report against the DEMO_PRODUCT_INFO table with a query like
                          select product_id, '<img HEIGHT="25" WIDTH="25" src="'||apex_util.get_blob_file_src('P96_X',PRODUCT_ID)||'" />' img
                          what I had to do make the images render was to

                          Create a page with an IR report region with the above query
                          Create another page where I defined a 'file-browse' type page item named P96_X. Set the Source Type to 'Database Column' and column name to PRODUCT_IMAGE
                          Created a ARP DML page process on this page with the following specifications
                          Owner => DEMO
                          Table Name => DEMO_PRODUCT_INFO
                          Page Item for PK => P1_ID
                          Primary Key Column => PRODUCT_ID
                          Process Point - On Submit..

                          Doc for get_blob_file extracted from package spec (APEX_UTIL)
                          procedure get_blob_file(
                              -- Automatically called from APEX form pages
                              -- Not designed to be called proceduarlly
                              -- Calls to this procedure can be generated by calling the apex_util.get_blob_file_src function
                              -- Page must have item of type FILE (FILE Browse)
                              --    DB_COLUMN           = Required case sensitive name of a valid column which is of type BLOB
                              --    MIMETYPE_COLUMN     = Optional case sensitive column name of a table column used to store the mimetype
                              --    FILENAME_COLUMN     = Optional case sensitive column name of a table column used to store the file name
                              --    LAST_UPDATE_COLUMN  = Optional case sensitive column name of a table column used to store the last update date of the BLOB
                              --    CHARSET_COLUMN      = Optional case sensitive column name of a table column used to store the file character set
                              --    CONTENT_DISPOSITION = inline or attachment
                              --    DOWNLOAD_LINK       = Optional text to be used for the download text, defaults to Download, translated
                              -- Page item source must include at least the database column name and a trailing colon
                              -- Mimetype column is required if the mimetype is to be encoded in the download header
                              -- Page item must be of source type of DATABASE COLUMN
                              -- Page must have a DML process of type DML_PROCESS_ROW, used to determine the tablename
                              -- Must be called from an APEX application context
                              -- Invalid inputs will result in a 404 error
                              s                     in number,                -- APEX session ID
                              a                     in number,                -- APEX application ID
                              p                     in number,                -- APEX page ID of the form page
                              d                     in number,                -- DML process APEX meta data ID
                              i                     in number,                -- ITEM of type FILE APEX meta data ID
                              p_pk1                 in varchar2,              -- Primary key value
                              p_ck                  in varchar2,              -- Checksum used to prevent URL Tampering
                              p_pk2                 in varchar2 default null, -- Optional Second Primary Key Value, used for compound keys
                              p_mimetype            in varchar2 default null, -- Optional ...
                              p_content_disposition in varchar2 default null, -- Optional use "inline" or "attachment" all other values ignored
                              p_show_last_mod       in varchar2 default 'Y'); -- Optional ...

                          Edited by: varad acharya on Oct 27, 2009 2:44 PM
                          • 10. Re: Confusion with get_blob_file_src
                            Whaow Varad, I never thought of doing a "desc" on the package (I'm guessing that's what you did?)
                            It's so tempting to give this a spin just now (10:10pm in a dark and rain-sodden town in Scotland :-) ) but I'll
                            resist the temptation until tomorrow sometime.
                            Many many thanks for the comprehesive reply. No guarantees that I will get this to work, but it gives me
                            Just to make this reply a bit more worthwhile, what's an "ARP" - Automatic R??? Process ? (did I tell you
                            I'm an Apex novice?? ;-) )
                            Are these processes similar to the "do the right thing" procedures and functions within Oracle Forms?

                            Sincerely, many thanks for your sterling efforts.


                            • 11. Re: Confusion with get_blob_file_src

                              I don't believe the 'desc' command will show the package source. I looked up the source for the APEX_UTIL package from Toad.

                              Sorry for the acronyms, ARP stands for Automatic Row Processing. This is a type of page process that is generally seen in APEX wizard built forms. You can however add one to your page by clicking on the '+' icon in the 'processes' section and then selecting 'Data Manipulation' for process type and the following page lets you further select from a list of 4 processes that do different things. The one relevant to this post is the 'Automatic Row Processing (DML)' type. Choose this type and provide the information requested in the next few pages to complete the definition of the page process.

                              APEX page processes are and behave like pl/sql anonymous blocks.

                              • 12. Re: Confusion with get_blob_file_src
                                Lutz Hartmann as sysdba
                                Hi Varad,
                                very interesting approach.
                                I will check it.

                                By the way: DESC never shows the source code of a package.
                                It just shos the interface of the package.

                                The source can be found in the TEXT column of the view DBA_SOURCE normally, but not for APEX_UTIL obviousely.
                                APEX_UTIL is just a public synonym for the synonym HTMLDB_UTIL, owned by PUBLIC which stands for the package
                                HTMLDB_UTIL owned by APEX_030200 .
                                For this you can find the source in DBA_SOURCE:
                                select text from dba_source where name='HTMLDB_UTIL' and owner='APEX_030200';

                                .. but of course it is wrapped (encrypted) => Oracle does not want you to look into their cards!

                                • 13. Re: Confusion with get_blob_file_src
                                  Hi Varad,
                                  True to my timings, I've just implemented your changes and it seems to work fine.
                                  Thankyou for your kind help.

                                  In your reply you wrote:
                                  Create a page with an IR report region with the above query
                                  Create another page where I defined a 'file-browse' type page item named P96_X.
                                  Set the Source Type to 'Database Column' and column name to PRODUCT_IMAGE
                                  Okay, so for some page N where the interactive report has to reside I create a query that mentions the get_blob_file_src call as specified
                                  elsewhere in your words.
                                  I'm also going to create a totally separate page (for me: N+1) and create a region in there and then this page item you mentioned above.
                                  I set this new (single) item's source to be the capitalised name of the BLOB column in the table that I'm trying to show.

                                  Created a ARP DML page process on this page with the following specifications
                                  Owner => DEMO
                                  I don't remember an "owner" attribute, just a "name" one.
                                  Table Name => DEMO_PRODUCT_INFO
                                  Page Item for PK => P1_ID
                                  Primary Key Column => PRODUCT_ID
                                  Process Point - On Submit..
                                  The "Table Name" is obviously the capitalised name of the table that holds my blob photograph. The "Page Item" I've set to the
                                  name of page N's primary key item. This use of "P1_ID" is a little confusing. The popup menu thinggy for this field only suggests
                                  the lone page item in this page - just ignore this and refer back to the interactive-report's page's item as described.

                                  The "Primary Key Column" is just the capitalised name of the primary key in the table that holds the blob photograph.

                                  Lastly, you said to set the "Process Point" to "On Submit.." - there are two possibilities here with the same prefix: an After and a Before
                                  if memory serves me right. I just chose the first.

                                  All in all this seems to work for me now; I'm still complaining that I had to seek your help in order to get this working: the documentation
                                  for this and other areas of Oracle is shambolic at times.

                                  Best regards Varad; until my next crisis...

                                  Mungo :-)
                                  • 14. Re: Confusion with get_blob_file_src
                                    I don't remember an "owner" attribute, just a "name" one.
                                    This label for this field is 'Table Owner' and it appears as a Select List on the page where you are asked to provide the table-name, PK column etc.
                                    This use of "P1_ID" is a little confusing
                                    I set it to to P1_ID which is a page-item in my application. I think this can just about be set to anything and APEX doesnt complain
                                    Lastly, you said to set the "Process Point" to "On Submit.." - there are two possibilities here with the same prefix: an After and a Before
                                    I chose - On Submit - After Computations and Validations. Either choice seems to work.

                                    1 2 Previous Next