Oracle 8i / User-Defined Type Problem

I am trying to create a user-defined type within Oracle, for example:

Create Type User_Tmp as Object
(
   First_Name varchar2(20),
   Last_Name varchar2(20)
);

I then created a function, for example:

Create or replace function GetUser as User_Tmp is
   myObj User_Tmp := User_Tmp(null,null)
begin
   myObj.First_Name := 'John';
   myObj.Last_Name := 'Doe';
   return myObj;
end;

When I call this from SQLPlus (or Toad) it works successfully. However, when calling it from a .NET app it throws an exception with the following message:

"Unsupported Oracle data type USERDEFINED encountered."

Any ideas how I can get around this?
LVL 15
liebrandAsked:
Who is Participating?
 
NetminderCommented:
Points (250) refunded and question closed.

Netminder
EE Admin
0
 
andrewharrisCommented:
First;y, how are you connecting to Oracle?

If you aren't using the Oracle OleDB provider, I would defineatly be trying that first. I have NEVER been able to get anything other than that 100% working.

Andrew
0
 
liebrandAuthor Commented:
I am using the Oracle Provider for .NET
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
andrewharrisCommented:
The MS one or the Oracle one?

Andrew
0
 
liebrandAuthor Commented:
The oracle one ... I believe I create a work around that seems to be working right now, here is what I did (in a nutshell):

create or replace type t_obj_test as object
(a number, b number, c varchar2(10));

create type t_tab_demo_proc as table of t_obj_test;

CREATE OR REPLACE function demo_proc
 return t_tab_demo_proc
    as
        l_data      t_tab_demo_proc := t_tab_demo_proc();
        l_cnt              number default 0;
    begin
             l_data.extend;
             l_data(1) := t_obj_test( 1,2,'test');
             l_data.extend;
             l_data(2) := t_obj_test( 2,3,'test');

       return l_data;
end;
/

select * from table(cast(demo_proc as t_tab_demo_proc));
0
 
andrewharrisCommented:
Sorry, only just occured to me that I missed you saying provider for .Net.

Can you paste the code you are using to execute the function?

Andrew
0
 
liebrandAuthor Commented:
Here is a snippet:

dim command as new oraclecommand("select GetUser() as user from dual", connection)
dim dr as oracledatareader

dr = command.executereader   <--- fails here
while dr.read
    console.writeline(dr("user"))
wend
0
 
liebrandAuthor Commented:
My comment on 11/18/2003 03:42PM PST seems to be working fine though -- so I am not sure we need to pursue this further. I just find it interesting that we cannot use user-defined types with .NET.
0
 
andrewharrisCommented:
Happy to let it go if you want.

Andrew
0
 
liebrandAuthor Commented:
Yea -- my solution is working for my needs... no need to pursue any further
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.