Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>You should be able to write a <a href="http://www.oracle-base.com/articles/misc/StringAggregationTechniques.php#user_defined_aggregate_function" rel="nofollow">custom aggregate function</a> along the lines of the aggregate functions that were created to concatenate strings before 11.2 and the LISTAGG function. </p> <p>For example, I can create an EMP_OBJ object and a collection of those objects (EMP_TBL) and write a custom aggregate function that generates an EMP_TBL collection from a simple SQL statement</p> <p>Create the basic types</p> <pre><code>SQL&gt; create type emp_obj as object ( 2 empno number, 3 ename varchar2(100) 4 ); 5 / Type created. SQL&gt; create type emp_tbl 2 as 3 table of emp_obj; 4 / Type created. </code></pre> <p>Create the type that we'll use to do the aggregation</p> <pre><code>SQL&gt; ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE emp_tbl_agg AS OBJECT 2 ( 3 g_emp_coll emp_tbl, 4 STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT emp_tbl_agg ) 5 RETURN NUMBER, 6 MEMBER FUNCTION ODCIAggregateIterate(self IN OUT emp_tbl_agg , 7 value IN emp_obj) 8 RETURN NUMBER, 9 MEMBER FUNCTION ODCIAggregateTerminate(self IN emp_tbl_agg, 10 returnValue OUT emp_tbl, 11 flags IN NUMBER) 12 RETURN NUMBER, 13 MEMBER FUNCTION ODCIAggregateMerge(self IN OUT emp_tbl_agg, 14 ctx2 IN emp_tbl_agg) 15 RETURN NUMBER 16* ); 17 / Type created. SQL&gt; ed Wrote file afiedt.buf 1 CREATE OR REPLACE TYPE BODY emp_tbl_agg IS 2 STATIC FUNCTION ODCIAggregateInitialize(sctx IN OUT emp_tbl_agg) 3 RETURN NUMBER IS 4 BEGIN 5 sctx := emp_tbl_agg(NULL); 6 sctx.g_emp_coll := new emp_tbl(); 7 RETURN ODCIConst.Success; 8 END; 9 MEMBER FUNCTION ODCIAggregateIterate(self IN OUT emp_tbl_agg, 10 value IN emp_obj ) 11 RETURN NUMBER IS 12 BEGIN 13 SELF.g_emp_coll.extend(); 14 SELF.g_emp_coll(self.g_emp_coll.count) := value; 15 RETURN ODCIConst.Success; 16 END; 17 MEMBER FUNCTION ODCIAggregateTerminate(self IN emp_tbl_agg, 18 returnValue OUT emp_tbl, 19 flags IN NUMBER) 20 RETURN NUMBER IS 21 BEGIN 22 returnValue := self.g_emp_coll; 23 RETURN ODCIConst.Success; 24 END; 25 MEMBER FUNCTION ODCIAggregateMerge(self IN OUT emp_tbl_agg, 26 ctx2 IN emp_tbl_agg) 27 RETURN NUMBER IS 28 BEGIN 29 FOR i IN ctx2.g_emp_coll.FIRST .. ctx2.g_emp_coll.LAST 30 LOOP 31 self.g_emp_coll.extend(); 32 self.g_emp_coll(self.g_emp_coll.count) := ctx2.g_emp_coll(i); 33 END LOOP; 34 RETURN ODCIConst.Success; 35 END; 36* END; SQL&gt; / Type body created. </code></pre> <p>Declare the aggregate function</p> <pre><code>SQL&gt; create or replace function emp_agg( p_input emp_obj ) 2 return emp_tbl 3 aggregate using emp_tbl_agg; 4 / Function created. </code></pre> <p>And now a simple GROUP BY using our custom aggregate function will generate an EMP_TBL collection</p> <pre><code>SQL&gt; select deptno, emp_agg( emp_obj( empno, ename ) ) 2 from emp 3 group by deptno; DEPTNO ---------- EMP_AGG(EMP_OBJ(EMPNO,ENAME))(EMPNO, ENAME) -------------------------------------------------------------------------------- 10 EMP_TBL(EMP_OBJ(7782, 'CLARK'), EMP_OBJ(7934, 'MILLER'), EMP_OBJ(7839, 'KING')) 20 EMP_TBL(EMP_OBJ(7369, 'smith'), EMP_OBJ(7902, 'FORD'), EMP_OBJ(7876, 'ADAMS'), E MP_OBJ(7788, 'SCOTT'), EMP_OBJ(7566, 'JONES')) 30 EMP_TBL(EMP_OBJ(7499, 'ALLEN'), EMP_OBJ(7900, 'SM2'), EMP_OBJ(7844, 'TURNER'), E DEPTNO ---------- EMP_AGG(EMP_OBJ(EMPNO,ENAME))(EMPNO, ENAME) -------------------------------------------------------------------------------- MP_OBJ(7698, 'BLAKE'), EMP_OBJ(7654, 'MARTIN'), EMP_OBJ(7521, 'WARD')) </code></pre>
    singulars
    1. This table or related slice is empty.
    1. POIs there an alternative to the MULTISET operator that avoids sub-queries?
      singulars
      1. PTQuestion
    1. PTAnswer
    1. USJustin Cave
    1. USJustin Cave
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTUpMod
    1. CO+1: I forgot about the custom aggregate functions! Sure, it's tremendously verbose, but it certainly gets the job done. Thanks!
      singulars
      1. PO
      1. USAdam Paynter
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload