Hi!

Oracle12c’s SQL language has many changes.  I’ll document some of the changes here.

There are some impressive new syntax available now.

Views now have visible and invisible columns.  I’m not a fan of removing columns because of the fear of the only report that uses it that runs New Years Eve will now fail. Big Smile   Views also work better with invokers rights, pointing to different tables than the definer or the invoker privileges allow.

Oracle12 allows for pattern matching across a series of rows or a percentage of rows.  I’m not comfortable posting examples from the Oracle documentation here so if you don’t have the Oracle documentation at your finger tips…go to Oracle.com, put ‘Documentation’ into the search window and download the .zip file from there.  Then look up these features for working examples.

This feature called Fetch First / Fetch Next is a better solution than the top N-tier solution using an inline view.  This syntax allows you to return the top N rows based on SQL syntax or for you to return the top percentage of rows based on SQL syntax.

SELECT EMPNO, ENAME
  FROM EMP
  ORDER BY EMPNO
  OFFSET 5 ROWS FETCH FIRST 5 ROWS ONLY;

There is also some new join syntax: ANSI CROSS JOIN and ANSI LEFT OUTERJOIN

Here is an update to inline views to allow columns from the inline view to be accessed by the outer query.  The lateral clause allows for rows from the multi column select of EMP to be accessed by the inline view.

Oracle11 or before (fails):

SELECT * FROM emp e,
              (SELECT * FROM dept d

                WHERE e.deptno = d.deptno);

ORA-00904: "E"."DEPTNO": invalid identifier

Oracle12 – works!

SELECT * FROM emp e,
              LATERAL(SELECT * FROM dept d

                       WHERE e.deptno = d.deptno);

Next week, I’ll expand on these options and how they fit in with some new PL/SQL options.

Dan Hotka
Oracle ACE Director
Instructor/Author/CEO