Re: MSDAORA and stored procedure

Try putting a return clause at the end of your ref cursor, ie use a strong cursor type as opposed to a weak cursor type.

I recently modified some code to use a weak cursor type as opposed to a strong cursor type and it stopped working. As soon as I reverted back to a strong type the procedures worked again.

Both these calls worked when executed from sqlolus.

Its also imporant that you use the correct driver: MDAC2.5 for Oracle. 2.1 does not work properly with Oracle.

>
>create or replace package atp_test
>
>as
>
>type generic_cursor is ref cursor ;
>
>procedure procedure1( result in out generic_cursor ) ;
>
>end atp_test
>
>;
>
>/
>
>create or replace package body atp_test
>
>as
>
>procedure procedure1( result in out generic_cursor )
>
>is
>
>begin
>
>open result for select * from table1 ;
>
>end procedure1;
>
>end atp_test
>
>;
>
>/
>
>-----------------------
>
>client code:
>
>-----------------------
>
>CCommand<CDynamicAccessor> cmd ;
>
>m_dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false);
>
>m_dbinit.AddProperty(DBPROP_AUTH_USERID, bstrUserName );
>
>m_dbinit.AddProperty(DBPROP_AUTH_PASSWORD, bstrPassword ) ;
>
>m_dbinit.AddProperty(DBPROP_INIT_DATASOURCE, bstrServerName );
>
>m_dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033);
>
>m_dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4);
>
>if ( FAILED( hr = m_db.OpenWithServiceComponents(
>m_bstrProvider,&m_dbinit ) ) )
>------
>m_session.Close() ;
>
>hr = m_session.Open( m_db ) ;
>
>if( FAILED( hr = cmd.Create( m_database.GetCurrentSession(), L"{ CALL
>atp_test.procedure1 }" ) ) )
>-----
>if( FAILED( hr = cmd.Prepare() ) )
>-----
>if( FAILED( hr = cmd.Open( NULL, NULL, true ) ) )
>-----
>m_database.CloseConnection( contextId ) ;
>
>for( hr = cmd.MoveFirst(); hr == S_OK ; hr = cmd.MoveNext() )
>
>{
>
>int column1 = *(int *)cmd.GetValue(1L) ;
>
>int column2 = *(int *)cmd.GetValue(2L) ;
>
>CComBSTR column3 = (char *)cmd.GetValue(3L) ;
>----
>
>}
>
>
>Sent via Deja.com http://www.deja.com/
>Before you buy.
Received on Wed Sep 20 2000 - 00:00:00 CDT