Forums Register Login

how to get out parameter from store procedure?

+Pie Number of slices to send: Send
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
+Pie Number of slices to send: Send
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


+Pie Number of slices to send: Send
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!
+Pie Number of slices to send: Send
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).]
+Pie Number of slices to send: Send
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).]
Destroy anything that stands in your way. Except this tiny ad:
a bit of art, as a gift, the permaculture playing cards
https://gardener-gift.com


reply
reply
This thread has been viewed 4785 times.
Similar Threads
Stored procedure which returns array
How to call java stored procedure
Problem in executing Oracle Function from JSP Page
plssssss Help me!!
ORA-06550: line 1, column 36: PLS-00103: Encountered the symbol ";"
More...

All times above are in ranch (not your local) time.
The current ranch time is
Mar 28, 2024 20:03:28.