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. 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 OUTERJOINHere 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 identifierOracle12 – 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 HotkaOracle ACE DirectorInstructor/Author/CEO
Hi! Oracle12c’s PL/SQL has many changes. I’ll document some of the changes here. I’m