Hi Guys,
I have a PL/SQL procedure that calls a php script (which returns an XML document) using AJAX. I want to change it to call another PL/SQL procedure instead of the PHP script. I have created a PL/SQL procedure that mimics the output of the PHP script exactly, however the AJAX is no longer working. Here is my code:
PL/SQL - Caller procedure
PROCEDURE myWebpage is
begin
-- Web page header
htp.print('
function getSubcategories(){
var ajaxRequest;
try{
// Opera 8.0+, Firefox, Safari
ajaxRequest = new XMLHttpRequest();
} catch (e){
// Internet Explorer Browsers
try{
ajaxRequest = new ActiveXObject("Msxml2.XMLHTTP");
} catch (e) {
try{
ajaxRequest = new ActiveXObject("Microsoft.XMLHTTP");
} catch (e){
// Something went wrong
alert("Your browser broke!");
return false;
}
}
}
ajaxRequest.onreadystatechange = function(){
if(ajaxRequest.readyState == 4 && ajaxRequest.status == 200){
var xmlDocument = ajaxRequest.responseXML;
var options = xmlDocument.getElementsByTagName("option");
var subcatSelect = document.getElementById("subcat_sel");
subcatSelect.options.length = 0;
for(var i=0; i<options.length; i++)
subcatSelect.options[i] = new Option(options.firstChild.data);
if ( document.requestInfo.pCategory.value == "Academic" ) {
document.getElementById(''acad_div'').style.display = "inline";
}
else {
document.getElementById(''acad_div'').style.display = "none";
document.requestInfo.pContactMethod.value = "Email";
}
}
}
// THIS LINE IS THE PHP CALL
ajaxRequest.open("POST", "http://mysite.com/dsu/query/ajaxquery.php", true);
// THIS LINE WILL BE USED FOR PL/SQL CALL
// ajaxRequest.open("POST", "http://mysite.com/SSBDADTST7/pkgAjax.ajaxQuery", true);
ajaxRequest.setRequestHeader(''Content-Type'', ''application/x-www-form-urlencoded'');
ajaxRequest.send("p_category=" + document.requestInfo.pCategory.value);
}
');
-- REST OF WEBPAGE
end myWebpage;
PHP SCRIPT
<?php
// Query Script ajaxquery.php
include("../../../ajax/connect.php");
$category = $_POST['p_category'];
// Query
if ( $category == "Academic" ) {
$query = ociparse($conn, 'select subject_desc from all_subjects
order by subject_desc');
}
else {
$query = ociparse($conn, 'select subcategory_desc from categories
where category = \'' . $category . '\'
and active = \'A\'
order by subcategory_desc');
}
ociexecute($query);
// Output
header("Content-type: text/xml");
echo "<?xml version=\"1.0\"?>";
echo "<options>";
while (ocifetchinto($query, $row, OCI_ASSOC)) {
$keys = (array_keys($row));
foreach ($keys as $k) {
echo "<option>" . $row[$k] . "</option>";
}
}
echo "</options>";
?>
PL/SQL - Called Procedure
PROCEDURE ajaxQuery ( p_category in varchar2 ) is
type ref_cursor is ref cursor;
subcat_curs ref_cursor;
lSubcat categories.subcategory_desc%type;
begin
if ( p_category = 'Academic' ) then
open subcat_curs for
select subject_desc from all_subjects
order by subject_desc;
else
open subcat_curs for
select subcategory_desc from categories
where category = p_category
and active = 'A'
order by subcategory_desc;
end if;
htp.print('<?xml version="1.0"?>');
htp.print('<options>');
loop
fetch subcat_curs into lSubcat;
exit when subcat_curs%notfound;
htp.print('<option>'||lSubcat||'</option>');
end loop;
htp.print('</options>');
close subcat_curs;
end getSubcategories;
Now I have tested the output of the PHP script and the second PL/SQL procedure and they are identical. I uncommented the line in the first PL/SQL procedure under "THIS LINE WILL BE USED FOR PL/SQL CALL". But the line where
var xmlDocument = ajaxRequest.reponseXML;
is returning a null object when I use the PL/SQL call. Do I need to modify something else for this to work?
Here's my oracle version, just in case:
Oracle Version:
Oracle Database 10g Enterprise Edition Release 10.2.0.4.0 - 64bi
PL/SQL Release 10.2.0.4.0 - Production
CORE 10.2.0.4.0 Production
TNS for IBM/AIX RISC System/6000: Version 10.2.0.4.0 - Productio
NLSRTL Version 10.2.0.4.0 - Production
Thanks
Edited by: jschmidt10 on May 17, 2010 7:32 AM