• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

call stored procedure from java

Hi
I am new to working with java against the DB.

The DBA wrote the following stored procedure:

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


remarks:
         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
}




0
dannysh
Asked:
dannysh
1 Solution
 
dannyshAuthor Commented:
BTW what is Cursor type ?
0
 
CEHJCommented:
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.
0
 
matt_lawCommented:
This should answer your question -

http://www.onjava.com/pub/a/onjava/2003/08/13/stored_procedures.html

Its basically this -

try
{
    int age = 39;
    String poetName = "dylan thomas";
    CallableStatement proc =
        connection.prepareCall("{ call set_death_age(?, ?) }");
    proc.setString(1, poetName);
    proc.setInt(2, age);
    cs.execute();
}
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 ;)
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
tapasviCommented:
Following code will help you:

but to use this code you need to make following changes:
in
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;
try
{
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
tapasvi
0
 
tapasviCommented:
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);

0
 
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 ?

   10x
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now