• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2748
  • Last Modified:

Call pl/sql function from javascript

I?m new to the language(s). If this is in the wrong forum please direct me to the correct one.
I think this is either very very simple or totally impossible ? please tell me.

How can I call an Oracle pl/sql procedure from Javascript.

If I have an existing pl/sql function called mysqlprocThatChecksTheDatabase, is it possible to call it from the button as:

htp.p('<INPUT TYPE="BUTTON" VALUE="Submit" onClick="if (mysqlprocThatChecksTheDatabase(myvalues)) form.submit();">');

If possible I would like to know how, and if it can?t be done then any alternatives would be welcome.

Thanks


0
Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd
Asked:
Thibault St john Cholmondeley-ffeatherstonehaugh the 2nd
  • 11
  • 4
  • 4
  • +2
1 Solution
 
edemcsCommented:
I never did this using JavaScript before, but I know how you can do it using ASP.
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Oh?
I?m not sure that ASP is something I want to get involved with just at the moment (cos I don?t really know what it is).
What I have is a dynamic html page created from plsql. The user can type a value into an ?INPUT TYPE = TEXT? box. They hit an Add to List button. I would like at this point to check if this value already exists in the database before copying it to a selection box.
When the user has finished all their entries to(and removals from) the selection box, Form.submit will attempt to update the database and any update errors are handled, but I don?t want to raise these errors for every entry to the list.
ie. The Add button should call an ?isItAlreadyHere? procedure without attempting an update.
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Sorry, that Oh should be followed by three dots NOT a question mark. It wasn't me it's something to do with the post and it makes my oh dear comment look rude. It wasnt supposed to be . please dont mis-read it.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
edemcsCommented:
what are you developing this in , out of curiosity?  Oracle Portal? WebDB?
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Oracle Portal - using dynamic pages. but I am very new to it.
0
 
edemcsCommented:
ok, in that case, don't worry about ASP or anything else, I thought you were doing this through HTML.  I haven't worked too much with Oracle Portal. Sorry. :)
0
 
graf27Commented:
one easy way is to use the WEB-PL/SQL Packages from IAS (formerly OAS). With this packages (htp,htf or WSGL), you can directly request and generate HTML/XML-pages. So you can create a procedure i.e:
        create or replace procedure current_users
             AS
                     ignore boolean;
             BEGIN
                     htp.htmlopen;
                     htp.headopen;
                     htp.title(`Current Users');
                     htp.headclose;
                     htp.bodyopen;
                     htp.header(1, `Current Users');
                     ignore := owa_util.tableprint(`all_users');
                     htp.bodyclose;
                     htp.htmlclose;
             END;
             /
             show errors

and than use it in your javascript ie:

<script>
        function openwin() {
          frmPopup = open( current_users , "Name","scrollbars=yes,resizable=yes,width=450,height=500");
         frmPopup.focus();
          if (frmPopup.opener == null) {
                frmPopup.opener = self;
          }
       }
</script>

more infos: http://technet.oracle.com/doc/was3x/was301/cart/plaguse.htm
0
 
CarlovskiCommented:
I may be getting the wrong end of the stick completely,
but it seems to me that you want to query a database, which I assume is living on your server, from a html form, using javascript, which runs on the client machine.
You'll need to use your button to call the procedure on the server, using some kind of server side scripting (CGI,ASP,PHP etc)
 
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Carlovski,

I do want to query the database, but I would rather not use Javascript at all. Its just that the onClick  of the button on the form (INPUT TYPE = "button" etc) seems to want to call a javascript function and not my existing pl/sql function (error: function not defined).

It may be my syntax and a really simple solution, but I can find no reference anywhere on how to do this so it may not be possible.

I am using three lanuages that are new to me already and adding another seems a bit of an overhead
when all I really want is a return from a function.
0
 
lolomurosCommented:
Well, and ?if you call a jsp in your button?, that opens a conection to the database, calls the pl, and close the connection.
Then you have to write a java class to connect to the database.
I think an example here may be too much.

Lolo.
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
I already have the database connection.
In the oracle I have a procedure written in pl/sql which will dynamically create the html and javascript to produce a page.
There is another procedure (IsItInIt) which will return True or False depending on whether the parameter value sent it already exists in a table.
I can call this procedure directly from within the pl/sql area of the code.
I have included an Add button on the page which will ideally call the IsItInIt procedure and then conditionally call a movestufffromoneboxtoanother piece of javascript which is included in the page.
On form.submit all the values in the second box are inserted into the database.

All I want to do is call IsItInIt from the button.onClick but I get either a syntax error or function not defined.
Is it just syntax or is it not possible.

htp.p('<INPUT TYPE="BUTTON" VALUE=">" WIDTH="60%" onclick = "if (!isItInIt(newvalue))addvalue(this.form.newentry)">');
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
graf27

I'm still looking at your code and trying to get it to compile for me.

Not sure if it is what I am looking for though - does it open a separate pop-up window ?
0
 
lolomurosCommented:
0
 
lolomurosCommented:
Well, I use a button submit on a form, not button onclick..
with the action(or call to the plsql procedure) in the form, not in the button
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
lolomuros,

Thats what I am trying to avoid.
There may be several entries on this form and I would like to validate each one separately before the form is submitted to the update procedure. Otherwise I may have to flag a chain of errors - The user would fix one and then get another message that the next value was invalid and so on.
0
 
lolomurosCommented:
well, then you could assign a value to the button and make the submit in a javascript function...
<SCRIPT>
function function_name(parameter){
document.formulario.action=parameter;
document.formulario.submit();
</SCRIPT>
form name="formulario" action=""
button onclick="javascript:function_name(value1)"
button2 onclick="javascript:function_name(value2)"

maybe this?
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
I still need to call my pl/sql validation proc from javascript

function_name(parameter)
   if IsItInIt(parameter)...

And the syntax still fails or procedure not defined
0
 
graf27Commented:
the java-script code is a function to open a new window which content is from the pl/sql-procedure "current_users"
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
graf27,

This looking tantalizingly close, but not quite useable yet.

To get rid of the window I have butchered your current_users proc into:

Create or Replace FUNCTION GIMME_5
 RETURN NUMBER
is
begin
     RETURN 5;
end;
/
commit;
show errors
--@D:\test\gimme_5.sql

which I can call, but at the moment I am getting no return from it.

<script>
function gimme5(){
retval=open("gimme_5","name");
alert(retval);
}
</script>

retval is fairly obviously an object, if I could get the return value from gimme_5 I think I will have my answer.
0
 
graf27Commented:
sorry, I don't know any technic to connect to the database directly from javascript. The protocol is established to request-response. So, I think you can do:

1. Generate the HTML-page from the Database( java, jsp, pl/sql, perl, ... ). So you can send any Database-data to your page, also results from your pl/sql packages/functions.

2. Generate the HTML-page in any way, containing local javascript-functions/variables. Use javascript as extension to HTML to calculate any variable on this page.

To clarify the example gimme_5:

the open() call is a request to the webserver, to send back a HTML-file. So gimme_5 can only be a function to generate(!) an HTML-file (and not for passing back a local javascript-variable).
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
graf27,

> not for passing back a local javascript-variable).

I think this means that what I was hoping to do cannot be done - at least not in the way that I wanted.

Would you agree ?
0
 
graf27Commented:
yes, I agree ( hope all the experts to ).

From one page in your browser, yo can only request another "page". With JAVASCRIPT, you can calculate any local variables and  use it for a nice navigation inside your page. For generating any pages (complete HTML-code), you can use any tool  inside/outside the Database ( Perl, JavaServerPage, JavaServlet, PL/SQL , ActiveServerPage .. ). These tools can have database-connection and use your PL/SQL-Function (only for generating a complete HTML-coded page!).
0
 
Thibault St john Cholmondeley-ffeatherstonehaugh the 2ndAuthor Commented:
Cannot be done

That would explain why I didnt get 10,000 replies the moment I posted my Q.

Never mind I will find another way - learn java or something :7)

Thanks graf27 for your explanation and for your time and effort.
0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 11
  • 4
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now