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

2 Part: Oracle SELECT procedure and JAVA Call

500 PTS - 2 Part Question

Part 1: Procedure in Oracle
====================
I have a table called "USERS" in an Oracle 9i database.  I need to create a procedure to get data from that table on a column titled "ZIPCODE".  Here is what I have so far, please correct and complete for me so that this will work for a JSP app.

        PROCEDURE       "USERS_GETBYZIPCODE" (
            p_ZIPCODE   IN VARCHAR2,
            p_RESULTS   OUT USERS%ROWTYPE
        )
        AS
        CURSOR USERSBYZIP IS SELECT * FROM USERS WHERE ZIPCODE = p_ZIPCODE;
        BEGIN
            OPEN USERSBYZIP;
            -- Complete my procedure!
            CLOSE USERSBYZIP;
        END;


Part 2: Calling Oracle Procedure from JAVA
==============================
Now that you have completed my procedure in Step 1, show me how I can call the procedure and get a RecordSet object for use in my JSP application.  I am using the java.sql package in a 1.4.2_11 JSP web application.  

Thanks in advance for your assistance.
0
mmarksbury
Asked:
mmarksbury
4 Solutions
 
CEHJCommented:
Part 1 should be moved to the appropriate database TA. We can help with the second. See

http://javaalmanac.com/egs/java.sql/CallProcedure.html
0
 
mmarksburyAuthor Commented:
I'd actually like someone who has done this before using both Oracle and JSP to answer based on their experience so that I do not get a vague answer.  Otherwise I'll get a broad answer in ORACLE topic and a broad answer in JAVA topic.  

This approach ensures I get an answer that addresses my need, hence the 500 PTS.
0
 
CEHJCommented:
>>Otherwise I'll get a broad answer in ORACLE

Why should you? The first part is a straightforward SP question in a language unrelated to Java ...
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!

 
hoomanvCommented:
> I'd actually like someone who has done this before
Im not the one

however the link is helpful
http://java.sun.com/docs/books/tutorial/jdbc/basics/sql.html
0
 
mukundha_expertCommented:
for PART 2:


1. first get a connection to the database and create a callable statement.
2. set the INput parameters
3. register the OUT parameters
4. execute
5. then get you can get the out parameters thro getXXX methods

sample:

Class.forName("oracle.jdbc.driver.OracleDriver");
Connection connect = DriverManager.getConnection("url","username","passwd");
CallableStatement cs = connect.prepareCall("begin procedurename(?,?)");
cs.setXXX(1,value);
cs.registerOutParameter(2,Types.DATATYPE);

cs.execute();
result = cs.getXXX();
0
 
mukundha_expertCommented:
Part 1 : am not very sure about it. i ve done it a long back. jus try and see

 PROCEDURE       "USERS_GETBYZIPCODE" (
            p_ZIPCODE   IN VARCHAR2,
            p_col1   OUT USERS.COL1%TYPE
           p_col2 OUT USERS.COL2%TYPE
         .... add all the cols u need
        )
        AS
        CURSOR USERSBYZIP IS SELECT col1,col2,col3.... FROM USERS WHERE ZIPCODE = p_ZIPCODE;
        BEGIN
            OPEN USERSBYZIP;
               FETCH USERSBYZIP INTO p_col1,p_co12;
            CLOSE USERSBYZIP;
        END;
0
 
mukundha_expertCommented:
for pat1:

i think you can also try,

BEGIN
SELECT col1,col2,col3,.... INTO p_col1,p_col2,p_col3 .. FROM USERS WHERE ZIPCODE = p_ZIPCODE;
END;

0
 
Mayank SAssociate Director - Product EngineeringCommented:
>> how I can call the procedure and get a RecordSet object for use in my JSP application.

Its better not to write any database connectivity code in your JSP. Use the DAO design pattern to ensure that your front-end does not depend on the back-end and where the data-store is actually located.

Read more about how to implement design-patterns in J2EE applications:

http://java.sun.com/blueprints/corej2eepatterns/Patterns/DataAccessObject.html 
0
 
KantiCommented:
For the question you had asked here is an example using JSP to ORALCE but it is better to  not to keep  the DB connectivity code in JSP page

http://elab.bus.umich.edu/how-to-oracle-jsp.php
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now