How can we call a stored procedure from java file

[kiranbabu chinthaa]

How can we call a stored procedure from java file.
I have a stored procedure in Oracle 10G, which is returning the data in a cursor, i need to fetch the data in the cursor and display the result in the java file,
can anybody help me out to give the complete procedure with steps to accomplish the same.
Thanks in advance.

[Ulf Dittmer]

Here's what a search through the JDBC forum finds for the term "calling oracle stored procedure".

[Vicki Amin]

Try this

  

CallableStatement cs;
    try {
      // Call a procedure with no parameters
        cs = connection.prepareCall("{call myproc}");
        cs.execute();
    
      // Call a procedure with one IN parameter
        cs = connection.prepareCall("{call myprocin(?)}");
    
        // Set the value for the IN parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure
        cs.execute();
    
      // Call a procedure with one OUT parameter
        cs = connection.prepareCall("{call myprocout(?)}");
    
        // Register the type of the OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Execute the stored procedure and retrieve the OUT value
        cs.execute();
        String outParam = cs.getString(1);     // OUT parameter
    
      // Call a procedure with one IN/OUT parameter
        cs = connection.prepareCall("{call myprocinout(?)}");
    
        // Register the type of the IN/OUT parameter
        cs.registerOutParameter(1, Types.VARCHAR);
    
        // Set the value for the IN/OUT parameter
        cs.setString(1, "a string");
    
        // Execute the stored procedure and retrieve the IN/OUT value
        cs.execute();
        outParam = cs.getString(1);            // OUT parameter
    } catch (SQLException e) {
    }

[kiranbabu chinthaa]

Thanks for your reply Vicki, and coming to my requirement, i have one input parameter of type String and one output parameter of type CURSOR, could you please give me the complete steps to call that stored procedure from java code and fetch the data that is returned from the stored procedure through the cursor and getting each and every column from that returned cursor.

Thanks in advance....

[Vicki Amin]

    
String query = "begin ? := storedc_procname(?); end;";

// register the type of the out param - an Oracle specific type
stmt.registerOutParameter(1, OracleTypes.CURSOR);

// set the in param
stmt.setString(2, <stringparameter>);

// execute and retrieve the result set
stmt.execute();
ResultSet rs = (ResultSet)stmt.getObject(1);

// print the results
while (rs.next()) 
{
 
     // Assuming 1st return parm is a String, 2nd is a Float and 3rd is a Date
     System.out.println(rs.getString(1) + "\t" + rs.getFloat(2) + "\t" + rs.getDate(3).toString());
}

[kiranbabu chinthaa]

Thank you vicki, i hope this will work fine for me.