Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Oracle 8i / User-Defined Type Problem

Posted on 2003-11-18
11
Medium Priority
?
1,127 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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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

On Demand Webinar: Networking for the Cloud Era

Ready to improve network connectivity? Watch this webinar to learn how SD-WANs and a one-click instant connect tool can boost provisions, deployment, and management of your cloud connection.

Question has a verified solution.

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

Recently while returning home from work my wife (another .NET developer) was murmuring something. On further poking she said that she has been assigned a task where she has to serialize and deserialize objects and she is afraid of serialization. Wha…
Welcome my friends to the second instalment and follow-up to our Minify and Concatenate Your Scripts and Stylesheets (http://www.experts-exchange.com/Programming/Languages/.NET/ASP.NET/A_4334-Minify-and-Concatenate-Your-Scripts-and-Stylesheets.html)…
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…

722 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