java.sql.Types.ARRAY from simple select statement

Question

What I have:

Table "CI", many columns in here, ci_id, description, ...
Table "CI_REL", ci_id, parent_ci_id, child_ci_id
CI_REL having multiple relations in them, to show that one record in CI has multiple parents, or multiple children records in CI

What I need:

Join the two tables, so I get one record of CI enriched with an Sql ARRAY (VARRAY?) for the parents and children in the record.

Example:

|id | description | children | parents |
|1  | root        | [2,3]    | null    |
|2  | child1      | [3]      | [1]     |
|3  | child2      | [2]      | [1]     |

Why do I need this? I'm using Apache Nifi for Data Ingestion into Apache Solr, and need a multivalue field for children and parents. Since the ExecuteSQL is interpreting the sql ResultSet, and acts on finding java.sql.Types.ARRAY in the way I need, I'd like to have a select, that actually returns this, somehow. BTW: I don't have any possibility to use PL/SQL, hopefully this won't make an answer impossible...

Thanks! Henning

I think you might be looking for [the `collect` function](https://docs.oracle.com/en/database/oracle/oracle-database/12.2/sqlrf/COLLECT.html), but you'd need to cast to a collection type you then map to an array? Showing your raw data might be useful; and why are `child1` and `child2` children of each other as well as of `root` (according to your `children` value for those, anyway)? — Alex Poole, Jan 19 '18 at 13:18
So something like that? I don't have the rights to create datatypes... select cast(collect(SELECT DISTINCT CHILD_CI_ID FROM CI_REL WHERE CI.CI_ID = PARENT_CI_ID) as varray(10) of varchar2(10)) The structure is a bit weird indeed, just to show, why I actually need arrays/multivalues here. The real data is similar, though. — Frischling, Jan 19 '18 at 13:26

score 2 · Accepted Answer · answered Jan 19 '18 at 14:46

You can use CAST( COLLECT( ... ) AS ... ) in a correlated sub-query generate your collections:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE CI ( id, description ) AS
SELECT 1, 'root' FROM DUAL UNION ALL
SELECT 2, 'child1' FROM DUAL UNION ALL
SELECT 3, 'child2' FROM DUAL;

CREATE TABLE CI_REL ( ci_id, child_ci_id, parent_ci_id ) AS
SELECT 1, 2, NULL FROM DUAL UNION ALL
SELECT 2, 3, 1    FROM DUAL UNION ALL
SELECT 3, NULL, 2 FROM DUAL;

CREATE TYPE NumberList IS TABLE OF Number(8,0);

Query 1:

SELECT c.*,
       ( SELECT CAST(
                  COLLECT(
                    DISTINCT child_ci_id
                    ORDER BY LEVEL
                  ) AS NumberList
                )
          FROM  CI_REL r
          WHERE child_ci_id IS NOT NULL
          START WITH r.ci_id = c.id
          CONNECT BY PRIOR child_ci_id = ci_id
       ) AS children,
       ( SELECT CAST(
                  COLLECT(
                    DISTINCT parent_ci_id
                    ORDER BY LEVEL
                  ) AS NumberList
                )
          FROM  CI_REL r
          WHERE parent_ci_id IS NOT NULL
          START WITH r.ci_id = c.id
          CONNECT BY PRIOR parent_ci_id = ci_id
        ) AS parents
FROM   CI c

Results:

| ID | DESCRIPTION | CHILDREN | PARENTS |
|----|-------------|----------|---------|
|  1 |        root |      2,3 |         |
|  2 |      child1 |        3 |       1 |
|  3 |      child2 |          |     2,1 |

Hi, sadly I can't create types. Not allowed to... is there a way without creating type? — Frischling, Jan 19 '18 at 15:25
`select distinct owner, type_name, coll_type, elem_type_name, length from all_coll_types where elem_type_name = 'NUMBER';` should give you a list of all the available number collections and VARRAYs. — MT0, Jan 19 '18 at 15:34
Cool, yes, I found this answer here: https://stackoverflow.com/questions/8785459/anonymous-table-or-varray-type-in-oracle#8786450 which goes in the same direction (I even need varchar2s in my real data)... almost done :) — Frischling, Jan 19 '18 at 15:35

java.sql.Types.ARRAY from simple select statement

1 Answers1