Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Oracle Database 19c for Developers

Chris
January 01, 2020

Oracle Database 19c for Developers

An overview of SQL and PL/SQL features from 12.2 -> 19c to help you build better, faster, more secure data-driven applications.

Chris

January 01, 2020
Tweet

More Decks by Chris

Other Decks in Technology

Transcript

  1. Oracle Database 19c for Developers Thu 11th June 12:15 –

    13:00 blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Chris Saxon, @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  2. Oracle Database 19c for Developers blogs.oracle.com/sql youtube.com/c/TheMagicofSQL asktom.oracle.com Chris Saxon,

    @ChrisRSaxon & @SQLDaily blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  3. The following is intended to outline our general product direction.

    It is intended for information purposes only, and may not be incorporated into any contract. It is not a commitment to deliver any material, code, or functionality, and should not be relied upon in making purchasing decisions. The development, release, timing, and pricing of any features or functionality described for Oracle’s products may change and remains at the sole discretion of Oracle Corporation. Statements in this presentation relating to Oracle’s future plans, expectations, beliefs, intentions and prospects are “forward-looking statements” and are subject to material risks and uncertainties. A detailed discussion of these factors and other risks that affect our business is contained in Oracle’s Securities and Exchange Commission (SEC) filings, including our most recent reports on Form 10-K and Form 10-Q under the heading “Risk Factors.” These filings are available on the SEC’s website or on Oracle’s website at http://www.oracle.com/investor. All information in this presentation is current as of September 2019 and Oracle undertakes no duty to update any statement in light of new information or future events. Safe Harbor blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  4. select add_one ( 1 ) from top_n ( employees, 5

    ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  5. select add_one ( 1 ) from top_n ( employees, 5

    ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon select 1 + 1 from employees fetch first 5 rows only
  6. select department_id, sum ( salary ) from employees group by

    department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  7. create view department_salaries as select department_id, sum ( salary )

    from employees group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  8. select department_id, sum ( salary ) from employees where job_id

    = job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  9. create view department_salaries ( job varchar2 ) as select department_id,

    sum ( salary ) from employees where job_id = job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  10. select department_id, sum ( salary ) from employees where job_id

    = dept_job_salaries.job group by department_id blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  11. return ' select department_id, sum ( salary ) from employees

    where job_id = dept_job_salaries.job group by department_id'; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  12. create function dept_job_salaries ( job varchar2 ) return varchar2 sql_macro

    is begin return ' select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  13. create function dept_job_salaries ( job varchar2 ) return varchar2 sql_macro

    is begin return ' select department_id, sum ( salary ) from employees where job_id = dept_job_salaries.job group by department_id'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) Find/replace
  14. select * from dept_job_salaries ( 'MANAGER' ); select department_id, sum

    ( salary ) from employees where job_id = 'MANAGER' group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  15. select * from dept_job_salaries ( :job ); select department_id, sum

    ( salary ) from employees where job_id = :job group by department_id; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  16. select * from top_n.tab fetch first top_n.num_rows rows only blogs.oracle.com/sql

    www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  17. create function top_n ( num_rows number, tab dbms_tf.table_t ) return

    varchar2 sql_macro is begin return 'select * from top_n.tab fetch first top_n.num_rows rows only'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6)
  18. create function top_n ( num_rows number, tab dbms_tf.table_t ) return

    varchar2 sql_macro is begin return 'select * from top_n.tab fetch first top_n.num_rows rows only'; end; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) Find/replace
  19. from top_n ( 5, employees ) from top_n ( :bind,

    jobs ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only
  20. from top_n ( 5, employees ) from top_n ( :bind,

    jobs ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (19.6) from employees fetch first 5 rows only from jobs fetch first :bind rows only
  21. alter table t_ext location ( '…20190102.csv' ); alter table t_ext

    location ( '…20190101.csv' ); alter table t_ext location ( '…20190103.csv' ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon sales_20190101.csv sales_20190102.csv sales_20190103.csv select * from t_ext;
  22. select * from ext_tab external modify ( [ default directory

    ] [ location ] [ (bad|log|discard)file parameters ] [ reject limit ] ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
  23. select * from external ( ( <cols> ) <your external

    table def> ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  24. select * from external ( ( c1 int, c2 int

    ) default directory dir location ( 'file.csv' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  25. create table t_ext ( … ) organization external ( type

    oracle_loader default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  26. create table t_ext ( … ) organization external ( type

    oracle_datapump default directory tmp location ( 't.dmp' ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  27. create table t_ext ( … ) organization external ( type

    oracle_datapump default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Not expdp/impdp compatible!
  28. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon exp.dmp create table … ( … )

    … ( … oracle_datapump … ) as select * from … select * from external ( … )
  29. create table partitioned_ext ( … ) organization external ( …

    ) partition by range ( … ) ( partition p201901 values less than ( … ) location ( '…201901.dmp' ), partition p201902 values less than ( … ) location ( '…201902.dmp' ) ); blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  30. create view sales_all as select * from sales_ext union all

    select * from sales blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  31. create table t_ext ( … ) external partition attributes (

    … ) partition by range ( … ) ( partition ext values less than ( … ) external location ( '….dmp' ), partition int values less than ( … ), … ); Internal is the default blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  32. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hybrid Partitioned Tables Restrictions alter table …

    exchange partition … with table …; ORA-14354: operation not supported for a hybrid-partitioned table Coming! Backport planned
  33. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon Hybrid Partitioned Tables Restrictions create table t_ext

    ( … ) external partition attributes ( … ) partition by hash ( … ) partitions 4; ORA-30657: operation not supported on external organized table Range & List only NOT interval or automatic
  34. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14354: operation not supported for a hybrid-partitioned

    table create index hybrid_i on hybrid ( cols ); Hybrid Partitioned Tables Restrictions
  35. blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14226: unique index may not be PARTIAL

    create unique index hybrid_ui on hybrid ( cols ) indexing partial; Hybrid Partitioned Tables Restrictions
  36. Hybrid Partitioned Tables Restrictions blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-14354: operation not

    supported for a hybrid-partitioned table alter table hybrid add constraint ck check ( c1 > 0 );
  37. create table t_ext ( … ) organization external ( type

    oracle_loader default directory tmp location ( 't.dmp' ) ) as select * from … ; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon ORA-30657: operation not supported on external organized table
  38. SQL Developer & SQLcl set sqlformat csv spool file.csv select

    * from hr.employees; "EMPLOYEE_ID","FIRST_NAME","LAST_NAME",… 100,"Steven","King",… 101,"Neena","Kochhar",… blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  39. select owner, listagg ( object_type, ',' ) within group (

    order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  40. select owner, listagg ( object_type, ',' on overflow truncate )

    within group ( order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  41. SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS, SYSTEM

    …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  42. SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS,...(48638) SYSTEM

    …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  43. listagg ( things, ',' [ on overflow (truncate|error) ] [

    text ] [ (with|without) count ] ) within group ( order by cols ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  44. SCOTT …,TABLE,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS,JAVA CLASS,...(48638) SYSTEM

    …,VIEW,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  45. SCOTT …,TABLE SH …,TABLE PARTITION,VIEW SYS …,JAVA CLASS, SYSTEM …,VIEW

    blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  46. select owner, listagg ( distinct object_type, ',' on overflow truncate

    ) within group ( order by object_type ) from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  47. SCOTT INDEX,TABLE SH …,TABLE,TABLE PARTITION,VIEW SYS …,UNIFIED AUDIT POLICY,VIEW,WINDOW SYSTEM

    …,TABLE PARTITION,TYPE,VIEW blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  48. json_array* => [ ] json_object* => { } [ ...

    ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} [ ... ] {...} { ... } json_*agg => json_*(non-agg) => [ ... ] blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (12.2)
  49. json_array json_arrayagg json_object json_objectagg blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon (18c) clob blob

    timestamp tz intervals raw binary_double binary_float nvarchar2 clob blob Improved Data Type Support
  50. select owner, json_arrayagg ( object_name order by object_name returning clob

    ) objs from dba_objects group by owner; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  51. substr ( objs, 2, length ( objs ) - 2

    ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  52. Plus other JSON Goodies blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon JSON_mergepatch JSON_equal JSON

    to SQL object conversion JSON_serialize JSON search index JSON Data Guide treat as JSON GeoJSON Materialized view support Partitioning support
  53. select count (*) from t; ----------------------------------- | 0 | SELECT

    STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  54. select /*+ index ( t some_index ) */ count (*)

    from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  55. select /*+ index ( t some_index ) */ count (*)

    from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  56. select * from dbms_xplan.display_cursor ( null, null, 'BASIC LAST HINT_REPORT'

    ); PLAN_TABLE_OUTPUT EXPLAINED SQL STATEMENT: ------------------------ … blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  57. Hint Report (identified by operation id / Query Block Name

    / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t some_index ) / index specified in the hint doesn't exist blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  58. Hint Report (identified by operation id / Query Block Name

    / Object Alias): Total hints for statement: 1 (U - Unused (1)) --------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t some_index ) / index specified in the hint doesn't exist blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  59. select /*+ index ( t ( c1 ) ) */

    count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  60. select /*+ index ( t ( c1 ) ) */

    count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS FULL| T | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  61. Hint Report (identified by operation id / Query Block Name

    / Object Alias): Total hints for statement: 1 (U - Unused (1)) ------------------------------------------- 2 - SEL$1 / T@SEL$1 U - index ( t ( c1 ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  62. info t NAME DATA TYPE NULL … C1 NUMBER Yes

    C2 VARCHAR2(400 BYTE) Yes C3 DATE Yes blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon No mandatory columns!
  63. select /*+ index ( t ( c1 ) ) */

    count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN | I | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  64. select /*+ index ( t ( c1 ) ) */

    count (*) from t; ----------------------------------- | 0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | INDEX FULL SCAN | I | ----------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  65. Hint Report (identified by operation id / Query Block Name

    / Object Alias): Total hints for statement: 1 ------------------------------------------- 2 - SEL$1 / T@SEL$1 - index ( t ( c1 ) ) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  66. -------------------------------------------- | Id | Operation | Name | -------------------------------------------- |

    0 | SELECT STATEMENT | | | 1 | SORT AGGREGATE | | | 2 | TABLE ACCESS INMEMORY FULL| T | -------------------------------------------- blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  67. 2 - SEL$1 / T@SEL$1 U - index ( t

    ( c1 ) ) / rejected by IGNORE_OPTIM_EMBEDDED_HINTS blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  68. • thou shalt not have v$ access! (pic) blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL

    @ChrisRSaxon Thou shalt not have v$ access! Ryan McGuire / Gratisography
  69. conn dev/dev select /*+ monitor */* from dual; select dbms_sqltune.report_sql_monitor

    () from dual; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  70. DBMS_SQLTUNE.REPORT_SQL_MONITOR() SQL Monitoring Report SQL Text ------------------------------ select /*+ monitor

    /* from dual Global Information ------------------------------ blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  71. Status : DONE (ALL ROWS) Instance ID : 1 Session

    : DEV (48:59561) SQL ID : d0h4256v0m8fv SQL Execution ID : 16777216 Execution Started : 09/10/2019 08:25:39 First Refresh Time : 09/10/2019 08:25:39 Last Refresh Time : 09/10/2019 08:25:39 Duration : .000164s blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  72. SQL Monitor Reports for Devs Reports for your user… …

    and only your user Standard monitoring rules Licensing! blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon
  73. select sid || ',' || serial# into sess from v$session

    where username = 'CHRIS' and action = '18 about 18'; execute immediate 'alter system cancel sql ''' || sess || ''''; blogs.oracle.com/sql www.youtube.com/c/TheMagicOfSQL @ChrisRSaxon