Category Archives: PL/SQL

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 1 Reply

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

The “Control Freak Trigger” Design Pattern

Oren Nakdimon 1 Reply

Suppose that every time we add records into the T1 table we have to do some additional stuff. One option to implement this is by using an AFTER INSERT trigger that will perform this additional stuff, but I really dislike this option (mainly because the code becomes hidden in a way, and there may be […]

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