asked on

Calling a PL/SQL stored procedure from Java

This is what I am trying to do:

Create a PL/SQL package that has a procedure in it that gets information from a table (Select * FROM users or something).

Then I want to call that procedure from Java and pass the information to a recordset. I don't need the whole Java program, just the Syntax for calling the PL/SQL procedure and passing it to a recordset. I would probably need the whole PL/SQL program.

Thanks

girionis

A good example of store procedures you can find here: http://otn.oracle.com/sample_code/tech/java/ejb_corba/files/hotel/hotelpaper/hotelJsp.htm and here: http://www.frc.utn.edu.ar/campus/ibm/pdfs/sg245945.pdf

The way to call them is the same for all databases, only the way to create them on the database side differs (I guess).

Hope it helps.

amit_chauhan

Here is the pl/sql procedure.
CREATE OR REPLACE PACKAGE my_pack AS
TYPE user_cur IS REF CURSOR;

FUNCTION get_all_users RETURN user_cur;
END;
/
CREATE OR REPLACE PACKAGE BODY my_pack AS
FUNCTION get_all_users RETURN user_cur IS
c user_cur;
BEGIN
OPEN c FOR SELECT user_id, user_name FROM USERS;
RETURN c;
END;
/

The java portion of this will be something like this :

public class myClass
{
public Vector getAllUsers () throws Exception
{
java.sql.Connection con = null;
java.sql.CallableStatement cs = null;
java.sql.ResultSet rs = null;
try
{
// Get the database connection here
.....
cs = con.prepareCall ("? = call my_pack.get_all_users ()");
cs.registerOutParameter (1, java.sql.Types.OTHER);
cs.execute ();
rs = (ResultSet) cs.getObject (1);
while (rs.next ())
{
int userId = rs.getInt ("user_id");
String userName = rs.getInt ("user_name");
}
}
catch (Exception e)
{
e.printStackTrace ();
throw e;
}
finally
{
if (rs != null)
{
try
{
rs.close ();
}
catch (Exception ex) {}
}
if (cs != null)
{
try
{
cs.close ();
}
catch (Exception ex) {}
}
if (conn != null)
{
try
{
conn.close ();
}
catch (Exception ex) {}
}
}
}
}

Hope that helps
Thanks
Amit

danceswithwolves

ASKER

When I try and create the package body I'm getting an error PL/SQL: SQL Statement ignored
PLS-002001: identifier 'USERS' must be declared.

Any ideas of what may be causing the error?

danceswithwolves

ASKER

When I try and create the package body I'm getting an error PL/SQL: SQL Statement ignored
PLS-002001: identifier 'USERS' must be declared.

Any ideas of what may be causing the error?

danceswithwolves

ASKER

nevermind I just realized that I was getting the errors because the USERS table didn't exist in my DB :)

amit_chauhan

Exactly :) That was just an example, replace the sql with your exact table/columns

Hope that helps
Thanks
Amit

danceswithwolves

ASKER

Everything seems to be working ok except the program seems to crash on this line...
System.out.println("test");
cs.registerOutParameter (1, java.sql.Types.OTHER);
System.out.println("test2");

test only prints once.

amit_chauhan

What is the error that you are getting ?

In case its crashing there, then you probably have to replace the statement with the OracleDriver's CURSOR type (if you are using Oracle driver)

Replace the statement with this statement and rerun the program:

cs.registerOutParameter (1, oracle.jdbc.driver.OracleTypes.CURSOR);

Hope that helps
Thanks
Amit

danceswithwolves

ASKER

It worked, but now it crashed on the

cs.execute ();

// line

amit_chauhan

Whats the exception trace ure getting ? Can u print that .

Amit

danceswithwolves

ASKER

SQLException: ORA-00900: invalid SQL statement

amit_chauhan

Sorry about this, forgot to put {..} in prepareCall statement.

Replace the statement :

cs = con.prepareCall ("? = call my_pack.get_all_users ()");

with

cs = con.prepareCall ("{ ? = call my_pack.get_all_users () }");

Hope that helps
Thanks
Amit

danceswithwolves

ASKER

PLS-00221: 'GET_ALL_USERS' is not a procedure or is undefined ORA-06550: line 1, column 7: PL/SQL: Statement ignored

Does it have to be a procedure instead of a function?

amit_chauhan

No, it could be a function or a procedure. What is the name of the function that you have created in the database ? Is it standalone function or it under some package ?
Replace my_pack.get_all_users with the actual package and function name.

Thanks
Amit

danceswithwolves

ASKER

cs = c.prepareCall ("{ ? = call WEBQUERY.get_all_users ) }");

Is there a command I can type in PL/SQL to verify that it exists?

amit_chauhan

On sql prompt in sqlplus, type this:
SQL>DESC WEBQUERY

danceswithwolves

ASKER

SQL> desc webquery;
FUNCTION GET_ALL_USERS RETURNS REF CURSOR

SQL>

Do I have to have the get_all_users in caps?

danceswithwolves

ASKER

Yes i think I did have to put it all in caps..

amit_chauhan

No, its case insensitive.

ASKER CERTIFIED SOLUTION

amit_chauhan

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

danceswithwolves

ASKER

Yet somehow it worked perhaps when I retyped the function in caps I retyped over a mistake. Here is the final statement I wrote:

cs = c.prepareCall ("{ ? = call WEBQUERY.GET_ALL_USERS () }");

Thank you so much for you help you saved soooo much time!!!!

amit_chauhan

You welcome.

Thanks
Amit