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

How would you do that in VB, using OLE DB provider w/ADO?

billmil_at_my-deja.com wrote in message <8felim$8rj$1_at_nnrp1.deja.com>...
>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