Truilus
Consider the following table setup:
CREATE TYPE my_type as OBJECT
(
some_text varchar(50),
some_number number
)
/
CREATE TABLE some_table
(
id number not null primary key,
some_info my_type
);
INSERT INTO some_table VALUES (1, my_type('Foobar', 42));
When running a select that includes the `some_info` column, no rows are returned.
If the column is not part of the result, DbFiddle shows a result
Displaying object types like that is a bit tricky, but the row containing that column and the other columns should be displayed
https://dbfiddle.uk/?rdbms=oracle_18&fiddle=fc027416423c6301ee403cec89fc497f
Top Answer
User 1791
Another option might be to use the Oracle 21c fiddle, instead of 18c, since 21c has the `JSON_OBJECT` function:
select json_object(some_info)
from some_table
| JSON_OBJECT(SOME_INFO) |
| :-------------------------------------- |
| {"SOME_TEXT":"Foobar","SOME_NUMBER":42} |
[ db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_21&fiddle=026683bdbd8056b3ad84575d27f4f240)
That seems to work. Although, not if we specifically need to test in an older version of Oracle.
--------------
**Edit:**
The JSON_ARRAY function is also useful:
```
select
json_array((shape).sdo_ordinates returning clob) as str
from
test_table
```
| STR |
| :-------------------------------- |
| [10,20,30,40,50,60] |
| [70,80,90,100] |
| [110,120,130,140,150,160,170,180] |
https://dbfiddle.uk/?rdbms=oracle_21&fiddle=f8793c649cfeac4a20f3eeb02957d874
[Function to convert varray to delimited string](https://community.oracle.com/tech/developers/discussion/comment/16841078/#Comment_16841078)
Answer #2
User 1791
Would it help to display the object name?
sys.anydata.gettypename(sys.anydata.convertobject(...))
ID | SOME_INFO
-: | :----------------------------------
1 | FIDDLE_NZJRCQZEFRDAZKPZQCRU.MY_TYPE
[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=3a126c06a4c5e5e2ae25557438d3aa30)
--------------
Related:
- [Provide tips in db<>fiddle help for returning abstract datatypes (Oracle)](https://topanswers.xyz/fiddle?q=2045)
- [Testing a UDT in Oracle Live SQL: Describe a query's column datatype without creating a view](https://stackoverflow.com/questions/72681217/testing-a-udt-in-oracle-live-sql-describe-a-querys-column-datatype-without-cre)
- [In Oracle, how do I verify the object type used from an object type hierarchy?](https://stackoverflow.com/questions/51033733/in-oracle-how-do-i-verify-the-object-type-used-from-an-object-type-hierarchy)
-------------
**Edit:**
Remove the lengthy user name:
```
select
id,
regexp_substr(
sys.anydata.gettypename(sys.anydata.convertobject(some_info)),
'[^.]+', 1, 2) as object_name
from
some_table
```
ID | OBJECT_NAME
-: | :----------
1 | MY_TYPE
[db<>fiddle](https://dbfiddle.uk/?rdbms=oracle_18&fiddle=e14097011a448fd9aa5970179249bfb2)