Quick Search:
CODE
Oracle PL/SQL Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
 The Oracle COLLECT Function       [Return To Index] Jump to:  
  Looking for the original pages? (formerly called "Morgan's Library") You can find them here.

Term: COLLECT

Definition:
The Oracle COLLECT function enables aggregating data into a collection, retaining multiple records within a single row (like a nested table). Essentially, COLLECT takes a column of any type and creates a nested table of the input type out of the rows selected.

A key benefit of this function is that it makes "string aggregation" very simple. One important difference between COLLECT and BULK COLLECT is that it cannot be used with local PL/SQL types, since a SQL type is needed.

Example Syntax:

COLLECT(<column>)
CREATE TYPE phone_book_t AS TABLE OF phone_list_typ;


Example Usage:

SELECT CAST(COLLECT(phone_numbers) AS phone_book_t)
H FROM customers;



Related Links:

Related Code Snippets:
  • BULK COLLECT - The SQL engine will bulk bind all the values present for a \column in a table befor...
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us 236 users online    © 2009 psoug.org
PSOUG LOGIN
Username: 
Password: 
Forgot your password?