{"id":73230,"date":"2013-07-25T11:18:11","date_gmt":"2013-07-25T11:18:11","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-database-12c-new-features-part-4\/"},"modified":"2021-07-14T13:07:39","modified_gmt":"2021-07-14T13:07:39","slug":"oracle-database-12c-new-features-part-4","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-database-12c-new-features-part-4\/","title":{"rendered":"Oracle Database 12c New Features \u2013 Part 4"},"content":{"rendered":"<p>Parts <a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-i\/\" title=\"Oracle Database 12c New Features \u2013 Part I\">1<\/a>,<a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-2\/\" title=\"Oracle Database 12c New Features \u2013 Part 2\">2<\/a> &amp; <a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-3\/\" title=\"Oracle Database 12c New Features \u2013 Part 3\">3<\/a> focusssed more on the most useful improvements and enhancements of Database administration: Performance Tuning, RMAN, Data Guard,\u00a0 ASM and Clusterware. This part of the series will mainly focus on some of the new features that are useful to developers.<\/p>\n<p><a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-i\/\" title=\"Oracle Database 12c New Features \u2013 Part I\">Read Oracle Database 12c New Features \u2013 Part 1<\/a><br \/>\n<a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-2\/\" title=\"Oracle Database 12c New Features \u2013 Part 2\">Read Oracle Database 12c New Features \u2013 Part 2<\/a><br \/>\n<a href=\"https:\/\/allthingsoracle.com\/oracle-database-12c-new-features-part-3\/\" title=\"Oracle Database 12c New Features \u2013 Part 3\">Read Oracle Database 12c New Features \u2013 Part 3<\/a><\/p>\n<p><strong>Part 4 covers:<\/strong><\/p>\n<ul>\n<li>How to truncate a master table while child tables contain data<\/li>\n<li>Limiting ROWS for Top-N query results<\/li>\n<li>Miscellaneous SQL*Plus enhancements<\/li>\n<li>Session level sequences<\/li>\n<li>WITH clause improvements<\/li>\n<li>Extended data types<\/li>\n<\/ul>\n<h2>Truncate table CASCADE<\/h2>\n<p>In the previous releases, there wasn&#8217;t a direct option provided to truncate a master table while it is referred to by the child tables and child records exist. The <strong>TRUNCATE TABLE<\/strong> with <strong>CASCADE<\/strong> option in 12c truncates the records in the master table and automatically initiates recursive truncate on child tables too, subject to foreign key reference as <strong>DELETE ON CASCADE<\/strong>. There is no <strong>CAP<\/strong> on the number of recursive levels as it will apply on all child, grand child and great grandchild etc.<\/p>\n<p>This enhancement gets rid of the prerequisite to truncate all child records before truncating a master table. The new <strong>CASCADE<\/strong> clause can also be applied on table partitions and sub-partitions etc.<\/p>\n<pre>\r\nSQL&gt; TRUNCATE TABLE &lt;table_name&gt; CASCADE;\r\n\r\nSQL&gt; TRUNCATE TABLE &lt;table_name&gt; PARTITION &lt;partition_name&gt; CASCADE;\r\n<\/pre>\n<p>An ORA-14705 error will be thrown if no <strong>ON DELETE CASCADE<\/strong> option is defined with the foreign keys of the child tables.<\/p>\n<h2>ROW limiting for Top-N result queries<\/h2>\n<p>There are various indirect approaches\/methods exist to fetch Top-N query results for top\/bottom rows in the previous releases. In 12c, retrieving Top-N query results for top\/bottom rows simplified and become straight forward with the new <strong>FETCH FIRST|NEXT|PERCENT<\/strong> clauses.<\/p>\n<p>In order to retrieve top 10 salaries from EMP table, use the following new SQL statement:<\/p>\n<pre>\r\nSQL&gt; SELECT eno,ename,sal FROM emp ORDER BY SAL DESC\r\n\t\t\t\tFETCH FIRST 10 ROWS ONLY;\r\n<\/pre>\n<p>The following example fetches all similar records of Nth row. For example, if the 10th row has salary of 5000 value, and there are other employees whose salary matches with the Nth value, the will also be fetched upon mentioning <strong>WITH TIES<\/strong> clause.<\/p>\n<pre>\r\nSQL&gt; SELECT eno,ename,sal FROM emp ORDER BY SAL DESC\r\n\t\t\t\tFETCH FIRST 10 ROWS ONLY WITH TIES;\r\n<\/pre>\n<p>The following example limits the fetch to 10 per cent from the top salaries in the EMP table:<\/p>\n<pre>\r\nSQL&gt; SELECT eno,ename,sal FROM emp ORDER BY SAL DESC\r\n\t\t\t\tFETCH FIRST 10 PERCENT ROWS ONLY;\r\n<\/pre>\n<p>The following example offsets the first 5 rows and will display the next 5 rows from the table:<\/p>\n<pre>\r\nSQL&gt; SELECT eno,ename,sal FROM emp ORDER BY SAL DESC\r\n\t\t\t\tOFFSET 5 ROWS FETCH NEXT 5 ROWS ONLY;\r\n<\/pre>\n<p>All these limits can be very well used within the PL\/SQL block too.<\/p>\n<pre>\r\nBEGIN\r\n\t\tSELECT sal BULK COLLECT INTO sal_v FROM EMP\r\n\t\t\t\tFETCH FIRST 100 ROWS ONLY;\r\nEND;\r\n<\/pre>\n<h2>Miscellaneous SQL*Plus enhancements<\/h2>\n<p><strong>Implicit Results on SQL*Plus<\/strong>: SQL*Plus in 12c returns results from an implicit cursor of a PL\/SQL block without actually binding it to a RefCursor. The new <strong>dbms_sql.return_result<\/strong> procedure will return and formats the results of <strong>SELECT<\/strong> statement query specified within PL\/SQL block. The following code descries the usage:<\/p>\n<pre>\r\nSQL&gt; CREATE PROCEDURE mp1\r\n                        as\r\n\t\t\tres1 sys_refcursor;\r\nBEGIN\r\n\t\topen res1 for SELECT eno,ename,sal FROM emp;\r\n\t\tdbms_sql.return_result(res1);\r\nEND;\r\n\r\nSQL&gt; execute mp1;\r\n<\/pre>\n<p>When the procedure is executed, it return the formatted rows on the SQL*Plus.<\/p>\n<p><strong>Display invisible columns<\/strong>: In Part 1 of this series, I have explained and demonstrated about invisible columns new feature. When the columns are defined as invisible, they won\u2019t be displayed when you describe the table structure. However, you can display the information about the invisible columns by setting the following on the SQL*Plus prompt:<\/p>\n<pre>\r\nSQL&gt; SET COLINVISIBLE ON|OFF\r\n<\/pre>\n<p>The above setting is only valid for <strong>DESCRIBE<\/strong> command. It has not effect on the <strong>SELECT<\/strong> statement results on the invisible columns.<\/p>\n<h2>Session level sequences<\/h2>\n<p>A new <strong>SESSION<\/strong> level database sequence can be created now in 12c to support the session level sequence values. These types of sequences are most useful and suitable on global temporary tables that have session level existence.<\/p>\n<p>Session level sequences produce a unique range of values that are limited within the session, not across the sessions. Once the session ends, the state of the session sequences also goes away. The following example explains creating a session level sequence:<\/p>\n<pre>\r\nSQL&gt; CREATE SEQUENCE my_seq START WITH 1 INCREMENT BY 1 SESSION;\r\n\r\nSQL&gt; ALTER SEQUENCE my_seq GLOBAL|SESSION;\r\n<\/pre>\n<p>The <strong>CACHE<\/strong>, <strong>NOCACHE<\/strong>, <strong>ORDER<\/strong> or <strong>NOORDER<\/strong> clauses are ignored for <strong>SESSION<\/strong> level sequences.<\/p>\n<h2>WITH clause improvements<\/h2>\n<p>In 12c, you can have faster running PL\/SQL function\/procedure in SQL, that are defined and declared within the <strong>WITH<\/strong> clause of SQL statements. The following examples demonstrate how to define and declare a procedure or function within the <strong>WITH<\/strong> clause:<\/p>\n<pre>\r\nWITH\r\n\t\tPROCEDURE|FUNCTION test1 (\u2026)\r\n\t\tBEGIN\r\n\t\t\t\t&lt;logic&gt;\r\n\t\tEND;\r\nSELECT &lt;referece_your_function|procedure_here&gt; FROM table_name;\r\n\/\r\n<\/pre>\n<p>Although you can\u2019t use the <strong>WITH<\/strong> clause directly in the PL\/SQL unit, it can be referred through a dynamic SQL within that PL\/SQL unit.<\/p>\n<h2>Extended data types<\/h2>\n<p>In 12c, the data type <strong>VARCHAR2<\/strong>, <strong>NAVARCHAR2<\/strong>, and RAW size will support up to 32,767 bytes in contrast to 4,000 and 2,000 in the earlier releases. The extended character size will reduce the use of going for LOB data types, whenever possible. In order to enable the extended character size, you will have to set the <strong>MAX_STRING_SIZE<\/strong> initialization database parameter to <strong>EXTENDED<\/strong>.<\/p>\n<p>The following procedure need to run to use the extended data types:<\/p>\n<ol>\n<li>Shutdown the database<\/li>\n<li>Restart the database in UPGRADE mode<\/li>\n<li>Modify the parameter: ALTER SYSTEM SET MAX_STRING_SIZE=EXTENDED;<\/li>\n<li>Execute utl32k.sql as sysdba : SQL&gt; @?\/rdbms\/admin\/utl32k.sql<\/li>\n<li>Shutdown the database<\/li>\n<li>Restart the database in READ WRITE mode<\/li>\n<\/ol>\n<p>In contrast to LOB data types, the extended data types columns in ASSM tablespace management are stored as SecureFiles LOBs, and in non-ASSM tablespace management they stored as BasciFiles LOBs.<\/p>\n<p><strong>Note:<\/strong> Once modified, you can\u2019t change the settings back to <strong>STANDARD<\/strong>.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>Parts 1,2 &amp; 3 focusssed more on the most useful improvements and enhancements of Database administration: Performance Tuning, RMAN, Data Guard,\u00a0 ASM and Clusterware. This part of the series will mainly focus on some of the new features that are useful to developers. Read Oracle Database 12c New Features \u2013 Part 1 Read Oracle Database 12c New Features \u2013 Part&hellip;<\/p>\n","protected":false},"author":316192,"featured_media":0,"comment_status":"open","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"_acf_changed":false,"footnotes":""},"categories":[143533],"tags":[],"coauthors":[],"class_list":["post-73230","post","type-post","status-publish","format-standard","hentry","category-oracle-databases"],"acf":[],"_links":{"self":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73230","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/users\/316192"}],"replies":[{"embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/comments?post=73230"}],"version-history":[{"count":1,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73230\/revisions"}],"predecessor-version":[{"id":91713,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/posts\/73230\/revisions\/91713"}],"wp:attachment":[{"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/media?parent=73230"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/categories?post=73230"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/tags?post=73230"},{"taxonomy":"author","embeddable":true,"href":"https:\/\/www.red-gate.com\/simple-talk\/wp-json\/wp\/v2\/coauthors?post=73230"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}