Solved

Oracle 8i / User-Defined Type Problem

Posted on 2003-11-18
11
1,118 Views
Last Modified: 2012-06-21
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?
0
Comment
Question by:liebrand
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 4
11 Comments
 
LVL 4

Expert Comment

by:andrewharris
ID: 9773708
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
 
LVL 15

Author Comment

by:liebrand
ID: 9773828
I am using the Oracle Provider for .NET
0
 
LVL 4

Expert Comment

by:andrewharris
ID: 9774329
The MS one or the Oracle one?

Andrew
0
SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

 
LVL 15

Author Comment

by:liebrand
ID: 9775031
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
 
LVL 4

Expert Comment

by:andrewharris
ID: 9775348
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
 
LVL 15

Author Comment

by:liebrand
ID: 9780067
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
 
LVL 15

Author Comment

by:liebrand
ID: 9780074
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
 
LVL 4

Expert Comment

by:andrewharris
ID: 9783917
Happy to let it go if you want.

Andrew
0
 
LVL 15

Author Comment

by:liebrand
ID: 9788836
Yea -- my solution is working for my needs... no need to pursue any further
0
 
LVL 5

Accepted Solution

by:
Netminder earned 0 total points
ID: 10247818
Points (250) refunded and question closed.

Netminder
EE Admin
0

Featured Post

Guide to Performance: Optimization & Monitoring

Nowadays, monitoring is a mixture of tools, systems, and codes—making it a very complex process. And with this complexity, comes variables for failure. Get DZone’s new Guide to Performance to learn how to proactively find these variables and solve them before a disruption occurs.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

More often than not, we developers are confronted with a need: a need to make some kind of magic happen via code. Whether it is for a client, for the boss, or for our own personal projects, the need must be satisfied. Most of the time, the Framework…
This article shows how to deploy dynamic backgrounds to computers depending on the aspect ratio of display
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question