Unsupported Oracle Datatype - Ref Cursor dont work !

[Mike]

Hi Folks,

I'm actually encountering a known problem, well i've already seen a
few posts, concerning the call of a stored proc. with a ref cursor as
output parameter. Although i've tried, everything (i think) it still
doesnt work by me and i really dont know why.
Well here's first my proc, included inside a package :

Package pa_xxx
type ty_ref_cur is ref cursor;

procedure pr_xxx
(mp_userid in t_user.userid%type,
mp_pers_id in t_person.personid%type,
cu_data out ty_ref_cur
)
is
begin
open cu_data for
select col1, col2 from t_xxx where userid = mp_userid and
persid = mp_pers_id;
...
end pr_xxx;

Then, from a client application, i've got a class where i try to call
this proc as follows :

public DataSet RunProc(string p_ProcName, int UserId, int PersId)
{
DataSet RowSet = new DataSet();
OracleCommand cmd = new OracleComman(p_ProcName,m_conn);
cmd.CommandType = CommandType.StoredProcedure;

OracleParameter param1 = new OracleParameter();
param1.ParameterName = "mp_userid";
param1.OracleType = OracleType.Number;
param1.Direction = ParameterDirection.Input;
param1.Value = UserId;
cmd.Parameters.Add(param1);

OracleParameter param2 = new OracleParameter();
param2.ParameterName = "mp_persid";
param2.OracleType = OracleType.Number;
param2.Direction = ParameterDirection.Input;
param2.Value = PersId;
cmd.Parameters.Add(param2);

OracleParameter param3 = new OracleParameter();
param3.ParameterName = "cu_data";
param3.OracleType = OracleType.Cursor;
param3.Direction = ParameterDirection.Output;
cmd.Parameters.Add(param3);


OpenConn();
OracleDataAdapter adpt = new OracleDataAdapter(cmd);


adpt.Fill(RowSet);
return RowSet;
}

So, everytime it reaches the statement "adpt.Fill", it fails returning
the message "unsupported oracle datatype encountered user defined..."
It seems that it doesnt recognize the datatype ty_refcur from my proc.
and i dont find why. I've added correctly a reference to the oracle
client. What more could i do ?
Thanks for helping,
Mike

 

[David Sceppa]

Mike,

You can use an OracleDataAdapter to retrieve data from a ref cursor.
I've included some sample C# code and PL/SQL code to create the package and
stored procedure the C# code calls.

It's possible that there's a data type in your procedure or table that
the OracleClient .NET Data Provider does not support.

I hope this information proves helpful.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.


//C# code:

OracleCommand cmd = cn.CreateCommand();
cmd.CommandText = "PackCursorTest.DeptsAndEmpsByDeptName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("pDeptName", OracleType.VarChar, 14);
cmd.Parameters[0].Value = "RESEARCH";
cmd.Parameters.Add("pDeptCursor", OracleType.Cursor);
cmd.Parameters[1].Direction = ParameterDirection.Output;
cmd.Parameters.Add("pEmpCursor", OracleType.Cursor);
cmd.Parameters[2].Direction = ParameterDirection.Output;

OracleDataAdapter da = new OracleDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

Console.WriteLine("Department");
foreach (DataRow row in ds.Tables[0].Rows)
Console.WriteLine("\tDeptNo: {0}\tLoc: {1}",
row["DEPTNO"], row["LOC"]);
Console.WriteLine();

Console.WriteLine("Employees");
foreach (DataRow row in ds.Tables[1].Rows)
Console.WriteLine("\tName: {0}\tJob: {1}\tDeptNo: {2}",
row["ENAME"], row["JOB"], row["DEPTNO"]);


//PL/SQL code:

CREATE OR REPLACE PACKAGE PackCursorTest AS
TYPE curDepts IS REF CURSOR RETURN DEPT%ROWTYPE;
TYPE curEmps IS REF CURSOR RETURN EMP%ROWTYPE;
PROCEDURE DeptsAndEmpsByDeptName
(pDeptName IN varchar2, pDeptCursor OUT curDepts,
pEmpCursor OUT curEmps);
END;

CREATE OR REPLACE PACKAGE BODY PackCursorTest AS
PROCEDURE DeptsAndEmpsByDeptName
(pDeptName IN varchar2, pDeptCursor OUT curDepts,
pEmpCursor OUT curEmps) AS
BEGIN
OPEN pDeptCursor FOR SELECT * FROM DEPT WHERE DName = pDeptName;
OPEN pEmpCursor FOR SELECT E.* FROM EMP E, DEPT D
WHERE E.DeptNo = D.DeptNo AND D.DName = pDeptName;
END;
END;

 

[Mike]

Hi David,

Thanks a lot for replying that's very nice from you. Well, i haven't
tried yet but reading your code, the only difference between yours and
mine relies on the declaration of the cursor type where you put "...is
ref cursor return table%rowtype" and i only have "..is ref cursor". I
dont know if this is the cause of this error because i have simple
parameters in this proc, two of number type and the ref cursor. Else,
i'm also using an Oracle DataAdpater object. Anyway i'll try again and
give feedback.

Mike.

 

[Mike]

Well I've tried now with the example that David gave me, even though
it doesn't work !!! I've created the table emp as example, replace my
c# code with David's one and when it reachs the "da.Fill(ds)"
statement, it gives the error saying USERDEFINED ENCOUNTERED.
I don't if there's a problem of version of the Oracle.Client, i added
it from "Add Reference" option and selected the
System.Data.OracleClient.

Any Idea ? I'm a little bit despaired.

 

[Mike]

Here's the exact message i'm receiving, maybe that could help :

"An unhandled exception of type 'System.NotSupportedException' occured
in system.data.dll
Additional information : Unsupported Oracle data type USERDEFINED
ENCOUNTERED."

 

[Frans Bouma [C# MVP]]

Here's the exact message i'm receiving, maybe that could help :

"An unhandled exception of type 'System.NotSupportedException' occured
in system.data.dll
Additional information : Unsupported Oracle data type USERDEFINED
ENCOUNTERED."

Oracle's User defined types are not supported in .NET 1.1, so if you want to
load a table with user defined types, the provider can't convert it into an
object for .NET.

Frans.

 

[David Sceppa]

Well said, Frans.

Mike, what data types are you using in your table?

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.

 

[Mike]

All right, i found out the cause of the error after some long and
exhausted searches. It was because, in the table i was trying to
query, there is a column of type object which is an oracle datatype.
This object is used to manage timestamp info. for every record, it
contains members like registered_by, registered_at, replaced_by,
replaced_at.... and so on
And, doing a "select * from table", returned that column in the cursor
which caused the error. So i removed that column in my select
statement and handle it separately, it worked then perfectly indeed.

Woaw, happy to know this.
Thank you very much guys for your precious involvement and help.

Mike

 

[David Sceppa]

Mike,

Good call on trying a simple "SELECT * FROM MyTable" query. Why
didn't I think to suggest that? Glad to hear that you're able to work with
ref cursors now.

David Sceppa
Microsoft
This posting is provided "AS IS" with no warranties,
and confers no rights. You assume all risk for your use.
© 2004 Microsoft Corporation. All rights reserved.