7 Replies Latest reply on Nov 4, 2009 6:21 PM by 675865


      I am trying to call an Oracle stored procedure through a DAD via AJAX in order to populate a select box (and for other things also, if I can get this to work!). Although I can paste the URL that returns the data into a browser window and display the procedure results with no problem and although the AJAX call works perfectly when I place the results of the stored procedure in a local web page and call it with AJAX, every time I try to call the stored procedure directly, I get an "Authorization Required" message from the statusText+ property and no data in either the responseText+ or the responseXML+ properties, although the readyState+ property shows a completed transaction (value = 4).

      My very basic AJAX code:

      <script type="text/javascript" language="JavaScript">
         var vOpts = new Array();
         var vSel = document.getElementById("select_Element_From_Webpage");

         var vURL = "http://MyWebServer/MyDAD/MySchema.MyPackage.MyStoredProcedure?MyParameter=MyParameterValue"; //Returns a string (via the htp.prn system procedure) equivalent to a Javascript array.
         //var vURL = "test3.html"; //Test page with correctly formatted returning data - works with the AJAX call.
         var vAJAX = GetXmlHttpObject(); //Call to function that returns an AJAX function based on browser.

         var j = 0;
         vAJAX.open("GET", vURL, true);
         catch(e){alert("not working!");}
         vAJAX.onreadystatechange = function()
            alert("Status = " + vAJAX.statusText);
            {  alert("Response Text: " + vAJAX.responseText);

               vOpts = eval(vAJAX.responseText);
               for(j = 0; j < vOpts.length; j++) vSel.options[j] = new Option(vOpts[j][0], vOpts[j][1], vOpts[j][2]);

      function GetXmlHttpObject()
         var xmlHttp = null;
         {  //Firefox, Opera 8.0+, Safari
            xmlHttp = new XMLHttpRequest();
         {  //Internet Explorer
            try {xmlHttp = new ActiveXObject("Msxml2.XMLHTTP");}
               try {xmlHttp = new ActiveXObject("Microsoft.XMLHTTP");}
               catch(e){alert("Unable to create XMLHTTP Object!");}

         return xmlHttp;

      I think it may have something to do with the security on the database itself, but once I have logged onto the database from my browser, I can access the stored procedure from any browser tab without having to re-log on, so it makes no sense to me why this is happening. I have also attempted to pass in the username and password via the open+ command, but the xmlhttp object does not seem to support this, although it's well documented in the specs. I have thoroughly searched the internet and can find nothing about this, except some information regarding mod_plsql and [how to format the request header to upload an XML document|http://forums.oracle.com/forums/thread.jspa?messageID=1162892] which I thought might be promising, but turns out to be a bust. Perhaps I just don't understand what the guy is doing - he doesn't really give a complete explanation. I did learn some neat JavaScript tricks from his thread, though. :-)

      Judging from the lack of comments on the internet, it would appear that nobody has attempted to do this, which seems ridiculous. I'm sure it's something simple that I'm missing, since I'm new to AJAX programming. I have done similar things by calling stored procedures via hidden IFrames but I wanted to avoid the large amount of additional code required to load objects via this method. Any help would be appreciated.
        • 1. Re: AJAX WITHOUT Apex
          Roel Hartman
          I don't see what's wrong with your code (at a glance), but in [this blog post|http://roelhartman.blogspot.com/2009/09/getting-rid-of-annoying-popup-help.html] there is a "getRequest" procedure listed that my be of any help to you....
          • 2. Re: AJAX WITHOUT Apex
            All flavors covered here ==> http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:234614318676
            incl hidden frame, Apex & Non-Apex
            1 person found this helpful
            • 3. Re: AJAX WITHOUT Apex
              Sorry, Roel - they have this blog locked up and I can't get to it.

              Thanks, maceyah. After looking over the ajax solution presented in the responses farther down, I am more convinced that there is some problem with the server configuration. I have gotten access to the logs from our DBA and I'm going to go over them and see if I can discern where the problem is occurring. Thank you both for your help.

              Edited by: shadeclan on Sep 29, 2009 5:58 AM
              • 4. Re: AJAX WITHOUT Apex
                Roel Hartman
                Why? It is mine ... ;-(
                • 5. Re: AJAX WITHOUT Apex
                  I work for NYS - they are a little retarded about what the employees can and can't see. They are especially hard on blogs, which they classify as "social networking sites". Why I can get to Dilbert.com and not to your blog is anybody's guess. ;-)

                  Edited by: shadeclan on Sep 29, 2009 6:04 AM
                  • 6. Re: AJAX WITHOUT Apex
                    Got It!

                    Mr. "Just Starting Out with AJAX" found a [website with some good information|http://www.hunlock.com/blogs/The_Ultimate_Ajax_Object] (No, Roel, I don't know why I could access this blog and not yours :-( ). Turns out that AJAX doesn't work cross-domain - probably for security reasons. I can't work with a webpage on my desktop (one domain) and have the AJAX code call anything from the Oracle DAD (other domain)! Frames and IFrames work because they don't have this limitation by design! Ta daa!

                    Looks like there's some other good information on this website, too. Gonna have to read it very carefully.

                    Thanks to everyone for their help. :-D
                    • 7. Re: AJAX WITHOUT Apex
                      Greetings, This post has some time already but here go my thoughts.

                      Ajax is restricted to same domain for security reasons yes (i'm not sure though).

                      There is however a way around it as always, you make an ajax request to your server which using a webservice can comunicate with an outer domain webservice and get what you need.
                      It may get slow, but i think it would work.

                      Best regards