Category Archives: PL/SQL

ODC Appreciation Day: Collections in SQL

Oren Nakdimon Leave a Reply

Here’s my contribution to the ODC Appreciation Day. Overview Last week I had the privilege to participate in the EOUC Database ACES Share Their Favorite Database Things session at Oracle OpenWorld, so I think that the best topic to write about, as part of the ODC Appreciation Day, is the one I talked about in […]

Continue reading

RETURNING INTO – Enhancement Suggestion

Oren Nakdimon 3 Replies

The RETURNING INTO clause is one of my favorite features. It returns data from the rows that have been affected by the DML statement, and as I wrote in this previous post: For INSERT it returns the after-insert values of the new row’s columns. For UPDATE it returns the after-update values of the affected rows’ […]

Continue reading

PL/SQL in SQL in View in SQL in PL/SQL

I presented “Write Less (Code) With More (Oracle 12c New Features)” yesterday at OGh Tech Experience 2017. One of the features I talked about was PL/SQL in the WITH Clause. One of the restrictions of this feature is that you cannot embed a static SQL query, that contains PL/SQL in the WITH clause, in PL/SQL […]

Continue reading

RETURNING INTO

Oren Nakdimon 2 Replies

The RETURNING INTO clause is one of my favorite PL/SQL features. It allows to write less code, improves readability and reduces context switches between PL/SQL and SQL. In this post I’d like to highlight some less-known characteristics of the RETURNING INTO clause and emphasize differences that exist when it is used in different DML statements. […]

Continue reading

COLLECT DISTINCT in PL/SQL Works in Oracle 12.2

Oren Nakdimon 1 Reply

About a year ago I wrote the post Subtleties – Part 1 (SQL and PL/SQL). I wrote there: 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 […]

Continue reading

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

Note: I wrote this post in May 2016, when the latest released version of Oracle was 12.1. As of Oracle 12.2 COLLECT DISTINCT is supported in PL/SQL. 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 […]

Continue reading