Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


How Do I call an Oracle  stored procedure with JSP?

Posted on 2005-04-21
Medium Priority
Last Modified: 2010-06-20
I need to know how to call a stored procedure in a JSP page.   I am using Oracle 9i Release 2 ( 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.
Question by:perrycn
  • 5
  • 3
  • 2
  • +1

Author Comment

ID: 13834492
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.  

Author Comment

ID: 13834960
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.
Independent Software Vendors: 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!

LVL 35

Expert Comment

ID: 13835008
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?

Author Comment

ID: 13835404
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) %>


Accepted Solution

koppcha earned 260 total points
ID: 13835456
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);

Author Comment

ID: 13835772
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.

Expert Comment

ID: 13837272
This is the link that explains how to connect

You can keep the whole connect part and executing part inside the scriptlet in JSP
LVL 19

Assisted Solution

by:Jim Cakalic
Jim Cakalic earned 240 total points
ID: 13838745

You can also accomplish this with a custom tag library.

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>

<!-- now we can use output data -->

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


Author Comment

ID: 13982601
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.

Featured Post

Technology Partners: 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!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Dropbox has a relatively new feature called Smart Sync.  This feature allows Dropbox Professional (not plus) and Dropbox Business (if enabled) users to store information in Dropbox WITHOUT storing any files on their computer.
Don’ts and Dos are two important end products of software testing basics that a tester needs to regard. This article attempts to explain the principles of both.
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month11 days, 2 hours left to enroll

571 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