How to obtain a resultset using a stored procedure

[Ashish Kothari]

my problem is that I am firing a select query in stored procedure .
I want to call this stored procedure in jsp page or bean and want to store select query output in a resultset .
Though we can use callable to call stored procedure but I don't know how to get resultset using CallableStatement.
please help

[Paul Christopher]

Use the following Cursor declaration in Oracle to retrieve the resultset

CREATE OR REPLACE PACKAGE P_Sample_Package
AS
TYPE generic_cursor_type IS REF CURSOR;
END;
/
CREATE OR REPLACE PROCEDURE P_Sample_Procedure (c1 OUT P_Sample_Package.generic_cursor_type)
AS
BEGIN
OPEN c1 FOR SELECT val_id, val_cd FROM TBPVAL_LKUP;
END;
/
and the following Java Statament to loop thru the Resultset
CallableStatement_Ex obj = new CallableStatement_Ex();
oracle.jdbc.pool.OracleDataSource ods;
ods = new oracle.jdbc.pool.OracleDataSource();
ods.setURL("jdbc racle:thin:@url:1521:SID");
ods.setUser("userid");
ods.setPassword("password");
Connection c = ods.getConnection();
CallableStatement cs = c.prepareCall("call P_Sample_Procedure (?)");
cs.registerOutParameter(1, OracleTypes.CURSOR);
cs.execute();
int i=0;
ResultSet rs = (ResultSet) cs.getObject(1);
while(rs.next())
{
System.out.println(rs.getString("val_id"));
System.out.println(rs.getString("val_cd"));
}
}
}
hope this will be useful.

[Avi Abrami]

Paul,
Pardon me, but I saw no mention in Ashish's post regarding the DBMS (s)he is using.
As far as I know, Oracle is not the only DBMS that supports stored procedures.
Also, Paul, your code looks incorrect to me (although I have not tested it -- did you?). Oracle does support the JDBC escape syntax, so I think that this line of your code:

CallableStatement cs = c.prepareCall("call P_Sample_Procedure (?)");

should be replaced with:

CallableStatement cs = c.prepareCall("{call P_Sample_Procedure(?)}");

In any case, Ashish, a (correct) version of the answer Paul has given you can be found at Oracle's Ask Tom Web site, at this Web page.
Good Luck,
Avi.

[Surendran Velath]

Hi Ashish,
Use the following code for any number of result sets returned from the procedure:
(Each select statement returns one resultset)
private Connection conn;
private CallableStatement cstt;
private ResultSet rs;
try
{
Class.forName( driver ).newInstance();
conn = DriverManager.getConnection(URL , user, pswd );
cstt = conn.prepareCall( "{call testproc()}" );
cstt.execute();
try
{
// cstt.getObject( 1 ); // for out params
rs = cstt.getResultSet();
ResultSetMetaData rsmd = rs.getMetaData();
int cols = rsmd.getColumnCount();
while(rs.next())
{
for(int i=0; i<cols; i++)
System.out.print(rs.getString(i+1)+", ");
System.out.println();
}
while(cstt.getMoreResults())
{
System.out.println( " Next:");
rs = cstt.getResultSet();
rsmd = rs.getMetaData();
cols = rsmd.getColumnCount();
while(rs.next())
{
for(int i=0; i<cols; i++)
System.out.print(rs.getString(i+1)+", ");
System.out.println();
}
}
}
catch(SQLException sqe)
{
System.out.println( " No value got from stored procedure" );
sqe.printStackTrace ();
}
}
catch( Throwable e )
{
e.printStackTrace();
}

[Ashish Kothari]

I thankful to you all.
Avi,I want to ask you a question ,suppose (SQL Server is the database or it can be any ,but in my case it is SQL-Server) i am firing more then one Select statement.
Then How would I proceed,suppose I want to proceed with the third select statement of Stored procedure and don't want to operate on first two.
please help

[Avi Abrami]

Ashish,
Sorry, but I don't use SQL Server. In any case, your question is unclear to me. Are you trying to execute only certain sections of your stored procedure?
Avi.

[Ashish Kothari]

Avi,
I have more then one select statement in stored procedure ,but I want deal with one of them only ,so I how would I pick resultset of that specific select statement.That is all I want to know.
thanks and regards.
Ashish

[Surendran Velath]

If you know it is the third procedure for sure then you can ignore the first two after fetching the ResultSets.
If not, then use a condition that is returned by your ResultSet to make sure it is the one you want.
Any way you will get the ResultSet in the order of appearance of SELECT statement inside the Procedure

[Ashish Kothari]

Thank you Surendran,Actually I didn't read your last message.
It would be lot helping.regards.
Ashish