How Do I call an Oracle stored procedure with JSP?

I need to know how to call a stored procedure in a JSP page.   I am using Oracle 9i Release 2 (9.2.0.5.0) or Oracle 10g (10.1.0) database.  O.S. Windows 2000.  Please give me an example, a line of code, or a reference on how to do this.  If this is not possible then tell me that also.
perrycnAsked:
Who is Participating?
 
koppchaCommented:
You can calling in the JSP using the scriptlets it self
just establish the connection Class.forName()
get the connection  and do some thing like this to call the proc
<%
CallableStatement csmt = con.prepareCall("{call Procedure name(?.?)}");
csmt.registerOutParameter(column no,data type);
csmt.setInt(columnno,columnname);
csmt.execute();
%>
0
 
perrycnAuthor Commented:
Also I am using Apache web server installed with Oracle Application Server 10g and JDeveloper 10g as the development tool for creating the web pages.  
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
perrycnAuthor Commented:
These articles explain how to create an Oracle Stored Procedure and Java Stored Procedure.   How do I make the call in the JSP page?  Give an example.
0
 
TimYatesCommented:
exactly as you would in java...

Is the question that you want to know how to load the JDBC drivers, connect to an Oracle database, execute an sql query and parse the result set?
0
 
perrycnAuthor Commented:
OK this explains how to call a Sybase stored procedure in java.   So are you saying I need to call the stored procedure in a java bean and then call the javabean in the jsp page.   Is there a way to call the stored procedure from within the jsp page?   For example the code for calling a java bean is:

first you have:
<jsp:useBean class="abcd.view.ControlledDataItem"  id="cdi" scope="page" />

then where you make the call you have:
<%= cdi.getCDI(l_wholeword, l_schema) %>



0
 
perrycnAuthor Commented:
Ok, koppcha, this is what I was looking for.  I don't quite know what you mean when you say:  establish the connection Class.forName()
get the connection???       I will have to try this later since I have to work on something else right now.
0
 
koppchaCommented:
This is the link that explains how to connect
http://www.cise.ufl.edu/class/cis4301fa04/Assignments%20Information%20and%20Database%20Systems%20I%20Fall%202004_files/example.html

You can keep the whole connect part and executing part inside the scriptlet in JSP
0
 
Jim CakalicSenior Developer/ArchitectCommented:
Hi,

You can also accomplish this with a custom tag library.
http://www.servletsuite.com/servlets/dbtag.htm

An example (from the docs):

<sql:setQuery id="A" res="myData" query="{call myProc1(?,?)}">
   <sql:setParameter position="1" type="int">6789</sql:setParameter>
   <sql:setOutputParameter position="2"><%=java.sql.Types.INTEGER%></sql:setOutputParameter>
</sql:setQuery>

<!-- now we can use output data -->
<%=(java.sql.CallableStatement)myData.getInteger(2)%>

<!-- close our statement -->
<sql:closeStatement res="myData"/>

Regards,
Jim
0
 
perrycnAuthor Commented:
I finally got the the stored procedure call to work in a jsp page.  koppcha, I did it similar to your statements.  jim cakalic I did try it your way but could not get JDeveloper to include the tag library.  I tried many different ways.  I guess I would need to write another question.  But I at least got it work one way.  Both you gave good answers.  Thanks for your help.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.