If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

 
Go Back  dBforums > Database Server Software > Oracle > Trouble Calling Stored Procedures in Java

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Atlanta, GA
Posts: 2
Thumbs up Trouble Calling Stored Procedures in Java

Hello All!

I am working withe Java/Oracle 9i and I am trying to call a Stored Procedue from Oracle. I have all the components set up , however when I register my output var, I keep getting this complie error

FP21M.java:7550: cannot resolve symbol
symbol : variable BOOLEAN
location: class java.sql.Types cs.registerOutParameter(1,java.sql.Types.BOOLEAN);


Here is the code that I am trying to run

try
{
// Call a procedure with one OUT parameter
Statement stmt = conn.createStatement();
CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(" + rc_14 + ")}");

// Register the type of the OUT parameter
cs.registerOutParameter(1, java.sql.Types.BOOLEAN);
// Execute the stored procedure and retrieve the OUT value
cs.execute();

String outParam = cs.getString(1); // OUT parameter

System.out.println("outParam value >" + outParam + "<");

conn.close();
}
catch (java.lang.Exception ex)
{
ex.printStackTrace ();
}

Anyone out there run into this error before??

I am running out of ideas...


Thanks!

Craig Butler
Reply With Quote
  #2 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 18
Re: Trouble Calling Stored Procedures in Java

Quote:
Originally posted by scbutler1
Hello All!

I am working withe Java/Oracle 9i and I am trying to call a Stored Procedue from Oracle. I have all the components set up , however when I register my output var, I keep getting this complie error

FP21M.java:7550: cannot resolve symbol
symbol : variable BOOLEAN
location: class java.sql.Types cs.registerOutParameter(1,java.sql.Types.BOOLEAN);


Here is the code that I am trying to run

try
{
// Call a procedure with one OUT parameter
Statement stmt = conn.createStatement();
CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(" + rc_14 + ")}");

// Register the type of the OUT parameter
cs.registerOutParameter(1, java.sql.Types.BOOLEAN);
// Execute the stored procedure and retrieve the OUT value
cs.execute();

String outParam = cs.getString(1); // OUT parameter

System.out.println("outParam value >" + outParam + "<");

conn.close();
}
catch (java.lang.Exception ex)
{
ex.printStackTrace ();
}

Anyone out there run into this error before??

I am running out of ideas...


Thanks!

Craig Butler
When you are calling the procedure you should call it with a bind variable as follows:
CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ;
Reply With Quote
  #3 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Location: Atlanta, GA
Posts: 2
Re: Trouble Calling Stored Procedures in Java

Ok, I can do that.

But here is my next question, how do you pass the needed parameters to it? The rc_14 used in the call is a string with all the parameters in it.

This is the part that confuses me.

Thanks for the reply!
Reply With Quote
  #4 (permalink)  
Old
Registered User
 
Join Date: Feb 2004
Posts: 18
Re: Trouble Calling Stored Procedures in Java

Quote:
Originally posted by muffi
When you are calling the procedure you should call it with a bind variable as follows:
CallableStatement cs = conn.prepareCall("{call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ;
I think a more suitale forum for your pisr would be the Java Forum. While calling a stored proceudure you pass the input parameters just as in a Prepared Statement. You can refer to the java.sun.com site for the api reference. If you are calling the statement in the following manner:
conn.prepareCall("{? = call PKG_UPDATE_ISSUE.P_UPDATE_ISSUE_DETAIL_SALES(?)}") ; then you need to register the out parameter which is the first ?. Is it a stored procedure or a stored function that you are calling.
Reply With Quote
  #5 (permalink)  
Old
Registered User
 
Join Date: Jan 2003
Posts: 10
Calling StoredProcedure in Java Code

hi,

You can call the stored procedure with the CallableStatement class. This class is the subclass of the PreparedStatement.

Step 1: Call the stored procedure by Callable Statement.

CallableStatement proc =
connection.prepareCall("{ call empNotification(?, ?) }");

The string passed to the prepareCall method is the procedure call specification. It specifies the name of the procedure to call and a ? for each parameter you need to specify.

Step 2: Set the input parameters

proc.setString(1, empName);
proc.setInt(2, age);

The empName and age is declared in the class.

Step 3: call the execute statement
proc.execute();

Here is the complete snippet.

Connection con = null;
CallableStatement proc = null;

try
{
con = connectionPool.getConnection();
proc = con.prepareCall("{ call empNotification(?, ?) }");
proc.setString(1, empName);
proc.setInt(2, age);
proc.execute();
}
finally
{
try
{
proc.close();
}
catch (SQLException e) {}
con.close();
}


If your stored procedure returns a value then it must be registered with the registerOutParameter method.

Hope this helps!

Regards
Babitha
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On