call stored procedure from java

I am new to working with java against the DB.

The DBA wrote the following stored procedure:

  config_pkg.get_app_config_xml(i_app_name => :i_app_name,
                                o_xml => :o_xml);

         i_app_name defined as String
         o_xml defined as Cursor

and I want to write a simple java code which run this procedure.
The procedure should get A string and return XML.

Assume I have already a connection to the db how will be the rest of code:

public static void main(String args[])
              Connection conn = getConnection(...);
             //WHAT SHOULD I DO HERE

Who is Participating?
tapasviConnect With a Mentor Commented:
sorry ..made one mistake here

should be 3 output params (as there are four question marks)

//3 output param are as follows
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.CHAR);
cs.registerOutParameter(4, Types.CHAR);

dannyshAuthor Commented:
BTW what is Cursor type ?
Please decipher that for those of use who don't understand the syntax ;-) You could, for instance have an IN parameter that is a String and an OUT parameter that is a Cursor, so you can iterate through a result.
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

This should answer your question -

Its basically this -

    int age = 39;
    String poetName = "dylan thomas";
    CallableStatement proc =
        connection.prepareCall("{ call set_death_age(?, ?) }");
    proc.setString(1, poetName);
    proc.setInt(2, age);
catch (SQLException e)
    // ....

but the exact format of the call may vary depending on your DB.

A cursor is a reference to the current row of the result set.

If you need anymore info, what DB are you using - this will help answer your q ;)
Following code will help you:

but to use this code you need to make following changes:
call <stored proc name>(?,?,?,?)

insert your stored proc name
and number of "?" should be equls to the sum of input parameters + output parameters

my example assumes one input parameter and 3 output parameters
(return values from stored proc)

import following things:

import java.sql.Connection;
import java.sql.CallableStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Types;


Connection con = null;
CallableStatement cs = null;
ResultSet rs = null;
con = DBConnection.getConnection();
cs = con.prepareCall("{ call <stored proc name>(?,?,?,?)}");
cs.setInt(1, Integer.parseInt("123")); //Input parameter one

//2 output param are as follows
cs.registerOutParameter(2, Types.CHAR);
cs.registerOutParameter(3, Types.CHAR);

rs = cs.executeQuery();

hope this helps
dannyshAuthor Commented:
Hi Comment
First I want to thank you for your help.

The DB I am working against is Oracle.
Can you tell me please who do I register to Cursor output ?

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.