Hi,



I am using The Oracle OLEDB provider for retieving data from a Stored 

procedure. I am having problems while accessing the Ref Cursor of type

RECORD. The code works fine if the Ref cursor is not of type RECORD.

But it fails if the Ref cursor is of type RECORD



Webserver : IIS

OS : NT, op pack 4.

Database : Oracle 8.1.6

Provider : Oracle OLEDB provider(8.1.7)





The code is as below::

=======================================================================





The PL/SQL scripts are as follows :

-----------------------------------------------------------------------



CREATE OR REPLACE PACKAGE test_package AS



TYPE RT1 IS RECORD (

	LABEL		 TEMP_ELEMENT_LIST.LABEL%TYPE,

	VALUE		 TEMP_ELEMENT_LIST.VALUE%TYPE

	);

TYPE test_cursor IS REF CURSOR RETURN RT1;

TYPE ok_cursor is REF CURSOR;

END;



CREATE TABLE TEST ( 

  ITEM_ID  NUMBER (10), 

  LABEL    VARCHAR2 (50), 

  VALUE    LONG, 

  SEQ      NUMBER (10)

);





create or replace PROCEDURE TEST_BY_RAVI_REF

 (

  nameIn    IN   varchar2,

  cout 		OUT   test_package.test_cursor

 )

  AS

 begin

  	  OPEN cout FOR  

		SELECT LABEL, VALUE FROM TEST

			WHERE label=TEST_BY_RAVI_REF_OK.nameIn ORDER BY 

SEQ;

	  EXCEPTION  

	     WHEN OTHERS THEN  

		 	null;

END;





create or replace PROCEDURE TEST_BY_RAVI_REF_OK

 (

  nameIn    IN   varchar2,

  cout 		OUT   test_packag.ok_cursor

 )

  AS

 begin

  	  OPEN cout FOR  

      	   SELECT * FROM TEST where label=TEST_BY_RAVI_REF_OK.nameIn; 

 	 EXCEPTION  

	     WHEN OTHERS THEN  

		 	null;

END;



===========================================================================

==========





The ASP code is as below :

------------------------------





<!--#include file="adovbs_r.asp"-->

<%

set con = Server.CreateObject("ADODB.Connection")

set rs = Server.CreateObject("ADODB.recordset")

set cmd = Server.CreateObject("ADODB.Command")



con.open "Provider=OraOLEDB.Oracle;Data Source=<Database Name>;User 

ID=<userName>;Password=<passWord>;PLSQLRSet=1"

con.begintrans



'This code works Fine

'=====================

' With cmd

'  Set .ActiveConnection = con

'   .CommandType = adCmdText

'   .CommandText = "{call TEST_BY_RAVI_REF_OK(?)}"

'    Set objNameParam1 = .CreateParameter("nameIn", adBSTR , adParamInput, 

50, "Alex")

'   .Parameters.Append objNameParam1

' End With





'This code where the Refcursor is of type RECORD, the ASP fails

'===============================================================



With cmd

  Set .ActiveConnection = con



 	  .CommandType = adCmdText

      .CommandText = "{call TEST_BY_RAVI_REF(?)}"

       Set objNameParam1 = .CreateParameter("nameIn", adBSTR , 

adParamInput, 50, "Alex")

      .Parameters.Append objNameParam1

End With





set rs = cmd.Execute



con.CommitTrans

do while not rs.eof

response.write(rs.fields(0) & "<BR>")

rs.movenext

loop



'--------End of ASP code



%>





The error message is :

=======================



OraOLEDB error '80040e21' 

Errors occurred 









===========================================================================



Please send your suggestions comments.



Ravi