4 Replies Latest reply: Mar 11, 2014 12:55 PM by Haisa.M RSS

    Insert Blob

    Haisa.M

      Hello All;

       

      I have a insert procedure ( I am not using automatic processing DML ) and it works fine but the blob content after insert does not show any link to the attachment.  Here is my code:

       

      declare

       

      REVISE  number;

      REVISION_ID number;

      L_FILE_BLOB BLOB;

      L_MIME_TYPE varchar2(255);

      L_FILE_NAME varchar2(255);

      begin

      select COALESCE (MAX(CUSTOM_REVISED_ID), 0) + 1 INTO REVISION_ID from RTA.RTA_CUSTOM_REPORT_REVISION;

      IF (:P23_REPORT_LAYOUT IS NOT NULL) THEN

      select  MIME_TYPE, BLOB_CONTENT, FILENAME INTO L_MIME_TYPE, L_FILE_BLOB, L_FILE_NAME  FROM apex_application_files WHERE NAME=:P23_REPORT_LAYOUT;

      INSERT INTO RTA.RTA_CUSTOM_REPORT_REVISION

      (CUSTOM_REVISED_ID, CUSTOM_REPORT_ID, REVISE_NUM, REQUESTER_NAME, REQUESTER_TITLE, REPORT_PURPOSE, CREATION_TYPE, CONDITIONS,

      INPUT_PARAMETERS,

      USER_DEPARTMENT, FREQUENCY, REPORT_GENERATED, REPORT_LAYOUT, FILE_NAME, MIME_TYPE, BLOB_UPDATE_DATE, VALIDATION_PROCEDURE,

      APP_OWNER_NAME, CREATED_BY,

      CREATION_DATE, LAST_UPDATED_BY, LAST_UPDATE_DATE, INACTIVE_DATE, PRODUCTION_DATE)

      VALUES (REVISION_ID, :P23_CUSTOM_REPORT_ID, '0' , :P23_REQUESTER_NAME, :P23_REQUESTER_TITLE, :P23_REPORT_PURPOSE, :P23_CREATION_TYPE,

      :P23_CONDITIONS,

      :P23_INPUT_PARAMETERS, :P23_USER_DEPARTMENT, :P23_FREQUENCY, :P23_REPORT_GENERATED, L_FILE_BLOB, L_FILE_NAME, L_MIME_TYPE,

      SYSDATE, :P23_VALIDATION_PROCEDURE,

      :P23_APP_OWNER_NAME, :APP_USER , SYSDATE, :APP_USER , SYSDATE , :P23_INACTIVE_DATE, :P23_PRODUCTION_DATE);

      DELETE FROM apex_application_files

      WHERE NAME=:P23_REPORT_LAYOUT;

      end if;

      commit;

      end;

       

      Please advise me on this. What I am doing wrong!!

       

      Thanks,

        • 1. Re: Insert Blob
          Mike Kutz

          but the blob content after insert does not show any link to the attachment

          HUH??!???

          Are you talking about showing a link in a Report?

           

          The value used for the 'link' column in the report should be the size of the BLOB.  (hint:  DBMS_LOB.GETLENGTH( blob_column) AS link  )

          The 'link' column then needs to be "formatted" -- specifically, you need to set the format to: BLOB

          When you do that, a section dedicated for 'filling in the blanks' will appear.  (APEX uses information in that section to create the URL link)

          You'll need to fill out "table name", "pk column name", "file name" and "blob column name"

          (you may have to create a synonym in your parsing schema that points to that table since you can't include "schema name" in that section.)

          (choose "attachment" if you want a link.  use "embedded" if you are trying to display a thumbnail images in the report.)

          From there, APEX will magically create a link that will work.

           

          Oracle provides documentation on how to handle LOBs in APEX.

           

          MK

           

          PS - this line gives me the shivers..

          select COALESCE (MAX(CUSTOM_REVISED_ID), 0) + 1 INTO REVISION_ID from RTA.RTA_CUSTOM_REPORT_REVISION;

          It is logically flawed because you assume only one person will be using this application at any given time.

          • 2. Re: Insert Blob
            Haisa.M

            Thanks for the reply. I did all of the steps you've mentioned in your comment

            but  I get error in the report page of:

             

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

             

            Please let me know what I am doing wrong!!

            • 3. Re: Insert Blob
              Mike Kutz

              Error codes usually come with a line number and some other text that helps determine the exact location where the error occurred.

              I don't know the location of the error, so I can't help you.

              It will also help if you should show the relevant information of the DEBUG page. (ie run it in "DEBUG")

               

              Also, feel free to place a few APEX_DEBUG.MESSAGE() lines within your process to assist you in finding the precise error.

              (eg show a debug message of what values you are going to use before you use them.)

               

              As always, if problems become too complicated to address in the forums, try to reproduce the problem on apex.oracle.com using non-confidential information.

              (don't forget to create a 'developer' account so you don't have to post up your email address for the spam-bots to collect)

               

              MK

              • 4. Re: Insert Blob
                Haisa.M

                I don't think the error is from the code because as soon as I changed the blob column attribute in report page it appears and when I remove that it will go away. When I change the format to blob and fill out the blob column attribute, it will give me this error and it does not have any line number associated with.  The error is just:

                 

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

                 

                nothing else.