Re: How to return a result set from a stored proc?

From: <billmil_at_my-deja.com>
Date: 2000/05/11
Message-ID: <8felim$8rj$1@nnrp1.deja.com>#1/1

To get a result set from an SQL statement you can either call a select statement directly or return a cursor variable from a PLSQL stored procedure.

Here's how to call it directly: private ResultSet getEditorResultSet(Statement stmt) throws SQLException { String SQL = "select USER_ID, LAST_NAME, HANDLE, EMAIL from USERS where PRIVILEGE_LIST like '%topicedit%'"; return stmt.executeQuery(SQL); }

Here's how to call using a stored procedure (Note: it's important to close both the statement and the result set in the finally section. If you don't close the result set you can end up with a maximum-open-cursors error): /** get all questions whose staging is not closed @param db database connection @return a vector of Question objects */ public static Vector getOpenQuestions(Connection db) throws SQLException { String query = "begin :1 := pkg_questions.get_open(); end; " ; CallableStatement stmt =

null;  ResultSet rs = null;    try {	 stmt = db.prepareCall(query);	     
   rs = DBUtil.getResultSet(stmt);	   return getVector(rs);	 }   
   finally {	     DBUtil.closeResultSet(rs);     

DBUtil.closeStatement(stmt); } }

We've created a separate "getResultSet" method to hide the Oracle-specific reference. Here's this method:

    public static ResultSet getResultSet(CallableStatement stmt)

        throws SQLException
    {

	stmt.registerOutParameter(1, oracle.jdbc.driver.OracleTypes.CURSOR);
	stmt.execute();
	ResultSet rs = (ResultSet) stmt.getObject(1);
	return rs;

    }

Finally, here's the plsql procedure which returns the cursor variable:

	FUNCTION  get_open
	   	RETURN cur_question_type
	IS
	  cur_question cur_question_type;

/* cur-question-type is declared as follows in the package header: */ /* TYPE cur_question_type IS REF CURSOR RETURN QUESTIONS%ROWTYPE; */

	BEGIN
		OPEN cur_question FOR
			 SELECT * FROM QUESTIONS WHERE
			 		staging_level = Pkg_Const.c_staging_level_closed;
		RETURN cur_question;
	EXCEPTION
		WHEN OTHERS THEN Pkg_Err.handle(SQLCODE);
	END;

If anyone knows any better ways or sees any performance problems, please comment.

bill milbratz
chicago IL usa

In article <8fega1$2i9$1_at_nnrp1.deja.com>,   Alexander Jerusalem <ajeru_at_my-deja.com> wrote:
> Hi,
>
> The subject basically says it all. I switched to Oracle from SQL-
> Server. In SQL-Server I could return a result set to the calling
> program from a stored procedure just by executing a sql statement. The
> same doesn't work in Oracle though. Following is my guess, which is not
> working:
>
> create or replace procedure (id in number) as
> begin
> select a, b, c from test where testId = id;
> end;
>
> Any hint would be very much appreciated.
>
> --
> Alexander Jerusalem
> VKN
> ajeru_at_gmx.net
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Thu May 11 2000 - 00:00:00 CDT