• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Ron McLeod
  • Paul Clapham
  • Jeanne Boyarsky
  • Liutauras Vilda
Sheriffs:
  • Rob Spoor
  • Bear Bibeault
  • Tim Cooke
Saloon Keepers:
  • Tim Moores
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Piet Souris
Bartenders:
  • Frits Walraven
  • Himai Minh

how to get out parameter from store procedure?

 
Ranch Hand
Posts: 121
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
 
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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!
 
Author
Posts: 65
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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).]
 
Don't get me started about those stupid light bulbs.
reply
    Bookmark Topic Watch Topic
  • New Topic