Calling PL/SQL function from JavaScript
Terri I.
JavaScript code? I have a form with a button - clicking that button
launches some JavaScript code. I would then like that code to run a
PL/SQL function, but simply including a line within the JS code like
'var retcode = Function(variable);' results in an error that Function
is not defined.... Any help would be appreciated!
Nuno Souto
AFAIK, you'd need to use a JSP-like approach. The button fires off
not a direct PL/SQL call, but a little Java servlet that does the
work: either via calling PL/SQL with JDBC or some other way of calling
Java directly in the DB engine (of which I know of none...). So, a
little more work may be required. There is also the possibility of
using PSP (PL/SQL pages) but that requires you to use the ORACLE iAS:
I'm not aware of any other AS that has a PL/SQL plug-in inside.
Cheers
Nuno Souto
nso...@optushome.com.au.nospam
John Russell
At first glance, it looks impossible. But recently I got the idea of
doing something similar (only with a stored procedure rather than a
function). So let's walk through the thought process.
Netscape had some kind of database access from Javascript called
'Livewire' but I don't know if that still exists. Doubtful that would
work very well, especially considering that many or most browsers
running Javascript would be Internet Explorer.
With PL/SQL Server Pages (PSP), you can mix PL/SQL and Javascript in
the same file. But I think the flow of execution would not be what you
want. The PL/SQL function would get executed once, as the HTML was
generated, rather than each time the event was received by Javascript.
I think we're getting warmer... although kludges of the highest order
might lie ahead.
If you used a PSP, or a PL/SQL stored procedure with the PL/SQL Web
Toolkit, you could create a window that displayed the function results
as part of the HTML. You could open the pop-up window using the URL
of the stored procedure, and encode the parameter variable in the URL
like so:
popurl = "http://wherever/pls/mypopup?param=" + variable
If this pop-up window contained Javascript, it could assign the value
of the PL/SQL function to a Javascript variable, something like:
htp.p('var retcode = ' || Function(param) || ';'
The HTML would contain the actual literal value of the return code.
Then the Javascript in the pop-up window could pass the value back to
the original window using the notation for calling functions across
windows. Then the pop-up window could close itself. (Maybe it was
only very tiny to begin with.)
Using the extra window seems like a complete kludge, but I don't see
any way to do what you ask without passing the parameters to PL/SQL
via name-value pairs in the query string, and passing the return value
back as a literal in the HTML code. Maybe instead of a second window,
you could use two frames within the same window and set
<framename>.location to the URL with the encoded parameter.
John
--
Got an Oracle database question?
Try the search engine for the database docs at:
http://tahiti.oracle.com/
Vladimir M. Zakharychev
IFRAMEs are just embedded frames, with the same capabilities, but one good feature that we use here: it's display may be set to none (style="display:none") and it will become invisible. Second part of the trick involves the way JavaScript/JScript accesses document in a different frame. You probably know that you don't have direct access to any properties in a cross-frame document. But what you can do is call any method in that document. We are using this fact to dynamically change content of the document without refreshing it with new parameters that may alter the content. Here's simple example:
1) main document defines an IFRAME and a callback function:
<script language="JavaScript">
function callbackFunction(param)
{
// do whatever you want here to alter the document according to
// passed parameter, for example, use document.createElement('OPTION')
// to create a new option value and add it to a select.
}
</script>
<iframe name="iframe1" src="initial.src" style="display:none"></iframe>
...
<!-- now some code to get the new data on event
here I assume that you are calling pl/sql code through mod_plsql or
OWA or a servlet.
-->
<button onClick="java script:document.frames('iframe1').src='some.plsql.procedure?param=value';return true;">Get New Data</button>
....
when user clicks Get New Data button, this triggers execution of some.plsql.procedure in context of the hidden IFRAME. How do we get the results from that IFRAME to our main document? That's what callbackFunction() is for.
some.plsql.procedure(param varchar2) is
begin
htp.p('
<script language="JavaScript">
parent.callbackFunction("whatever value you want to pass");
</script>
');
end;
That's it! Being executed in context of the IFRAME, this JavaScript just calls parent.callbackFunction() (which is defined and accessible, unlike properties of parent, like innerHTML or anything else) and passes some dynamically generated data to it. callbackFunction() then alters content of the parent document, because it is defined in context of that document and has access to all its properties and objects! For example, this function may repopulate some SELECT with new OPTIONs, or do anything else with the data it receive.
Hope this trick would be of help to you as it was to us.
regards,
Bob.