How to read UDT object into result query?

Question

I have a User defined type with many attributes like so:

create or replace type MyObject is object
(
    attribute 1,
    attribute 2,
    attribute 3
    ...
)

I have a function that returns that object:

create or replace function GetMyObject
(...params...)
return MyObject
is
...

Now I attempt to call that function from a C# application using Oracle.ManagedDataAccess.Client. Because MyObject is not known type to C#, I try to call OracleCommand.ExecuteReader() and read the values of attributes from a result query one by one:

select GetMyObject(...) from dual;

What I am getting in Oracle is this table:

    |    GetMyObject(...)   |
--------------------------------
 1  |  [UserName.MyObject]  |

Instead I expect to get this :

    | attribute 1 | attribute 2 | attribute 3 | ...
------------------------------------------------------
 1  |   value 1   |   value 2   |   value 3   | ...

How should the select statement look like?

Answer 1

The whole point of "objects" is that they are composite data, treated as single units. That is why you are seeing what you are seeing.

If you want to see the individual components, you will need to SELECT three columns (or however many), not one. And to access the individual attributes, one per column, you must use "dot" notation.

select GetMyObject(...).attribute1 as attribute1
     , GetMyObject(...).attribute2 as attribute2
--   (etc.)
from dual;

Obviously, I assume you didn't actually have spaces in attribute names.