Code Library - Oracle Live SQL

Tutorial

Get Started with Table Functions 1: Overview

This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". This module of...

table function

88 7,060

7.1 years ago Steven Feuerstein

Tutorial

Get Started with Table Functions 4: Pipelined Table Functions

This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". Pipelined tabl...

pipelined table function

29 1,717

7.1 years ago Steven Feuerstein

Tutorial

Get Started with Table Functions 2: Returning Multiple Columns

This tutorial is part of the Oracle Dev Gym class "Get Started with Table Functions". This module sh...

table function

33 1,614

7.1 years ago Steven Feuerstein

Script

Varray Examples

The varray (variable size array) is one of the three types of collections in PL/SQL (associative arr...

varray

14 148

9.6 years ago Steven Feuerstein

Script

SELECT * FROM [table] with PL/SQL procedure

"In Table" utility: use DBMS_SQL to implement a Method 4 dynamic SQL challenge. Overview: ...

method 4,dynamic SQL

6 133

8.2 years ago Steven Feuerstein

Script

SQL on Collections

"Hi Steven, what is the best way to perform operations like SELECT a, SUM(b) from t GROUP BY a that ...

table operator

4 66

9.5 years ago Steven Feuerstein

Script

ROW GENERATOR - Methods to Generate Series

A collection of methods to create a list on the fly. Commonly referred to as row generation querie...

Row generator, SQL, Model Clause, generate_series

17 61

9.8 years ago Sven Weller

Script

Simple Table Function Example: Collection of Scalars

A table function is a function executed with the TABLE operator, and then within the FROM clause of ...

table function

2 52

9.2 years ago Steven Feuerstein

Script

Initializing Collection (Varray) Variable to Empty

This example invokes a constructor twice: to initialize the varray variable team to empty in its dec...

LNPLS

0 41

8.5 years ago Sarah Hirschfeld (Oracle)

Script

Fun with Collection Methods

In this script, I demonstrate most of the methods available for PL/SQL collections, including COUNT,...

COLLECTION,METHOD

4 39

9.5 years ago Steven Feuerstein

Script

Accessing index of associative array in SELECT-FROM TABLE() operation

As of Oracle Database 12c Release 1, you can now use the TABLE operator with associative arrays whos...

TABLE,INDEX VALUE

2 39

9.4 years ago Steven Feuerstein

Script

Inserting an array in a table

This script create a collection (varray of records) and insert it into a table.

collections, varrays, insert collections, type, record type,

0 36

6.8 years ago Victor Augusto

Script

Demonstration of SQL%BULK_ROWCOUNT FORALL Pseudo-Collection

SQL%BULK_ROWCOUNT is a pseudo-collection that contains one element for each DML statement executed b...

SQL%BULK_ROWCOUNT,FEUERSTEIN,FORALL

4 36

9.7 years ago Steven Feuerstein

Script

Outer_Apply_01

Example for using OUTER APPLY to outer join with a table function. See more details on OUTER APP...

apply, outer join,table function

2 32

9.8 years ago Lucas Jellema

Script

Data Type Compatibility for Collection Assignment

VARRAY types triplet and trio have the same element type, VARCHAR(15). Collection variables group1 a...

LNPLS PLS-00382

1 28

8.5 years ago Sarah Hirschfeld (Oracle)

Script

FORALL

FORALL transfers data from a PL/SQL collection to the specified table using collections.

FORALL

0 27

7.8 years ago Damián Ribeiro

Script

Best Collection Type for FORALL?

What is the best type of collection to use with FORALL? Is it an associative array? Nested table? Va...

performance,forall,collection

3 26

6.1 years ago Steven Feuerstein

Script

A To JSON Conversion Package (starting point)

I wrote this package for an upcoming Oracle Magazine article on JSON and PL/SQL. I didn't want to ha...

json,12.2,plsql,array

1 26

7.4 years ago Steven Feuerstein

Script

SODA for PL/SQL: Read and Write Operations

This script accompanies an Oracle Magazine article be published in mid-2019. In the meantime, enjoy ...

soda,json,collection,document

2 25

6.2 years ago Steven Feuerstein

Script

BULK COLLECT Never Appends to Your Collection

BULK COLLECT always replaces the current contents of a collection before the fetch is executed. It n...

bulk collect, collection

0 25

8.7 years ago Steven Feuerstein

Script

An Introduction - Collections and Ref Cursors

This script gives an introduction of how to use a nested table collection and store results of a dyn...

Collections, Ref Cursors

1 25

5 years ago Arnab P

Script

Use TABLE Operator with Associative Arrays in Oracle Database 12c (for blog post)

This script "backs up" the post written by Steven Feuerstein demonstrating the use of TABLE with ass...

12c,12.1,collection,table

1 24

7.9 years ago Steven Feuerstein

Script

Randomizer Package: Generate lists of unique, random values

Uses DBMS_RANDOM to generate random integers and strings, but also ensures no duplicates and passes ...

RANDOM

2 24

9.6 years ago Steven Feuerstein

Script

MULTISET UNION Examples

Use MULTISET UNION to perform set-level union operations on nested tables. Remember: with MULTISET, ...

collection,nested table,multiset,union

0 22

6.8 years ago Steven Feuerstein

Script

Iterating Through Collections

Collections can be sparse or dense. They can be associative arrays, nested tables, varrays. They can...

collection,iterator

4 22

4.1 years ago Steven Feuerstein

Script

Nested Collections Example

You can nest collections within collections, directly and indirectly. In this script, I create a set...

nested collection,collection

1 21

9.4 years ago Steven Feuerstein

Script

Advanced Collection Features: Nested and String-Indexed

Here's a package that makes it easy to manage lists of lists, using a nested collection and a string...

NESTED COLLECTION,STRING INDEX

2 21

9.6 years ago Steven Feuerstein

Script

Assigning Null Value to Nested Table Variable

This example initializes the nested table variable dept_names to a non-null value; assigns a null co...

LNPLS

0 19

8.5 years ago Sarah Hirschfeld (Oracle)

Script

Sort Associative Arrays Using SQL (12.1)

Starting with 12.1, you can apply the TABLE operators to associative arrays indexed by integer (inde...

SORT,associative,array,12c

0 16

9.5 years ago Steven Feuerstein

Script

Identically Defined Package and Local Collection Types

The package specification and the anonymous block define the collection type NumList identically.

PL/SQL PLS-00306

0 15

8.3 years ago Sarah Hirschfeld (Oracle)

Script

Characteristics of BULK COLLECT Collections

This script demonstrates key characteristics of a collection populated by a BULK COLLECT fetch: it i...

collection,bulk collect

1 15

9.1 years ago Steven Feuerstein

Script

Use Warnings to Identify NOCOPY Opportunities

With OUT and IN OUT parameters, you can benefit from adding the NOCOPY clause, which asks the compil...

WARNING,ERROR,plw,nocopy

1 14

7.4 years ago Steven Feuerstein

Script

MULTISET example

use of MULTISET EXCEPT, INTERSECT, UNION on collection type of nested table. For a complex type ...

MULTISET EXCEPT INTERSECT UNION

0 14

9.2 years ago Amit Shah

Script

Query Table with JSON Data Type

This script created a table with a CLOB attribute to save JSON documents. A query shows how to Quer...

Select JSON contents from a Collection

2 11

7.1 years ago FernandoPonte

Script

FORALL with VALUES OF

The VALUES OF clause, like its "sister" INDICES OF, makes it easier to use FORALL with bind arrays t...

VALUES OF,FORALL

0 11

9.5 years ago Steven Feuerstein

Script

Checking Equality of Nested Tables

One of the nicest things about nested tables is that you can compare two such collections for equali...

NESTED TABLE

2 11

9.5 years ago Steven Feuerstein

Script

Qualified Expressions for Associative Arrays (aka, collection constructors)

Aggregates and their necessary adjunct, qualified expressions, improve program clarity and programme...

18c,collection,array,initialize,constructor

4 10

7.4 years ago Steven Feuerstein

Script

[ACollectionIsACollection][10g]table function parameter

Demo script displaying how to build and use a table function in Oracle 10g.

Collections Oracle10g Presentation Patrick Barel Patch72

1 9

4.8 years ago Patch72

Script

Working with Varrays

Using collection methods such as LIMIT, COUNT, FIRST, EXTEND, TRIM and DELETE for working with Varra...

Varray

1 8

6.6 years ago merce_jance

Script

Generate code to move rows into a string-indexed collection

We'd all love to be able to do something like "SELECT * BULK COLLECT INTO x FROM employees INDEX BY ...

3 8

5 years ago Steven Feuerstein

Script

table() usage inconsistency

table() function works fine in select statements and fails in delete statements

table() collection ORA-00902

0 7

8.1 years ago azemerov

Script

Emulate multi-dimensional arrays with nested collections

PL/SQL doesn't offer native support for multi-dimensional arrays, as you will find in other programm...

mutli-dimensional array, array,collection

1 7

9.4 years ago Steven Feuerstein

Script

Statistics collection in 12c for CTAS tables

Script demonstrating the ability of Oracle 12c to collect table statistics during a CTAS and during ...

12c CTAS statistics collection

0 6

8.6 years ago Richard Smith

Script

Find Associative Array INDEX BY Type

Lukas Eder of jOOQ graciously gave permission to share his very interesting use of dynamic PL/SQL to...

index by,collection type

0 6

8.5 years ago Steven Feuerstein

Script

[ACollectionIsACollection][11g]Select from NT

Demo script displaying you have to create an object type for the Nested Table in Oracle 11g to be ab...

Collections Oracle11g Presentation Patrick Barel Patch72

0 5

4.8 years ago Patch72

Script

String Tracker Utility: Is this string already in use?

The string_tracker package allows you to keep track of whether a certain name has already been used ...

COLLECTION,METHOD,string index,associative array

0 5

9.4 years ago Steven Feuerstein

Script

PL/SQL Puzzle: string-indexed collection

When you are using a string-indexed collection and relying on implicit conversions, you might be sur...

0 5

5.6 years ago Steven Feuerstein

Script

Identically Defined Package and Standalone Collection Types

This defines a standalone collection type NumList that is identical to the collection type NumList d...

PL/SQL PLS-00306

0 5

8.3 years ago Sarah Hirschfeld (Oracle)

Script

collection_Procedure_OUT_Parameter_print

Bulk Collect into collection object type and print the OUT Parameter result.

1 4

8 years ago Krishnaraja

Script

[ACollectionIsACollection][9i]pipelined table function parameter

Demo script displaying how to build and use a pipelined table function in Oracle 9i.

Collections Oracle9i Presentation Patrick Barel Patch72

1 4

4.8 years ago Patch72

Script

[ACollectionIsACollection][12c]Select from AA

Demo script displaying you don't have to create an object type for the Nested Table in Oracle 12c to...

Collections Oracle12c Presentation Patrick Barel Patch72

1 4

4.8 years ago Patch72

Script

Errors with FORALL with Sparse Collections

If one or more of your bind collections are sparse (an undefined index value between lowest and high...

collection,forall,sparse

0 4

5.3 years ago Steven Feuerstein

Script

[ACollectionIsACollection][9i]Fetch from table bulk collect into associative array

Fetch the data from a table, bulk collecting into an Associative Array of records.

Collections Oracle9i Presentation Patrick Barel Patch72

0 3

4.8 years ago Patch72

Script

[ACollectionIsACollection][9i]Create a collection -AssociativeArray-Varchar2

Create an Associative Array using varchar2 as the index.

Collections Oracle9i Presentation Patrick Barel Patch72

0 3

4.8 years ago Patch72

Script

[ACollectionIsACollection][7]Fill collection from table

Create a PLSQL Table in Oracle7 using the data from a cursor.

Collections Oracle7 Presentation Patrick Barel Patch72

0 3

4.8 years ago Patch72

Script

[ACollectionIsACollection][10g]for loop timing

Script demonstrating how much time it takes to run through a cursor for loop when Oracle optimizes t...

Collections Oracle10g Presentation Patrick Barel Patch72

0 3

4.8 years ago Patch72

Script

[ACollectionIsACollection][10g]bulk collect timing

Script demonstrating how much time is takes to run through a cursor using a simple loop and BULK COL...

Collections Oracle10g Presentation Patrick Barel Patch72

0 3

4.8 years ago Patch72

Script

Using a record type constructor to populate a nested table on 11g

To use a function as record type constructor allows you to insert values on array directly instead o...

record type, record, record constructor, 11g, collections. nested table

0 3

6.8 years ago Victor Augusto

Script

Simple String Tracker Package: String Indexed Collection Example

This script offers first an implementation of a "Have I used this string?" API using an integer-inde...

collection,string,index,array,sparse,string index

0 3

4.3 years ago Steven Feuerstein

Script

Object_Collection_Comparisons_Part1

It is not (yet) possible to execute these scripts within LiveSQL. I offer them to you here so that...

Object_Collection_Comparisons_Part1

0 3

8.8 years ago Json PGA Memory Usage