Nolem的部落格

K文件看倒不錯的enhance , memo 一下~

1.

db 12.2 Quoted User-Defined Identifiers

A quoted user-defined identifier is enclosed in double quotation marks. Between the double quotation marks, any characters from the database character set are allowed except double quotation marks, new line characters, and null characters. For example, these identifiers are acceptable:

Example a

"X+Y"
"last name"
"on/off switch"
"employee(s)"
"*** header info ***"

Example b Reserved Word as Quoted User-Defined Identifier

DECLARE
  "BEGIN" varchar2(15) := 'UPPERCASE';
  "Begin" varchar2(15) := 'Initial Capital';
  "begin" varchar2(15) := 'lowercase';
BEGIN
  DBMS_Output.Put_Line("BEGIN");
  DBMS_Output.Put_Line("Begin");
  DBMS_Output.Put_Line("begin");
END;
/

2.變數宣告長度放寬到128 BYTES ,這倒是好消息

3.JSON SUPPORT 

4.VALIDATE_CONVERSION,以前要判斷資料有異常無法轉換TYPE 的都比較麻煩,現在要找異常資料就快多了

The following statement returns 0, because the specified value cannot be converted to BINARY_FLOAT:

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT)
  FROM DUAL;

The following statement returns 1, because the specified number format model enables the value to be converted to BINARY_FLOAT:

SELECT VALIDATE_CONVERSION('$29.99' AS BINARY_FLOAT, '$99D99')
  FROM DUAL;

5.DEPRECATE Pragma ,這對進行版本更新時,不知道有哪些程式用到舊版程式,強制停用很不錯

停用版本宣告

PACKAGE pack5 AS
PRAGMA DEPRECATE(pack5 , ’Package pack5 has been deprecated, use new_pack5 instead.’);
 PROCEDURE foo;
 PROCEDURE bar;
END pack5;

接著有引用pack5的都會有警告訊息

SP2-0810: Package Body created with compilation warnings

Errors for PACKAGE BODY PACK6:
4/10     PLW-06020: reference to a deprecated entity: PACK5 declared in unit PACK5[1,9].  
         Package pack5 has been deprecated, use new_pack5 instead

6.MV ENHANCE add ON STATEMENT Refresh 

This example creates a materialized view sales_mv_onstat that uses the ON STATEMENT refresh mode and is based on the sh.sales, sh.customers, and sh.products tables. The materialized view is automatically refreshed when a DML operation is performed on any of the base tables. No commit is required after the DML operation to refresh the materialized view.

CREATE MATERIALIZED VIEW sales_mv_onstat
REFRESH FAST ON STATEMENT USING TRUSTED CONSTRAINT
AS
SELECT s.rowid sales_rid, c.cust_first_name first_name, c.cust_last_name last_name,
       p.prod_name prod_name, 
    s.quantity_sold quantity_sold, s.amount_sold amount_sold
FROM sh.sales s, sh.customers c, sh.products p
WHERE s.cust_id = c.cust_id and s.prod_id = p.prod_id;

7.LISTAGG NEW ENHANCE  OVERFLOW control

LISTAGG ( [ALL] <measure_column> [,<delimiter>] [ON OVERFLOW TRUNCATE [truncate_literal] | ON OVERFLOW ERROR  [WITH | WITHOUT COUNT]]) 
        WITHIN GROUP (ORDER BY <oby_expression_list>)

8.OCI Support for Distributed LOBs

   已前跨DB處理lob 非常麻煩,現在總算簡單了...