Monthly Archives: May 2016

Subtleties – Part 2 (Nested Tables and Varrays)

Oren Nakdimon 1 Reply

In Part 1 we saw that the SQL function COLLECT with the DISTINCT option is not natively supported in PL/SQL. One suggested workaround was to apply the SET function on the result of the “simple” COLLECT function (without the DISTINCT option). This works fine, in both SQL and PL/SQL, as long as the collection type […]

Continue reading

Subtleties – Part 1 (SQL and PL/SQL)

Oren Nakdimon 3 Replies

Almost every valid SQL statement (i.e., that is executed successfully by the SQL engine) can be embedded successfully as a static SQL in PL/SQL. Almost, but not every statement. One example is the COLLECT aggregate function with the DISTINCT option. To demonstrate it I’ll use the PROJECT_ASSIGNMENTS table, which contains assignments of people to projects. […]

Continue reading