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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
liebrandAuthor Commented:
I am using the Oracle Provider for .NET
andrewharrisCommented:
The MS one or the Oracle one?

Andrew
Become a Certified Penetration Testing Engineer

This CPTE Certified Penetration Testing Engineer course covers everything you need to know about becoming a Certified Penetration Testing Engineer. Career Path: Professional roles include Ethical Hackers, Security Consultants, System Administrators, and Chief Security Officers.

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));
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
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
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.
andrewharrisCommented:
Happy to let it go if you want.

Andrew
liebrandAuthor Commented:
Yea -- my solution is working for my needs... no need to pursue any further
NetminderCommented:
Points (250) refunded and question closed.

Netminder
EE Admin

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
.NET Programming

From novice to tech pro — start learning today.