?
Solved

Calling a PL/SQL stored procedure from Java

Posted on 2003-03-26
22
Medium Priority
?
449 Views
Last Modified: 2012-06-27
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
0
Comment
Question by:danceswithwolves
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 11
  • 10
22 Comments
 
LVL 35

Expert Comment

by:girionis
ID: 8212887
 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.
0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8214439
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
0
 

Author Comment

by:danceswithwolves
ID: 8219519
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?
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 

Author Comment

by:danceswithwolves
ID: 8219570
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?
0
 

Author Comment

by:danceswithwolves
ID: 8219731
nevermind I just realized that I was getting the errors because the USERS table didn't exist in my DB :)
0
 
LVL 2

Expert Comment

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

Hope that helps
Thanks
Amit
0
 

Author Comment

by:danceswithwolves
ID: 8226772
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.

0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227005
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

0
 

Author Comment

by:danceswithwolves
ID: 8227129
It worked, but now it crashed on the
 

     cs.execute ();

// line

0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227150
Whats the exception trace ure getting ? Can u print that .


Amit
0
 

Author Comment

by:danceswithwolves
ID: 8227338
SQLException: ORA-00900: invalid SQL statement
0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227370
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
0
 

Author Comment

by:danceswithwolves
ID: 8227403
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?
0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227422
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
0
 

Author Comment

by:danceswithwolves
ID: 8227460
cs = c.prepareCall ("{ ? = call WEBQUERY.get_all_users ) }");

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

0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227472
On sql prompt in sqlplus, type this:
SQL>DESC WEBQUERY

0
 

Author Comment

by:danceswithwolves
ID: 8227490
SQL> desc webquery;
FUNCTION GET_ALL_USERS RETURNS REF CURSOR

SQL>

Do I have to have the get_all_users in caps?

0
 

Author Comment

by:danceswithwolves
ID: 8227501
Yes i think I did have to put it all in caps..
0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227503
No, its case insensitive.
0
 
LVL 2

Accepted Solution

by:
amit_chauhan earned 600 total points
ID: 8227508
Unless you created the function my putting " (double quote) around the function name.

0
 

Author Comment

by:danceswithwolves
ID: 8227524
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!!!!

0
 
LVL 2

Expert Comment

by:amit_chauhan
ID: 8227539
You welcome.

Thanks
Amit
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

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

By the end of 1980s, object oriented programming using languages like C++, Simula69 and ObjectPascal gained momentum. It looked like programmers finally found the perfect language. C++ successfully combined the object oriented principles of Simula w…
Introduction This article is the last of three articles that explain why and how the Experts Exchange QA Team does test automation for our web site. This article covers our test design approach and then goes through a simple test case example, how …
Viewers learn about the scanner class in this video and are introduced to receiving user input for their programs. Additionally, objects, conditional statements, and loops are used to help reinforce the concepts. Introduce Scanner class: Importing…
This theoretical tutorial explains exceptions, reasons for exceptions, different categories of exception and exception hierarchy.
Suggested Courses
Course of the Month8 days, 6 hours left to enroll

765 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