Let me ask a similar questions. I would be grateful if you could help me out on the same ...

I have a master table

Master_TABLE
MCol1
MCol2
Mcol3

Child_table
CCol1
CCol2
CCol3

Now I somehow need the data in the following format -

FCol1 FCol2 FCol3 Fcol4
MCol1 MCol2 Mcol3 <<Child object or Nested table>>

Now the child object can have multiple rows from the child table.

Benefit - If I could do this I would avoid unnecessary flow of the master data row for each child row across the network.

Use - Want to pull data from master child table from Oracle and in java would need to create a object with 1 entry for the parent and multiple entries for the child.

Let me know if the requirement makes sense.

SQL> create or replace type emp_row as object
  2   (
  3   empno                         number(4)
  4   ,ename                                  varchar2(10)
  5   ,job                                    varchar2(9)
  6   ,mgr                                    number(4)
  7   ,hiredate                               date
  8   ,sal                                    number(7,2)
  9   ,comm                                   number(7,2)
 10   ,deptno                                 number(2)
 11  );
 12  /

Type created.

SQL>
SQL> create or replace type emp_list as table of emp_row
  2  /

Type created.

SQL>
SQL> select
  2  deptno, dname,
  3         ( select collect(emp_row( empno, ename, job, mgr, hiredate, sal, comm, deptno))
  4           from emp
  5           where emp.deptno = dept.deptno)
  6  from dept;

    DEPTNO DNAME
---------- --------------
(SELECTCOLLECT(EMP_ROW(EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO))FROMEMPWHEREEMP.DEPTNO=DEPT.DEPTNO)(EMPNO, ENAME, J
------------------------------------------------------------------------------------------------------------------------
        10 ACCOUNTING
EMP_LIST(EMP_ROW(7782, 'CLARK', 'MANAGER', 7839, '09-JUN-81', 2450, NULL, 10), EMP_ROW(7839, 'KING', 'PRESIDENT', NULL,
'17-NOV-81', 5000, NULL, 10), EMP_ROW(7934, 'MILLER', 'CLERK', 7782, '23-JAN-82', 1300, NULL, 10))

        20 RESEARCH
EMP_LIST(EMP_ROW(7369, 'SMITH', 'CLERK', 7902, '17-DEC-80', 800, NULL, 20), EMP_ROW(7566, 'JONES', 'MANAGER', 7839, '02-
APR-81', 2975, NULL, 20), EMP_ROW(7788, 'SCOTT', 'ANALYST', 7566, '09-DEC-82', 3000, NULL, 20), EMP_ROW(7876, 'ADAMS', '
CLERK', 7788, '12-JAN-83', 1100, NULL, 20), EMP_ROW(7902, 'FORD', 'ANALYST', 7566, '03-DEC-81', 3000, NULL, 20))

        30 SALES
EMP_LIST(EMP_ROW(7499, 'ALLEN', 'SALESMAN', 7698, '20-FEB-81', 1600, 300, 30), EMP_ROW(7521, 'WARD', 'SALESMAN', 7698, '
22-FEB-81', 1250, 500, 30), EMP_ROW(7654, 'MARTIN', 'SALESMAN', 7698, '28-SEP-81', 1250, 1400, 30), EMP_ROW(7698, 'BLAKE
', 'MANAGER', 7839, '01-MAY-81', 2850, NULL, 30), EMP_ROW(7844, 'TURNER', 'SALESMAN', 7698, '08-SEP-81', 1500, NULL, 30)
, EMP_ROW(7900, 'JAMES', 'CLERK', 7698, '03-DEC-81', 950, NULL, 30))

        40 OPERATIONS
EMP_LIST()


4 rows selected.

when I try that code on 11.2.0.4.0 or 12.1.0.2.0, the type name resulting from the COLLECT aggregate function is system generated

SYSTPbm+nxlVHUPPgU38J7Ar/hA==(EMP_ROW(7839, 'KING' etc

and we need to cast it:

select cast(collect(emp_row(empno,ename,job,mgr,hiredate,sal,comm,deptno)) as emp_list)

to get

EMP_LIST(EMP_ROW(7839, 'KING' etc

the oracle docs say:
To get accurate results from this function you must use it within a CAST function

Hi,

There are various ways to accomplish this. Most db-tools (hibernate, openjpa, eclipselink) support a version of this by bulk fetching the details for a list of previously fetched masters. They usually use the IN-construct for Oracle.

Oracle can also fetch nested cursors, from which you can fetch the details. I've not tried this but a fear those are individual roundtrips.

I think there is a syntax for JDBC-selects, that suppresses repeated values (almost like sqlplus) but I haven't tried this either.

I'm pretty sure that the jdbc-transport-protocol is clever enough to not send repeated column values in the stream. On the client side those are just repeated pointers. You may need to use ORDER BY in the select for this to work though. So you could use the simple join for fetching.

I personally have trouble with using "extraneous" types to accomplish things like this. Particularly sql-types that don't support %ROWTYPE. Fortunately the support for package-types for SQL has been getting better and better with each Oracle version.

regards,

Personally, with recent enough versions of Oracle I would look at JSON, because it helps you put data into a hierarchy without multiple cursors and because it can be easily translated to Java classes.

with emps as (
  select deptno,
    json_arrayagg(
      json_object(
        'EMPNO' value empno, 
        'ENAME' value ENAME, 
        'JOB' value JOB, 
        'MGR' value MGR,  
        'HIREDATE' value HIREDATE,  
        'SAL' value SAL, 
        'COMM' value COMM absent on null
      )
    ) j_emps
  from emp
  group by deptno
)
select json_arrayagg(
  json_object(
    'DEPTNO' value DEPTNO,
    'DNAME' value DNAME,
    'LOC' value LOC,
    'EMPS' value j_emps
  )
) json_data
from dept join emps using(deptno);

[
  {
    "DEPTNO": 10,
    "DNAME": "ACCOUNTING",
    "LOC": "NEW YORK",
    "EMPS": [
      {
        "EMPNO": 7782,
        "ENAME": "CLARK",
        "JOB": "MANAGER",
        "MGR": 7839,
        "HIREDATE": "1981-06-09T00:00:00",
        "SAL": 2450
      },
      {
        "EMPNO": 7934,
        "ENAME": "MILLER",
        "JOB": "CLERK",
        "MGR": 7782,
        "HIREDATE": "1982-01-23T00:00:00",
        "SAL": 1300
      },
      {
        "EMPNO": 7839,
        "ENAME": "KING",
        "JOB": "PRESIDENT",
        "HIREDATE": "1981-11-17T00:00:00",
        "SAL": 5000
      }
    ]
  },
  {
    "DEPTNO": 20,
    "DNAME": "RESEARCH",
    "LOC": "DALLAS",
    "EMPS": [
      {
        "EMPNO": 7369,
        "ENAME": "SMITH",
        "JOB": "CLERK",
        "MGR": 7902,
        "HIREDATE": "1980-12-17T00:00:00",
        "SAL": 800
      },
      {
        "EMPNO": 7902,
        "ENAME": "FORD",
        "JOB": "ANALYST",
        "MGR": 7566,
        "HIREDATE": "1981-12-03T00:00:00",
        "SAL": 3000
      },
      {
        "EMPNO": 7876,
        "ENAME": "ADAMS",
        "JOB": "CLERK",
        "MGR": 7788,
        "HIREDATE": "1987-05-23T00:00:00",
        "SAL": 1100
      },
      {
        "EMPNO": 7566,
        "ENAME": "JONES",
        "JOB": "MANAGER",
        "MGR": 7839,
        "HIREDATE": "1981-04-02T00:00:00",
        "SAL": 2975
      }
    ]
  },
  {
    "DEPTNO": 30,
    "DNAME": "SALES",
    "LOC": "CHICAGO",
    "EMPS": [
      {
        "EMPNO": 7499,
        "ENAME": "ALLEN",
        "JOB": "SALESMAN",
        "MGR": 7698,
        "HIREDATE": "1981-02-20T00:00:00",
        "SAL": 1600,
        "COMM": 300
      },
      {
        "EMPNO": 7900,
        "ENAME": "JAMES",
        "JOB": "CLERK",
        "MGR": 7698,
        "HIREDATE": "1981-12-03T00:00:00",
        "SAL": 950
      },
      {
        "EMPNO": 7844,
        "ENAME": "TURNER",
        "JOB": "SALESMAN",
        "MGR": 7698,
        "HIREDATE": "1981-09-08T00:00:00",
        "SAL": 1500,
        "COMM": 0
      },
      {
        "EMPNO": 7698,
        "ENAME": "BLAKE",
        "JOB": "MANAGER",
        "MGR": 7839,
        "HIREDATE": "1981-05-01T00:00:00",
        "SAL": 2850
      },
      {
        "EMPNO": 7654,
        "ENAME": "MARTIN",
        "JOB": "SALESMAN",
        "MGR": 7698,
        "HIREDATE": "1981-09-28T00:00:00",
        "SAL": 1250,
        "COMM": 1400
      },
      {
        "EMPNO": 7521,
        "ENAME": "WARD",
        "JOB": "SALESMAN",
        "MGR": 7698,
        "HIREDATE": "1981-02-22T00:00:00",
        "SAL": 1250,
        "COMM": 500
      }
    ]
  }
]