12 Replies Latest reply on Nov 24, 2014 9:37 PM by partlycloudy

    File upload error: ORA-20895: BLOB set request failed application

    partlycloudy

      APEX 4.2.2

      ORDS 2.0.10 deployed in Tomcat 8.0.15

       

      Submitting a page with a File Browse item throws a HTTP 500 Internal Server Error with the following exception

       

      Caused by: java.sql.SQLException: ORA-20895: BLOB set request failed application: "161", session: "14438471746696", page: "11" ORA-06512: at "APEX_040200.HTMLDB_UTIL", line 2790 ORA-06512: at line 1

       

      This page works fine when processed by OHS/mod_plsql

       

      Added a  <entry key="apex.docTable">FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$</entry> to defaults.xml but that didn't help.

       

      Help? Thanks

        • 1. Re: File upload error: ORA-20895: BLOB set request failed application
          Kris Rice-Oracle

          It looks like APEX is having an issue.  You may want to ask over on that forum or check the settings around files uploads.

           

          Caused by: java.sql.SQLException: ORA-20895: BLOB set request failed application: "161", session: "14438471746696", page: "11" ORA-06512: at "APEX_040200.HTMLDB_UTIL", line 2790 ORA-06512: at line 1

           

          That's from apex.

           

          The way it works is if you are in a db where apex 4+ is installed we use the htmldb_util ( apex_util ) .set_blob plsql call over using the doc table settings. It’s using this call to perform file uploads:

           

                 begin ? := apex_util.set_blob(p_file_name=>?,p_mime_type=>?,p_blob_content=>?,p_item_id=>?,p_application_id=>?, p_page_id=>?, p_session_id=>?, p_request=>? ); end;

           

           

           

           

          -kris

          • 2. Re: File upload error: ORA-20895: BLOB set request failed application
            partlycloudy

            Kris - Interesting. I have never heard about the APEX_UTIL.SET_BLOB procedure. It appears to used exclusively by the APEX listener and the API is not documented so I am not sure why it is complaining. As I said, the exact same page when used with OHS/mod_plsql, the upload into the APEX doc table works fine.

             

            OK I will ask on the APEX forum. Thanks.

            • 3. Re: File upload error: ORA-20895: BLOB set request failed application
              Kris Rice-Oracle

              It's something we added to support apex having the ability to load a blob into any table vs the common shared for everyone doctable.  If you have a File Item on a page, you can pick a different table per File Item for storing, it opens up a lot of options. 

               

              With the caveat I'm not on the apex team but have access to source code. Are you trying to upload a file on an anonymous page?

              • 4. Re: File upload error: ORA-20895: BLOB set request failed application
                partlycloudy

                Well, the File Browse page item attributes page still has just 2 choices for the Storage type: Table WWV_FLOW_FILES and BLOB column specified in item source, no different than when we are using OHS/mod_plsql. So I am not sure I understand your comment about picking a different table. If you are referring to the ORDS defaults.xml key apex.docTable, I already set it to FLOWS_FILES.WWV_FLOW_FILE_OBJECTS$ (aka  WWV_FLOW_FILES) but that didn't help.

                 

                No, the page I am trying to upload on is a authenticated page and APP_USER is set to the authenticated user. Yes, I see where you are going, when I try the same page with OHS/mod_plsql on a unauthenticated DAD, I get the error "This instance does not allow unauthenticated users to upload files".

                 

                Any other ideas?

                • 5. Re: File upload error: ORA-20895: BLOB set request failed application
                  Kris Rice-Oracle

                  I was just looking at the source code and the exception you are seeing is on an if block where this is the check

                   

                      if not wwv_flow_security.g_authenticated or wwv_flow.g_unrecoverable_error then



                  -kris


                  1 person found this helpful
                  • 6. Re: File upload error: ORA-20895: BLOB set request failed application
                    partlycloudy

                    Kris - Thanks, that is helpful. I am not sure why either of the conditions in that IF statement would be true. I ran the page in debug mode (f?p=...:YES:...) and see the following

                     

                    ...

                    11/24/2014 1:59:05.634476 PM -05:00    Show page tempate footer    11

                    11/24/2014 1:59:05.634503 PM -05:00    Rendering form close tag and page checksum    11

                    11/24/2014 1:59:05.636713 PM -05:00    Process point: AFTER_FOOTER    11

                    11/24/2014 1:59:05.636752 PM -05:00    Processes - point: AFTER_FOOTER    11

                    11/24/2014 1:59:05.637485 PM -05:00    gv$sesstat.statistic# = 436: execute count=4463    11

                    11/24/2014 1:59:05.637507 PM -05:00    Final commit    11

                    11/24/2014 2:02:21.546827 PM -05:00    DEPRECATED: wwv_flow_custom_auth_std.logout    11

                    ...

                     

                    So it looks like the first step in ACCEPT processing is the wwv_flow_custom_auth_std.logout?! This API call does *not* appear anywhere in the application code.  A logout would certainly cause *wwv_flow_security.g_authenticated* to be set to FALSE and trigger the exception you mentioned.


                    Any idea why the APEX engine would logout before starting accept processing?


                    Thanks

                    • 7. Re: File upload error: ORA-20895: BLOB set request failed application
                      Kris Rice-Oracle

                      I do not.  Colm asked Joel to peek in this thread maybe he'll think of something.

                      1 person found this helpful
                      • 8. Re: File upload error: ORA-20895: BLOB set request failed application
                        partlycloudy

                        The only other thing I can think of is the following snippet from the Tomcat conf/web.xml

                         

                        <filter-mapping>

                            <filter-name>SpnegoHttpFilter</filter-name>

                            <url-pattern>/f</url-pattern>

                        </filter-mapping>

                         

                        This instructs Tomcat to pass all APEX requests /f?p=... URIs via the SPNEGO filter for authentication. But APEX handles page submission with a different URL (wwv_flow.accept) so somehow Tomcat and APEX recognize this and are internally calling (the deprecated!) wwv_flow_custom_auth_std.logout before handling the form submission.

                         

                        I tried adding another filter-mapping

                         

                        <filter-mapping>

                            <filter-name>SpnegoHttpFilter</filter-name>

                            <url-pattern>wwv_flow*</url-pattern>

                        </filter-mapping>

                         

                        but Tomcat doesn't like that, doesn't start up. catalina.out shows Caused by: java.lang.IllegalArgumentException: Invalid <url-pattern> wwv_flow* in filter mapping

                         

                        I even tried wwv_flow.accept and wwv_flow%46.accept but get the same error.

                        • 9. Re: File upload error: ORA-20895: BLOB set request failed application
                          partlycloudy

                          Wow, I thought it was a long shot but that was indeed the issue. Adding

                           

                          <filter-mapping>

                              <filter-name>SpnegoHttpFilter</filter-name>

                              <url-pattern>*.accept</url-pattern>

                          </filter-mapping>

                           

                          to $CATALINA_HOME/conf/web.xml

                           

                          fixed the issue! The debug log now shows

                           

                          11/24/2014 2:52:08.959513 PM -05:00    Show page tempate footer

                          11/24/2014 2:52:08.959541 PM -05:00    Rendering form close tag and page checksum

                          11/24/2014 2:52:08.962216 PM -05:00    Process point: AFTER_FOOTER

                          11/24/2014 2:52:08.962256 PM -05:00    Processes - point: AFTER_FOOTER

                          11/24/2014 2:52:08.963011 PM -05:00    gv$sesstat.statistic# = 436: execute count=4511

                          11/24/2014 2:52:08.963033 PM -05:00    Final commit

                          11/24/2014 2:52:18.513638 PM -05:00    A C C E P T: Request="PREVIEW"

                          11/24/2014 2:52:18.513737 PM -05:00    Reset NLS settings

                          11/24/2014 2:52:18.513987 PM -05:00    alter session set NLS_LANGUAGE="AMERICAN"

                           

                          Looks like the servlet specification for the url-pattern parameter is very specific, I don't usually think of the .accept in wwv_flow.accept as a file extension like *.jsp or *.html but I guess that's how it's interpreted in this context.

                           

                          Kris - Thanks for pointing me in the right direction.

                          • 10. Re: File upload error: ORA-20895: BLOB set request failed application
                            partlycloudy

                            I would be curious to know exactly who/what resulted in the call to wwv_flow_custom_auth_std.logout when Tomcat determined that the URL used for form submission was NOT passing the condition for the SpnegoHttpFilter filter. I would have thought that Tomcat will simply allow the URL to go through unfiltered (since my filter specified /f and NOT /*). I guess that's a question for Tomcat experts.

                            • 11. Re: File upload error: ORA-20895: BLOB set request failed application
                              Kris Rice-Oracle

                              Yeah it's very specific for sure.  I assume the Spnego stuff works well for you ?  I need to blog/write up how to do AD support with ORDS as it's a common questions or do you have a blog I can point to

                              • 12. Re: File upload error: ORA-20895: BLOB set request failed application
                                partlycloudy

                                Yes, so far so good. The SPNEGO filter is working well for me so far. I pieced it together using various resources. This was my starting point. I documented my journey in this thread. Niels' write-up was very helpful but with the SPNEGO filter, I don't really need Apache, just ORDS deployed into Tomcat is sufficient. I like that, one less moving part to worry about. People much smarter than me have done a thorough evaluation of ORDS vs OHS vs EPG and concluded that ORDS is the future, that's good enough for me :-)

                                 

                                Next step is to regression test our applications to make sure they don't need any changes due to the move from OHS to ORDS. All of them use a custom page sentry based on the HTTP header CGI variable REMOTE_USER which is what the SPNEGO filter does. So, to my pleasant surprise, the migration from OHS to ORDS should involve zero changes to our applications.  Will let you know if I come across any gotchas during testing.

                                 

                                I am too lazy to blog but I will be happy to proof-read what you come up with :-) Feel free to email me if I can be of any help.

                                 

                                I do have a few questions, mainly around how Tomcat, SPNEGO and servlet filters and all that stuff works, most of them are documented in that OTN thread. The questions are more for my understanding than anything else. I get anxious when I don't fully understand any part of the technology stack I implement, call it a character flaw. I think I am striking out on OTN forums for Tomcat questions, probably need to hop on to stackoverflow.com or some specialized Tomcat forums.

                                 

                                Thanks