Win a copy of Functional Reactive Programming this week in the Other Languages forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

how to get out parameter from store procedure?

 
Mindy Wu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Posts: 65
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
Mindy Wu
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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).]
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic