Solved

Call pl/sql function from javascript

Posted on 2001-06-15
23
2,041 Views
Last Modified: 2007-12-19
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
Comment
  • 11
  • 4
  • 4
  • +2
23 Comments
 
LVL 8

Expert Comment

by:edemcs
ID: 6195710
I never did this using JavaScript before, but I know how you can do it using ASP.
0
 
LVL 17
ID: 6195851
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
 
LVL 17
ID: 6195862
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
 
LVL 8

Expert Comment

by:edemcs
ID: 6195919
what are you developing this in , out of curiosity?  Oracle Portal? WebDB?
0
 
LVL 17
ID: 6195933
Oracle Portal - using dynamic pages. but I am very new to it.
0
 
LVL 8

Expert Comment

by:edemcs
ID: 6196065
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
 
LVL 3

Expert Comment

by:graf27
ID: 6206070
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
 
LVL 2

Expert Comment

by:Carlovski
ID: 6206250
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
 
LVL 17
ID: 6206621
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
 

Expert Comment

by:lolomuros
ID: 6207058
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
 
LVL 17
ID: 6209371
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 17
ID: 6209446
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
 

Expert Comment

by:lolomuros
ID: 6209450
0
 

Expert Comment

by:lolomuros
ID: 6209457
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
 
LVL 17
ID: 6209583
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
 

Expert Comment

by:lolomuros
ID: 6209612
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
 
LVL 17
ID: 6209670
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
 
LVL 3

Expert Comment

by:graf27
ID: 6209675
the java-script code is a function to open a new window which content is from the pl/sql-procedure "current_users"
0
 
LVL 17
ID: 6210017
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
 
LVL 3

Expert Comment

by:graf27
ID: 6213145
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
 
LVL 17
ID: 6213317
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
 
LVL 3

Accepted Solution

by:
graf27 earned 100 total points
ID: 6217254
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
 
LVL 17
ID: 6217363
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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

In this article, we'll look how to sort an Array in JavaScript, including the more advanced techniques of sorting a collection of records either ascending or descending on two or more fields. Basic Sorting of Arrays First, let's look at the …
The task A number given should be formatted for easy reading by separating digits into triads. Format must be made inline via JavaScript, i.e., frameworks / functions are not welcome. So let’s take a number like this “12345678.91¿ and format i…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
The viewer will learn the basics of jQuery including how to code hide show and toggles. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now