Oracle 12c New Features for Developers

1. Oracle 12c New Features for Developers By Database star www.databasestar.com

2. Oracle 12c • Released in 2013 • Contains a lot of new features • Some are useful for DBAs, some are useful for developers, some for both • As a developer, what do you need to know? • I wrote an entire post on it: • http://www.completeitprofessional.com/oracle-12c-new-features-for-developers • These slides explain all of those features

3. Increased column size limits • VARCHAR2, NVARCHAR2, and RAW are larger than previous Oracle versions

4. Increased column size limits • How can you use the new sizes? • Change the setting called MAX_STRING_SIZE within the init.ora file • STANDARD – old sizes • EXTENDED – new sizes

5. APPROX_COUNT_DISTINCT • New function – APPROX_COUNT_DISTINCT • Gives you an approximate count of records • Faster than COUNT • Not 100% accurate but pretty close

6. Row Limiting with Top N • Getting the top N rows can be hard in Oracle • In Oracle 12c, you can use new syntax • FETCH FIRST 10 ROWS ONLY – shows only the first 10 rows

7. Row Limiting with Top N SELECT first_name, last_name, date_of_birth FROM student ORDER BY date_of_birth FETCH FIRST 10 ROWS ONLY;

8. Pattern Matching • Pattern matching is easier in Oracle 12c • Uses the MATCH_RECOGNIZE keyword

9. Pattern Matching Syntax SELECT columns FROM table MATCH_RECOGNIZE ( PARTITION BY  ... ORDER BY ... MEASURES ... PATTERN... DEFINE... ) ORDER BY col1...

10. JSON in Database • Oracle 12c now support JSON in database columns • You can query directly inside the column data

11. JSON Example SELECT b.document.businessName, b.document.address.streetNumber, b.document.address.streetName FROM businesses b { “businessName”:”Cars Galore”, “address”:{ “streetNumber”:”14”, “streetName”:”Main Street”, “city”:”Denver”, “state”:”Colorado”, “country”:”USA”}, “businessIndustry”:”Automotive” }

12. Lateral Clause for Inline Views • Normally you can’t refer to columns outside an inline view from within the inline view • With a LATERAL clause, you can

13. Lateral Clause Example SELECT first_name, last_name, school_name FROM student s, LATERAL (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id)

14. CROSS APPLY Clause • Similar to Lateral • Variant of the CROSS JOIN • Right side of the keyword can reference the column on the left

15. CROSS APPLY Example SELECT first_name, last_name, school_id, school_name FROM student s, CROSS APPLY (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id AND sc.school_state = ‘California’)

16. OUTER APPLY Clause • Similar to CROSS APPLY • More like a LEFT OUTER JOIN

17. OUTER APPLY Example SELECT first_name, last_name, school_id, school_name FROM student s, OUTER APPLY (SELECT school_name FROM school sc WHERE sc.school_id = s.school_id AND sc.school_state = ‘California’)

18. Partial Join Evaluation • New optimisation type • Part of the optimisation process • You might see it in the Explain Plan • Partial Join Evaluation • Also called PJE

19. Cascading Truncate • Have you tried to TRUNCATE a table that had other records referring to it, and got an error about foreign keys? • With Oracle 12c, you can run a TRUNCATE CASCADE • This will skip this error and delete the rows that refer to it • Only works if the foreign key is defined as ON DELETE CASCASE

20. Cascading Truncate Example TRUNCATE TABLE parent_table_name CASCADE;

21. Pluggable Databases • One of the main features in Oracle 12c • A “root” database is created • “Seed” database is a template for creating other databases • “Pluggable databases” are where the data is stored • All inside the root

22. Why pluggable databases? • Easier maintenance • Easier implementation of new databases – just copy the seed • Easier to move to the cloud

23. Invisible Columns • Columns that do not appear in the table definition or SELECT * statements • Use the INVISIBLE keyword when defining a column

24. Invisible Indexes • Allows more than one index on a column at one time • Create index and add the INVISIBLE keyword

25. Identity Columns • Set a column to automatically generate a value • Similar to AUTO_INCREMENT in other databases • Add GENERATED AS IDENTITY to column definition when creating a table

26. Default Values • Simplify data entry • Use sequences as the default values • Or use default values only when a NULL is specified

27. Session Sequences • Sequences currently keep their values for the database for all sessions • Session sequences are new • They let you retain the value only for the session • Not very useful for primary keys, but there are other uses

28. Sequence KEEP and NOKEEP • KEEP and NOKEEP are keywords for creating sequences • KEEP retains the NEXTVALUE value for replays during Application Continuity • NOKEEP will not retain the NEXTVALUE for these replays • NOKEEP is the default

29. Data Redaction • You can hide certain fields in certain ways • Replace characters with spaces or numbers • Or, change part of the information • Good security feature

30. Grant Roles to PL/SQL Programs Only • Previously, you had to grant user access to the PL/SQL program and the table • Now, just grant user access to the PL/SQL program • Program will still access the table • Makes it more secure

31. UTL_CALL_STACK Package • Improvements to the call stack • Use the UTL_CALL_STACK package to get information about your call stack

32. PL/SQL ACCESSIBLE BY Clause • Allows you to specify which packages can access other packages • Helps with security • Simplifies package definition if you want to implement this • Add the words ACCESSIBLE BY to the package when defining

33. PL/SQL Table Operator • Before 12c, you could only use the TABLE operator in some situations • Now, you can use them with locally defined types

34. WITH Clause and PL/SQL Functions • Now you can define PL/SQL functions and procedures inside a WITH clause • Likely improves run time • Object is not created and stored in the database

35. Online DDL Statements • DDL normally locks tables • Some statements can now be run in “online” mode, which does not impact any DML that is running • Statements relate to indexes, constraints, and columns

36. DDL Logging • You can enable DDL logging • Many DDL statements are captured • CREATE/ALTER/DROP/TRUNCATE TABLE • CREATE/ALTER/DROP PACKAGE/FUNCTION/VIEW/SYNONYM/SEQUENCE • DROP USER

37. Bequeath Option • Allows you to specify which user’s privileges will be used when running a statement • Specify either invoker’s or definer’s rights • Invoker’s rights – the person running the statement • Definer’s rights – the person who created the object

38. Period Definition • Allows you to easily specify which records are valid at a particular date • Use the PERIOD clause when creating a table • You can then use the PERIOD clause in SELECT queries

39. Conclusion • Many new features in Oracle 12c • These are all of the features I think are helpful for new developers • Find out more here: http://www.databasestar.com/oracle-12c-new-features-for-developers