14 Replies Latest reply: Mar 19, 2014 7:33 AM by Billy~Verreynne RSS

    Using htp.p in PL/SQL routines

    Andre Olivier

      We have an Apex application, originally written in Apex 4.0 on a Oracle 10g XE database. Within this application, use is made of htp.p calls at various points (eg. to generate a report at the end of a process).

       

      Following an upgrade to Oracle 11g XE and Apex 4.2.4, the htp.p calls do not work. We do not get any error messages, either on screen or in the logs, they simply just do nothing.

       

      Our initial assumption was that this had something to do with the fine grained security introduced in 11g, and we attempted to fix the situation by adding ACL entries. Either we have not arrived at the correct ACL setting, or the problem isn't related to security. We are now at the point of rewriting various PL/SQL functions to remove the htp.p constructs, and use other mechanisms as a workaround. This is obviously a time consuming exercise, and there is an element of risk involved, and all the while the production system is not working correctly.

       

      Does anyone have any suggestions around this?

        • 1. Re: Using htp.p in PL/SQL routines
          kvlek

          Hi Andre,

           

          htp.p should work. Have you tried to run the page with the debug option and look for some errors there?

          I am pretty sure it has to do something with security but not ACL's. More like execute rights.

           

          Regards,

          Kees Vlek

          -----

          +If the question is answered please change it to answered and mark the appropriate post as correct/helpfull.+

          • 2. Re: Using htp.p in PL/SQL routines
            Kofi

            Hi Andre,

            I agree with Kees there.

            Also, if you use SQL Developer, you can try using enabling OWA Output and use it  to test the htp.p calls right there to see if you have a problem at the database level.
            Cheers

            Kofi

            • 3. Re: Using htp.p in PL/SQL routines
              Andre Olivier

              I am really not winning on this yet, and have a client who is very, very upset at this point. Any assistance will be greatly appreciated. For reference, I am running Oracle 11g XE, Apex 4.2.4, Apex Listener 2.0.5, Tomcat 7.0.50, Java SE 7u50

               

              I have gone through the database privileges on the UTL_HTTP,HTP and OWA_UTIL packages and have confirmed that the required users have EXECUTE privileges. I probably have made it too open at this point because I have basically granted execute to just about everyone (but this is on my local machine, so that's not really an issue).

               

              I have also downloaded SQL Developer (as suggested), and have tried to get to the bottom of the issue, but I am still no nearer a solution.

               

              I have reduced my call to a simple Javascript alert, using both of the following formats:

              BEGIN

              htp.p('<script language="Javascript">');

              htp.p('alert("Hello world");');

              htp.p('</script>');

              END;

               

              and

               

              begin

              htp.script('alert("Hello world")', 'Javascript');

              END;

               

              In both cases, SQL Developer reports "anonymous block completed" with no errors, and the OWA Output pane shows the Javascript (or rather HTML) correctly, so it seems as if the call is being handled correctly, with no security issues, by the database. I do however not get the alert box popping up. The same applies if I try to do a redirect from the current page to another in the application - the call seems fine in the application/database, and I get a success message (if I put one in on the process definition).

               

              One thing of interest is reports I am getting in the Tomcat console. I suspect (but don't know for sure) that this is related to the problem - but I have no idea how to get around these :

              a) Often there is a "304 Not Modified" response, which if I read the log correctly is related to various images

              b) At other times there is a "Adding X-FRAME-OPTIONS: DENY" message

              All the Google search responses to the above are related to mod_plsql, Oracle HTTP server and/or Tomcat with Apache - none of which is my setup

               

              Any suggestions?

              • 4. Re: Using htp.p in PL/SQL routines
                Tom Petrus

                Is anything you put out with htp.p in the html output? View the page source after it finishes loading.

                What do you put out with htp.p? Where (process point)?

                Can you reproduce on apex.oracle.com?

                • 5. Re: Using htp.p in PL/SQL routines
                  Billy~Verreynne

                  HTP.prn() writes text into a buffer. The very same buffer used by Apex PL/SQL code.

                   

                  Do a sanity test first - so you can confirm to yourself that HTP.prn() works. Create a basic procedure like the following:

                  create or replace procedure WebTest is

                  begin

                    HTP.prn( 'Hello world. <hr>' );

                  end;

                   

                  Grant execute on WebTest to public.

                   

                  Then call it via a web browser URL. Use the same base URL as for Apex. Replace the /f?.. call to the flow engine, with a call to this stored proc. E.g.

                  http://my-server.my-domain.com:7777/base/MYSCHEMA.WEBTEST

                   

                  Why would HTP.prn() not work on an Apex page? That could be where and when that call is made when Apex renders the page. You cannot simply willy-nilly make calls to HTP.prn() anywhere in a page. Also, regions can be conditionally rendered - so make sure that the regions using HTP.prn() calls are also regions that are rendered.

                   

                  As for Javascript via HTP.prn() calls - keep in mind that when you add Javascript to a page to be executed, it needs to be executed when the DOM objects and references it use, are available. I often see Javascript fail because of that. In a jquery environment, use $(document).ready(handlerFunction) for ensuring that your custom code runs at the right point (typically after the DOM has been constructed and is ready for use).

                   

                  Lastly - use the Firefox/Chrome (can't and don't use IE myself) console to check for errors. It could be that an existing URL used is old/incorrect, fails to load the required Javascript/CSS/whatever file, and results in a series of cascading errors impacting the contents your HTP.prn() code adds to the page.

                  • 6. Re: Using htp.p in PL/SQL routines
                    04DazzaRPD

                    I'm helping Andre on this, so I hope you don't mind my interjection:

                     

                    After viewing the page source, there is no JavaScript relating to the HTP.P call, apart from the success message defined in the process properties.

                     

                    What we're trying to do is to open a report based on predefined layout and queries via JavaScript, which has to be called via HTP.P as the rest of the required code is in PL/SQL. The process point is "On Submit: After Computations and Validations" as the report depends on what rows we select via a checkbox and the data inside the selected rows, and thus requires the selections to be submitted before the report is printed.

                     

                    So in less-jumbled English: the HTP.P call is nested in a PL/SQL procedure and is meant to produce a PDF based on data selected by the PL/SQL, and all this is meant to happen "On Submit: After Computations and Validations".

                    • 7. Re: Using htp.p in PL/SQL routines
                      04DazzaRPD

                      Tried as you suggested, the process was created but upon execution of the URL Tomcat came up with an error saying "HTTP Status 404: The requested resource is not available"

                      • 8. Re: Using htp.p in PL/SQL routines
                        Billy~Verreynne

                        Tomcat? Does it support calling Oracle web-enabled procedures?

                         

                        The usual options for Oracle web-enabled procedure calls are Apache-with-mod_plsql (also known as OHS/Oracle HTTP Server), and the Apex Listener.

                         

                        Only ever used Apache for this - and all you need is to use the DAD (Database Access Descriptor) for Apex, and replace the Apex portion of the URL (the /f?.. syntax) with the schema name of the web enabled proc, followed by the proc name.

                         

                        E.g.

                        http://server:7777/dad/SCHEMA.PROCEDURE

                         

                        Make sure whatever you use to call PL/SQL web-enabled procedures (like Apex), allows you to also call your custom web-enabled stored proc.

                         

                        See Understanding mod_plsql - Invoking mod_plsql for details.

                        • 9. Re: Using htp.p in PL/SQL routines
                          Billy~Verreynne

                          a114b1a8-d33a-42ce-9cc8-965492df6c19 wrote:

                           

                          So in less-jumbled English: the HTP.P call is nested in a PL/SQL procedure and is meant to produce a PDF based on data selected by the PL/SQL, and all this is meant to happen "On Submit: After Computations and Validations".

                           

                          Doubt that this is a valid point to write data into the HTP buffer of the session. The reason is that after submission a new buffer (including HTTP header) needs to be generated - either for the same page being rendered again, or for a brand new page is to be rendered (i.e. via a branch condition to that the Apex flow engine need to follow).

                           

                          So how can you at post page processing (upon receiving a submitted page, and prior to the new page being rendered by Apex), inject HTML code into the HTP buffer? I would expect that to be overwritten by Apex as it starts rendering the follow on page.

                           

                          The "safe place" to inject HTML code into the HTP buffer is via dynamic PL/SQL regions - or via dynamic actions that make Ajax calls to PL/SQL.

                          • 10. Re: Using htp.p in PL/SQL routines
                            04DazzaRPD

                            We have the Listener installed as well as Tomcat.

                             

                            The HTP.P calls used to work with our previous environment (which was Oracle 10g R2, Apex Listener 1.3.5 (I believe - not sure on the version number), Apex 4.0.1 and Tomcat 7.0.5 (different from 7.0.50), so I believe Tomcat does support web-enabled procedures (unfortunately I'm doing an apprenticeship with this, so not 100% definite).

                             

                            I've just tried calling WebTest using apex as the DAD (as the link I call is usually http://localhost:8088/apex/rest-of-link) along with my SchemaName.WebTest, and the Apex Listener page displayed with 404 - Not Found.

                            • 11. Re: Using htp.p in PL/SQL routines
                              04DazzaRPD

                              An update on this.

                               

                              Following your advice, I moved the HTP.P alert to a region On Load, and can confirm the HTP.P works (so that's a step forward I guess). Thank you for that suggestion

                               

                              So we can no longer process it in the fashion we would've liked to, which is a bit of a bummer. We're now trying to use the Apex Dynamic Actions (created in the Dynamic Actions section of the Page Rendering section) as a new point to execute the PL/SQL from. However we're running into an error that reads as follows:

                               

                              Error parsererror - SyntaxError invalid character.

                               

                              An example of our PL/SQL for this process is:

                              DECLARE TYPE auths is VARRAY(20) of VARCHAR2(30);
                              incauths auths;
                              authf BOOLEAN;
                              currauth VARCHAR2(30);
                              currind INTEGER;
                              i INTEGER;
                              j INTEGER;
                              brcode NUMBER;
                              autbref NUMBER;
                              numf NUMBER;
                              BEGIN select user_branch into brcode from sys_users where user_name = :APP_USER;
                              insert into regsys_debug (debug_point,debug_call,debug_process) values ('AA','start of processing','Create Authority Batches');

                               

                              --Build array of authority names for which batches required
                              incauths := auths('','','','','','','','','','','','','','','','','','','','');

                               

                              for i in 1..APEX_APPLICATION.G_F01.count LOOP authf := FALSE; select authority_code into currauth from transaction_header where transaction_code = APEX_APPLICATION.G_F01(i); currind := 0;
                              for j in incauths.FIRST .. incauths.LAST LOOP if incauths(j) IS NULL then if currind = 0 then currind := j; end if; end if;

                               

                              if incauths(j) = currauth then authf := TRUE; end if; END LOOP; if authf = FALSE then incauths(currind) := currauth; end if; END LOOP;

                               

                              --Create batches for authorities and update transactions for authority with batch no
                              for i in incauths.FIRST..incauths.LAST LOOP if incauths(i) IS NOT NULL then select count(*) into numf from authority_batch where auth_batch_branch=brcode and auth_batch_ref is not null;

                               

                              if numf > 0 then select max(auth_batch_ref) into autbref from authority_batch where auth_batch_branch = brcode; else autbref := 0; end if; autbref := autbref + 1;

                               

                              insert into authority_batch (AUTH_BATCH_AUTHORITY,AUTH_BATCH_DATE_CREATED,AUTH_BATCH_DATE_PRINTED,AUTH_BATCH_USER,AUTH_BATCH_REF,AUTH_BATCH_BRANCH,BATCH_TIMESTAMP,ALL_TRANS_CHECKED_BACK) values (incauths(i),SYSDATE,SYSDATE,:APP_USER,autbref,brcode,:P51_TIMESTAMP,'No');

                               

                              for j in 1..APEX_APPLICATION.G_F01.count LOOP select authority_code into currauth from transaction_header where transaction_code = APEX_APPLICATION.G_F01(j);
                              if incauths(i) = currauth then update transaction_header set transaction_status='Authority Scheduled',authority_batch_no = AUTHORITY_BATCH_SEQ.CURRVAL where transaction_code = APEX_APPLICATION.G_F01(j); end if; END LOOP; end if; commit work; END LOOP;

                               

                              (I tried to neaten it up as much as I could, apologies if it's still unclear)

                               

                              The above code is placed before the HTP.P stuff (I cannot quote the full code as the HTP.P code does not display. However, upon running this dynamic action, I get the aforementioned error and don't know why because the PL/SQL ran fine in 10g. Any ideas?

                              • 12. Re: Using htp.p in PL/SQL routines
                                pkpanda

                                I had the X-FRAME-OPTIONS: DENY" message and I have fixed by changing the application level setting to same source. If you have not done this then the default is DENY.

                                • 13. Re: Using htp.p in PL/SQL routines
                                  Billy~Verreynne

                                  04DazzaRPD wrote:

                                   

                                  We have the Listener installed as well as Tomcat.

                                   

                                  The HTP.P calls used to work with our previous environment (which was Oracle 10g R2, Apex Listener 1.3.5 (I believe - not sure on the version number), Apex 4.0.1 and Tomcat 7.0.5 (different from 7.0.50), so I believe Tomcat does support web-enabled procedures (unfortunately I'm doing an apprenticeship with this, so not 100% definite).


                                  The URLs used by Apex are all calls to stored procedure code in the database. This requires the web server to recognise such a URL, identify the target database, translate that URL into an anonymous PL/SQL block call, create a database session, execute the call, use the OWA libraries to read the result of the call made, and return the result to the browser.


                                  This thus need additional intelligence on the part of the web server. This is provided by mod_plsql for an Apache server. Like mod_php for example, which provides the intelligence for Apache to call and process PHP pages/code, mod_plsql provides the intelligence to Apache for dealing with PL/SQL stored proc database calls from a web client.


                                  Tomcat lacks such a module - and can this not deal with web clients calling PL/SQL stored procs.


                                  Keep in mind that with Apex, the ENTIRE application layer resides INSIDE the database as PL/SQL stored procedures, functions and packages.

                                   

                                  I've just tried calling WebTest using apex as the DAD (as the link I call is usually http://localhost:8088/apex/rest-of-link) along with my SchemaName.WebTest, and the Apex Listener page displayed with 404 - Not Found.

                                  Then you are likely misformatting the URL (case sensitivity is for example critical), or your Apex Listener has some kind of ACL that prevents anything else but Apex calls (vaguely remember something like this when looking at it some years ago).


                                  In my opinion - if you run Apex as a production web application tier, then you need OHS (Oracle HTTP Server) installed. It is part of the Oracle Fusion middleware s/w stack and can be installed as a stand-alone component (no other Fusion s/w, except for OHS).


                                  I would not use EPG (Oracle database's internal web server for Apex), or an Apex Listener. (for production)



                                  • 14. Re: Using htp.p in PL/SQL routines
                                    Billy~Verreynne

                                    Do not write large chunks of code in Apex PL/SQL blocks.

                                     

                                    You need to treat Apex as the rendering layer. Not as the actual app code layer.

                                     

                                    You want your app code as PL/SQL packages - with well designed and "formal" application interfaces.

                                     

                                    From PL/SQL blocks in Apex, you then want to call these app interfaces - instead of writing complex anonymous PL/SQL code blocks that are difficult to read, understand, test, instrument, debug and maintain.