Win a copy of Mesos in Action this week in the Cloud/Virtualizaton forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

How to obtain a resultset using a stored procedure

 
Ashish Kothari
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 1
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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:

should be replaced with:

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
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 1141
1
Java Oracle
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Greenhorn
Posts: 19
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
Ranch Hand
Posts: 37
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Thank you Surendran,Actually I didn't read your last message.
It would be lot helping.regards.
Ashish
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic