1 2 Previous Next 23 Replies Latest reply: Sep 22, 2011 10:38 AM by rukbat RSS

    Image column in an interactive report (APEX 4.0)

    456939
      Hi,
      I have added a new blob column containing images to an interactive report, but get the following error:-

      ORA-06502: PL/SQL: numeric or value error: character to number conversion error

      If the "IMAGE" column is removed from the report using the "actions:select column" control, the report is ok.

      The column contains the following settings in the "BLOB Download Format Mask":-

      IMAGE:WINEDETAILS:IMAGE:WINE_ID::MIMETYPE:FILENAME:::inline:Download

      Have I missed something?

      Regards
      Tim
        • 1. Re: Image column in an interactive report (APEX 4.0)
          429923
          When you include the blob, you need to only include something that indicates the length - e.g. dbms_lob.getlength(RESUME), not include the blob column itself.

          -- Sharon
          • 2. Re: Image column in an interactive report (APEX 4.0)
            456939
            Where does this need to be placed, in the SQL for the report? The SQL is:-

            select "WINE_ID",
            "WINE",
            "IMAGE"
            from "#OWNER#"."WINEDETAILS"

            the "IMAGE" columns stores the BLOB. Do I not need to select the BLOB in the query?

            --Tim                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                   
            • 3. Re: Image column in an interactive report (APEX 4.0)
              429923
              select wine_id, wine, dbms_lob.getlength(image) my_image
              from wine_details

              -- Sharon
              • 4. Re: Image column in an interactive report (APEX 4.0)
                456939
                Thanks, changed the sql to:-
                select "WINE_ID",
                "WINE",
                dbms_lob.getlength(IMAGE) IMAGE
                from "#OWNER#"."WINEDETAILS"

                Image now displays
                Thanks
                Tim

                Edited by: Timbo2 on Jul 13, 2010 6:36 PM
                • 5. Re: Image column in an interactive report (APEX 4.0)
                  568298
                  How can you resize the image in this scenario? I've used the above method and get the image to display fine, but it is huge and i need to resize it. Also, i have looked at the sample 4.0 application, but the image in that is stored in the same table as the products. I can't get that to work either because my blob column is in a different table than the non-blob data (i.e. i have a pictures table because multiple tables in the application have a pictureid column which then has a relationship to the pictures table).
                  • 6. Re: Image column in an interactive report (APEX 4.0)
                    Mettemusens2
                    Hi all

                    I'm on Apex 4.0

                    Does anyobe know how to resize the picture in a report?

                    I have tried with height=50 in Element Attributes and the Element Options Attribute - but the image still displays as its normal size (which is too big for my liking).

                    Regards
                    Mette
                    • 7. Re: Image column in an interactive report (APEX 4.0)
                      546178
                      I have a same problem.
                      Anyone know the solution??

                      THNX..
                      • 8. Re: Image column in an interactive report (APEX 4.0)
                        ATD
                        Hi,

                        Have a look at: http://apex.oracle.com/pls/apex/f?p=267:11

                        You can resize the image to fit within the boundaries of the TD tag it is in. In that example, I'm setting the width and height to 50px. If you only set one dimension, the other should be set automatically to retain the correct "aspect ratio"

                        Andy
                        • 9. Re: Image column in an interactive report (APEX 4.0)
                          546178
                          I tried like you described in your example, but still cant see the picture in report column.
                          When i tried to execute procedure display_image manualy, an error is happened:
                          ORA-06502: PL/SQL: numeric or value error
                          ORA-06512: at "SYS.OWA_UTIL", line 354
                          ORA-06512: at "SYS.OWA_UTIL", line 413
                          ORA-06512: at "PROD.DISPLAY_IMAGE", line 15
                          ORA-06512: at line 7

                          I tried to update owa_util from 10.1.2.0.4 (XW database) to 10.1.2.0.6 version - nothing happened.

                          Please help..
                          • 10. Re: Image column in an interactive report (APEX 4.0)
                            ATD
                            Hi

                            What is your procedure code?

                            Andy
                            • 11. Re: Image column in an interactive report (APEX 4.0)
                              546178
                              Procedure code:

                              CREATE OR REPLACE PROCEDURE PROD.DISPLAY_IMAGE
                              (
                              inID NUMBER
                              )
                              AS
                              vMIME VARCHAR2(48);
                              vLENGTH NUMBER;
                              vFILENAME VARCHAR2(2000);
                              vBLOB BLOB;
                              BEGIN
                              SELECT MIME_TYPE, OBJEKT, FILE_NAME, DBMS_LOB.GETLENGTH(OBJEKT)
                              INTO vMIME, vBLOB, vFILENAME, vLENGTH
                              FROM TOB_DOKUMENTI
                              WHERE ID = inID;
                              owa_util.mime_header(nvl(vMIME, 'application/octet'), FALSE);
                              htp.p('Content-length: ' || vLENGTH);
                              owa_util.http_header_close;
                              wpg_docload.download_file(vBLOB);
                              END;
                              /

                              select statement:
                              SELECT
                              V.ID, V.PREZIME, V.IME,
                              V.DATUM_RODJENJA, V.JMBG, V.OIB,
                              V.BROJ_OSOBNE, V.BROJ_PUTOVNICE, V.BROJ_ZDR_ISKAZNICE,
                              V.NAPOMENA, V.OPIS, V.STATUS,
                              V.ID_VRSTE, V.NAZIV_VRSTE, V.SLIKA,
                              V.ID_KUPCA,
                              dbms_lob.getlength(v.slika) slika1,
                              '<img src="#OWNER#.DISPLAY_IMAGE?inID=' || NVL(v.id_doc, 0) || '" height="50" width="50" />' IMAGE
                              FROM VOB_CLANOVI_KUCANSTVA V
                              where v.status = 'D'
                              and v.id_kupca = :F130_ID_KUP
                              • 12. Re: Image column in an interactive report (APEX 4.0)
                                ATD
                                Hi,

                                The procedure looks correct to me. It matches my version apart from table and field names.

                                Firstly, did you run: GRANT EXECUTE ON DISPLAY_IMAGE TO PUBLIC

                                Secondly, is "OBJEKT" a reserved word in your language version of Oracle?

                                Lastly, is your table defined in the same way as mine:
                                CREATE TABLE "A_IMAGES"
                                (
                                  "IMAGE_ID" NUMBER(10,0) NOT NULL ENABLE,
                                  "FILE_NAME" VARCHAR2(4000) NOT NULL ENABLE,
                                  "BLOB_CONTENT" BLOB,
                                  "MIME_TYPE" VARCHAR2(4000),
                                  CONSTRAINT "A_IMAGES_PK" PRIMARY KEY ("IMAGE_ID") ENABLE
                                )
                                (apart from the table and field names)

                                Andy
                                • 13. Re: Image column in an interactive report (APEX 4.0)
                                  546178
                                  Firstly, did you run: GRANT EXECUTE ON DISPLAY_IMAGE TO PUBLIC
                                  Yes..

                                  Secondly, is "OBJEKT" a reserved word in your language version of Oracle?
                                  No, objekt is not reserved word.

                                  Lastly, is your table defined in the same way as mine:

                                  CREATE TABLE "A_IMAGES"
                                  (
                                  "IMAGE_ID" NUMBER(10,0) NOT NULL ENABLE,
                                  "FILE_NAME" VARCHAR2(4000) NOT NULL ENABLE,
                                  "BLOB_CONTENT" BLOB,
                                  "MIME_TYPE" VARCHAR2(4000),
                                  CONSTRAINT "A_IMAGES_PK" PRIMARY KEY ("IMAGE_ID") ENABLE
                                  )

                                  Script of my table:
                                  CREATE TABLE PROD.TOB_DOKUMENTI
                                  (
                                  ID NUMBER(10) NOT NULL,
                                  PARENT_ID NUMBER(3) NOT NULL,
                                  ID_VALUE NUMBER(10),
                                  NAZIV_DOKUMENTA VARCHAR2(500 BYTE),
                                  VELICINA NUMBER,
                                  OBJEKT BLOB,
                                  LINK VARCHAR2(1000 BYTE),
                                  DATUM_OD DATE NOT NULL,
                                  DATUM_DO DATE,
                                  STATUS VARCHAR2(1 BYTE) DEFAULT 'D' NOT NULL,
                                  ID_KUPCA NUMBER(10) NOT NULL,
                                  ID_VRSTE NUMBER(6) NOT NULL,
                                  ID_DOC_ORIGINAL NUMBER,
                                  FILE_NAME VARCHAR2(4000 BYTE),
                                  MIME_TYPE VARCHAR2(100 BYTE)
                                  )..

                                  I also think that everything is set up correctly, but it still not working..

                                  THNX for Your reply..

                                  Kreso..

                                  (apart from the table and field names)

                                  Andy
                                  • 14. Re: Image column in an interactive report (APEX 4.0)
                                    ATD
                                    Hi,

                                    OK - one possibility, perhaps, is that you have used VARCHAR2(nn BYTE) to define some fields. This may or may not allow for nn number or characters - non-English characters that include accents such as veličina may take up more space.

                                    If you can change the fields to VARCHAR2(nn) - which should be the same as VARCHAR2(nn CHAR) - that may help.

                                    Otherwise, I could recreate your procedure etc on my OTN workspace

                                    Andy
                                    1 2 Previous Next