Home » Open Source » Programming Interfaces » PL/SQL and AJAX
PL/SQL and AJAX [message #278263] Fri, 02 November 2007 10:50 Go to next message
Duane
Messages: 398
Registered: December 2002
Senior Member
Does anyone have an example of using PL/SQL and AJAX?

What I'm really after is a simple example of submitting a variable, doing a lookup on a table on that variable, then displaying the results back to the page without doing a page reload.

Is that even possible with PL/SQL? From what I've read, it sounds like we should be able to do it.
Re: PL/SQL and AJAX [message #278270 is a reply to message #278263] Fri, 02 November 2007 11:02 Go to previous messageGo to next message
Michel Cadot
Messages: 59149
Registered: March 2007
Location: Nanterre, France, http://...
Senior Member
Account Moderator
PL/SQL does not know AJAX.
Maybe AJAX (which I don't know) can call SQL and PL/SQL.
I move this question in Open Database Interface forum.

Regards
Michel
Re: PL/SQL and AJAX [message #278312 is a reply to message #278263] Fri, 02 November 2007 16:23 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
AJAX is simply javascript programming that opens a channel to the server, executes a URL and return the output to your page without the need to reload the page. This is a procedure I wrote that uses AJAX and creates a form.

create or replace procedure ajax_test4 is
begin
  htp.p('
  <script type="text/javascript" language="javascript">
    function makeRequest(url) {
    //
    // This javascript function makes the connection
    // to the server to execute a script
    //
        var httpRequest;

        if (window.XMLHttpRequest) 
        { // Mozilla, Safari, ...
            //alert("Create a new httpRequest for Mozilla");
            httpRequest = new XMLHttpRequest();
            if (httpRequest.overrideMimeType) 
            { httpRequest.overrideMimeType(''text/xml''); }
        } 
        else if (window.ActiveXObject) 
        { // IE
            //alert("Create a new httpRequest for IE");
            try {httpRequest = new ActiveXObject("Msxml2.XMLHTTP"); } 
                catch (e) 
                { try {httpRequest = new ActiveXObject("Microsoft.XMLHTTP"); } 
                  catch (e) {}
                }
        }
        if (!httpRequest) 
         {
            alert(''Giving up :( Cannot create an XMLHTTP instance'');
            return false;
         }
        httpRequest.onreadystatechange = function() { alertContents(httpRequest); };
        httpRequest.open(''GET'', url, true);
        httpRequest.send(null);

    }  //end function makeRequest(url)

    function alertContents(httpRequest) 
    {
    //
    // This function collects the response from the server
    // and puts it into a form variable
    //
       if (httpRequest.readyState == 4) 
       {
         if (httpRequest.status == 200) 
          {
           if (httpRequest.responseText == "Valid\n")
            { alert("A valid response was returned"); }
           else
            { document.loadme.f_emp_lname.value = httpRequest.responseText; 
              //alert(httpRequest.responseText); 
            }
           //document.loadme.f_emp_lname.value = httpRequest.responseText;
          } 
         else 
          {
            alert(''There was a problem with the request.'');
          }
        }
    }  //end function alertContents(httpRequest)
    
function checkName(p_emp_id)
{
  //
  // This function executes the makeRequest function that connects to the
  // server, executes the URL.
  //
  url = ''/pls/portal/pickcreator.ajax_procedure?p_emp_id='' + p_emp_id;
    //alert("URL is " + url);
    makeRequest(url);
}  //end function checkName(input)
</script>

<!--  This portion creates the form and runs the query when the EMP_ID field is exited -->
<form name="loadme">
EMP_ID:  <input type="text" name="f_emp_id" onblur="makeRequest(''/pls/portal/itcreator.ajax_procedure?p_emp_id='' + f_emp_id.value)"><br>
EMP_LNAME: <input type="text" name="f_emp_lname">
</form>

  ');
  
end ajax_test4;


This is the procedure that executes on the server, noted in the previous procedure as the "url" reference, and returns a value. It queries my employees database and returns the employee name based on the employee ID entered in the previous field. It will execute when you tab out of the ID field.

create or replace procedure ajax_procedure(p_emp_id in varchar2 default null) is

   v_emp_id    itcreator.employees.emp_id%TYPE;
   v_emp_name  itcreator.employees.emp_lname%TYPE;
   
begin
  
  if p_emp_id is NULL then 
     v_emp_id := '09357';
  else
     v_emp_id := p_emp_id;
  end if;
  
     select emp_lname||', '||emp_fname 
     into   v_emp_name
     from  employees 
     where emp_id = v_emp_id;

  htp.p(v_emp_name);
  
end ajax_procedure;



Hope this helps,
Ron
Re: PL/SQL and AJAX [message #278747 is a reply to message #278263] Mon, 05 November 2007 09:32 Go to previous messageGo to next message
Duane
Messages: 398
Registered: December 2002
Senior Member
Thanks, yes it does. It's actually the very same code I've been playing around with.

I can't seem to the get my code to return an XML file. Have you gotten that part to work? I'm trying to return multiple values, such as, employee id, first name, last name and so on. Using your code, would you just return a string with some type of delimiter and parse it using javascript to populate the input fields?
Re: PL/SQL and AJAX [message #278755 is a reply to message #278747] Mon, 05 November 2007 09:59 Go to previous messageGo to next message
Ronald Beck
Messages: 121
Registered: February 2003
Senior Member
I haven't done much more than I sent to you. I tried some of the XML, but wasn't real successful. My primary purpose was to pop up a javascript "alert" box with information retrieved from the server based on user input. I'm not real strong on javascript coding, so once I got something working, I went with it and moved on to the other aspects of the project. I haven't had the time to go back and "play" with the XML aspect of returning information.

You should be able to experiment with AJAX outside your pl/sql. It's just plain javascripting. Once you get it doing what you want, then stick it into your procedure code.

Hope this helps,
Ron
Re: PL/SQL and AJAX [message #493408 is a reply to message #278755] Mon, 07 February 2011 15:15 Go to previous message
yalimgerger
Messages: 1
Registered: February 2011
Junior Member
It has been a while since this thread is changed. I think it deserves a quick update. Over the last seven years (since anyone wrote anything to this thread, PL/SQL has become much more AJAX friendly. APEX, the leading PL/SQL framework for web development has started to include some AJAX features. There are also two relatively unknown frameworks out there that are AJAX by default i.e. there is no need for the PL/SQL developer to do anything special to combine AJAX and PL/SQL. These frameworks are inherently AJAX. One of them is Formspider (www.theformspider.com) which in the spirit of full disclosure, we built, the other is Turbo-Enterprise (www.turbo-enterprise.com). I have no affiliation with them. Both frameworks are worth to take a look.
Previous Topic: JDBC with Oracle (merged)
Next Topic: Perl DBI vs SQLPLUS vs TOAD
Goto Forum:
  


Current Time: Mon Sep 22 01:30:22 CDT 2014

Total time taken to generate the page: 0.08726 seconds