wood burning stoves 2.0*
The moose likes JDBC and the fly likes how to get out parameter from store procedure? Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "how to get out parameter from store procedure?" Watch "how to get out parameter from store procedure?" New topic
Author

how to get out parameter from store procedure?

Mindy Wu
Ranch Hand

Joined: Jan 12, 2001
Posts: 121
Hello all,
I have a problem while trying to call the store procedure and return the out parameter:
Here is my procedure:
Create or replace procedure check_date(intNum IN number,
outDATE OUT DATE) is
begin
select sysdate into outDATE from dual;
end;
this code execute successfully in pl/sql.
My java code as follow:
public class StoreProcedure2
{
public static void main(String[] args)
{
Connection conTest;
ResultSet rsTest = null;
CallableStatement cstmt = null;
conTest = DBConnect.getConnection();
int intTest = 12;
try
{
cstmt = conTest.prepareCall("{ call check_date(? ?)}");
System.out.println("after prepreCall");
cstmt.setInt(1, intTest);
System.out.println("after setInt=" + intTest);
cstmt.registerOutParameter(2, Types.DATE);
System.out.println("after register");
cstmt.execute();
System.out.println("after execute");
System.out.println("give me the out parameter: " + cstmt.getDate(2));
cstmt.close();
}
catch(Exception error)
{
System.err.println("exception:" + error.getMessage());
}
}
}

//****************
Here are the error msg I got when run it in java:
exception:ORA-06550: line 1, column 21:
PLS-00103: Encountered the symbol "" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using is null is not | | indicator is dangling
The symbol "," was substituted for "" to continue.

I really need help on this! I will be very appreciate your help!
Thanks very much!
Mindy
Buzz
Greenhorn

Joined: Jun 06, 2001
Posts: 1
what you got is a ResultSet, not a single output parameter
you should use
getResultSet() in CallableStatement or change the stored procedure to use set outDate= something
Originally posted by Mindy Wu:
Hello all,
I have a problem while trying to call the store procedure and return the out parameter:
Here is my procedure:
Create or replace procedure check_date(intNum IN number,
outDATE OUT DATE) is
begin
select sysdate into outDATE from dual;
end;
this code execute successfully in pl/sql.
My java code as follow:
public class StoreProcedure2
{
public static void main(String[] args)
{
Connection conTest;
ResultSet rsTest = null;
CallableStatement cstmt = null;
conTest = DBConnect.getConnection();
int intTest = 12;
try
{
cstmt = conTest.prepareCall("{ call check_date(? ?)}");
System.out.println("after prepreCall");
cstmt.setInt(1, intTest);
System.out.println("after setInt=" + intTest);
cstmt.registerOutParameter(2, Types.DATE);
System.out.println("after register");
cstmt.execute();
System.out.println("after execute");
System.out.println("give me the out parameter: " + cstmt.getDate(2));
cstmt.close();
}
catch(Exception error)
{
System.err.println("exception:" + error.getMessage());
}
}
}

//****************
Here are the error msg I got when run it in java:
exception:ORA-06550: line 1, column 21:
PLS-00103: Encountered the symbol "" when expecting one of the following:
. ( ) , * @ % & | = - + < / > at in mod not range rem => ..
<an exponent (**)> <> or != or ~= >= <= <> and or like as
between from using is null is not | | indicator is dangling
The symbol "," was substituted for "" to continue.

I really need help on this! I will be very appreciate your help!
Thanks very much!
Mindy

Mindy Wu
Ranch Hand

Joined: Jan 12, 2001
Posts: 121
Thanks Buzz!
However, it is still not work and I still getting the same message:
I have revised my code as follow:
public class StoreProcedure2
{
public static void main(String[] args)
{
Connection conTest;
ResultSet rsTest = null;
CallableStatement cstmt = null;
conTest = DBConnect.getConnection();
int intTest = 12;
try
{
cstmt = conTest.prepareCall( "{ call check_date(? ?)}");
System.out.println("after prepreCall");
cstmt.setInt(1, intTest);
System.out.println("after set string1 =" + intTest);
cstmt.registerOutParameter(2, java.sql.Types.DATE);
System.out.println("after register");
System.out.println( "Execute: " + (rsTest = cstmt.executeQuery()));
System.out.println("after execute");
while (rsTest.next())
{
System.out.println("give me the out parameter: " + rsTest.getDate(2));
}
cstmt.close();
rsTest.close();
}
catch(Exception error)
{
System.err.println("exception:" + error.getMessage());
}
}
}
Can provide me with more help please? Thank you!
Bjarki Holm
Author
Ranch Hand

Joined: May 25, 2001
Posts: 65
Dear Mindy,
since you are using PL/SQL, you might as well use the direct syntax for calling PL/SQL procedures. Also, you were right in the first place not using a <code>ResultSet</code> for the <code>OUT</code> parameter. Instead, you should call <code>cstmt.execute()</code>, and then <code>cstmt.getDate(2)</code>, as shown in the following example:
<code>
public class StoreProcedure {
    
    public static void main(String[] args) {
        
        Connection conn = null;
        CallableStatement cstmt = null;
        Date result;
        conn = DBConnect.getConnection();
        int test = 12;
        try {
        
            cstmt = conn.prepareCall("BEGIN check_date(?,?); END;");
            cstmt.setInt(1, test);
            cstmt.registerOutParameter(2, java.sql.Types.DATE);
            cstmt.execute();
            result = csmt.getDate(2);
            cstmt.close();
            
        } catch(Exception error) {
            System.err.println("exception:" + error.getMessage());
        } finally {
            if (conn != null) {
                conn.close();
            }
        }
    }
}
</code>
Hope this helps!

[This message has been edited by Bjarki Holm (edited June 09, 2001).]


Bjarki Holm
Mindy Wu
Ranch Hand

Joined: Jan 12, 2001
Posts: 121
Thank you all! I fixed my problem now, the error is i missed a comma in between the question mark.
Thanks all once again!
Mindy
[This message has been edited by Mindy Wu (edited June 09, 2001).]
 
Consider Paul's rocket mass heater.
 
subject: how to get out parameter from store procedure?