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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 467
  • Last Modified:

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
0
danceswithwolves
Asked:
danceswithwolves
  • 11
  • 10
1 Solution
 
girionisCommented:
 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
 
amit_chauhanCommented:
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
 
danceswithwolvesAuthor Commented:
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.

 
danceswithwolvesAuthor Commented:
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
 
danceswithwolvesAuthor Commented:
nevermind I just realized that I was getting the errors because the USERS table didn't exist in my DB :)
0
 
amit_chauhanCommented:
Exactly :) That was just an example, replace the sql with your exact table/columns

Hope that helps
Thanks
Amit
0
 
danceswithwolvesAuthor Commented:
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
 
amit_chauhanCommented:
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
 
danceswithwolvesAuthor Commented:
It worked, but now it crashed on the
 

     cs.execute ();

// line

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


Amit
0
 
danceswithwolvesAuthor Commented:
SQLException: ORA-00900: invalid SQL statement
0
 
amit_chauhanCommented:
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
 
danceswithwolvesAuthor Commented:
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
 
amit_chauhanCommented:
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
 
danceswithwolvesAuthor Commented:
cs = c.prepareCall ("{ ? = call WEBQUERY.get_all_users ) }");

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

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

0
 
danceswithwolvesAuthor Commented:
SQL> desc webquery;
FUNCTION GET_ALL_USERS RETURNS REF CURSOR

SQL>

Do I have to have the get_all_users in caps?

0
 
danceswithwolvesAuthor Commented:
Yes i think I did have to put it all in caps..
0
 
amit_chauhanCommented:
No, its case insensitive.
0
 
amit_chauhanCommented:
Unless you created the function my putting " (double quote) around the function name.

0
 
danceswithwolvesAuthor Commented:
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
 
amit_chauhanCommented:
You welcome.

Thanks
Amit
0

Featured Post

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

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