Start a new topic

show table oracle types or varray types in query results

[This topic is migrated from our old forums. The original author name has been removed]

I want to execute a query grouped by some column and show a collected column as a nested table in oracle (see https://docs.oracle.com/database/121/SQLRF/functions034.htm#SQLRF06304 for more information about collect). The query executes fine, but the collected column is shown as null. When I execute the same query in a java application using jdbc, I am able to show the results of the nested table (see https://docs.oracle.com/cd/B28359_01/java.111/b31224/oraarr.htm#BABDEJBJ for more information about how to retrieve data from an oracle.sql.ARRAY type in jdbc). As an example, I create a customers and a bankaccounts table: create table customers ( id number(6) not null primary key , name varchar2(30) not null , birthdate date not null ); create table bankaccounts ( nr number(10) not null primary key , customer_id number(6) not null , current_balance number(14,2) not null ); ... and populate it: insert into customers select 1, 'Jeffrey Lebowski', date '1964-01-01' from dual union all select 2, 'Walter Sobchak', date '1961-01-01' from dual; insert into bankaccounts select 123456789, 1, 10 from dual union all select 987654321, 1, 100 from dual union all select 234567890, 2, 2000 from dual; I also create a nested table type: CREATE TYPE myNumberTableType AS TABLE OF number(14,2); ... and finally execute the following query: select c.id, c.name, cast(collect (b.current_balance) as myNumberTableType) as collectedBalances from customers c inner join bankaccounts b on b.customer_id = c.id group by c.id, c.name; This executes fine, but the last column only shows nulls: ID NAME COLLECTEDBALANCES -- ---------------- ----------------- 1 Jeffrey Lebowski (null) 2 Walter Sobchak (null) I also tried the same using a VARRAY: CREATE TYPE myNumberArrayType AS VARRAY(10) OF number(14,2); ... and execute the query like the following: select c.id, c.name, cast(collect (b.current_balance) as myNumberArrayType) as collectedBalances from customers c inner join bankaccounts b on b.customer_id = c.id group by c.id, c.name; ... but I still get the same results. I'm using dbViz 9.1.7 using oracle thin driver using ojdbc7-12.1.0.2.0.jar. Is dbViz supposed to support nested tables and varrays in queries? Best regards Jan

[This reply is migrated from our old forums.]

Re: show table oracle types or varray types in query results
Hi Jan, DbVisualizer currently does not support displaying nested tables. We have an open ticket for looking into this and I have added your vote for this feature. Best Regards, Hans Edited by: Hans Bergsten on Mar 31, 2016 9:52 PM
[This reply is migrated from our old forums. The original author name has been removed]

Re: show table oracle types or varray types in query results
ok, thanks for the recordly Jan