Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

Oracle 8i / User-Defined Type Problem

Posted on 2003-11-18
11
Medium Priority
?
1,131 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
  • 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
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 
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

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

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

Many of us here at EE write code. Many of us write exceptional code; just as many of us write exception-prone code. As we all should know, exceptions are a mechanism for handling errors which are typically out of our control. From database errors, t…
Hello there! As a developer I have modified and refactored the unit tests which was written by fellow developers in the past. On the course, I have gone through various misconceptions and technical challenges when it comes to implementation. I would…
this video summaries big data hadoop online training demo (http://onlineitguru.com/big-data-hadoop-online-training-placement.html) , and covers basics in big data hadoop .
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…

926 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