SQL> select checksum(country_name), checksum(distinct country_name)
  2       , count(*), count(distinct country_name) 
  3  from sh.countries;

CHECKSUM(COUNTRY_NAME) CHECKSUM(DISTINCTCOUNTRY_NAME)   COUNT(*) COUNT(DISTINCTCOUNTRY_NAME)
---------------------- ------------------------------ ---------- ---------------------------
                117432                         117432         23                          23

SQL> 
SQL> select checksum(country_region), checksum(distinct country_region)
  2       , count(*), count(distinct country_region) 
  3  from sh.countries;

CHECKSUM(COUNTRY_REGION) CHECKSUM(DISTINCTCOUNTRY_REGION)   COUNT(*) COUNT(DISTINCTCOUNTRY_REGION)
------------------------ -------------------------------- ---------- -----------------------------
                  683368                           415340         23                             6

SQL> WITH nums AS (
  2     select 3 as n from dual union all
  3     select 5 as n from dual)
  4  --
  5  SELECT sum(n), BIT_AND_AGG(n), BIT_OR_AGG(n), BIT_XOR_AGG(n) FROM nums;

    SUM(N) BIT_AND_AGG(N) BIT_OR_AGG(N) BIT_XOR_AGG(N)
---------- -------------- ------------- --------------
         8              1             7              6

SQL> select checksum(country_region), checksum(distinct country_region)
  2       , count(*), count(distinct country_region) 
  3  from sh.countries;

CHECKSUM(COUNTRY_REGION) CHECKSUM(DISTINCTCOUNTRY_REGION)   COUNT(*) COUNT(DISTINCTCOUNTRY_REGION)
------------------------ -------------------------------- ---------- -----------------------------
                  683368                           415340         23                             6

SQL> select checksum(country_region), checksum(distinct country_region)
  2       , count(*), count(distinct country_region) 
  3  from (SELECT * FROM sh.countries ORDER BY dbms_random.value);

CHECKSUM(COUNTRY_REGION) CHECKSUM(DISTINCTCOUNTRY_REGION)   COUNT(*) COUNT(DISTINCTCOUNTRY_REGION)
------------------------ -------------------------------- ---------- -----------------------------
                  683368                           415340         23                             6

SQL> select job from emp where deptno = 20;

JOB      
---------
CLERK
MANAGER
ANALYST
CLERK
ANALYST

SQL> select job from emp where deptno = 10;

JOB      
---------
MANAGER
PRESIDENT
CLERK

SQL> select job from emp where deptno = 20
  2  MINUS
  3  select job from emp where deptno = 10;

JOB      
---------
ANALYST

SQL> select job from emp where deptno = 20
  2  MINUS ALL
  3  select job from emp where deptno = 10;

JOB      
---------
CLERK
ANALYST
ANALYST

SQL> select job from emp where deptno = 20
  2  EXCEPT
  3  select job from emp where deptno = 10;

JOB      
---------
ANALYST

SQL> select job from emp where deptno = 20
  2  EXCEPT ALL
  3  select job from emp where deptno = 10;

JOB      
---------
CLERK
ANALYST
ANALYST

SQL> select job from emp where deptno = 20
  2  INTERSECT
  3  select job from emp where deptno = 10;

JOB      
---------
MANAGER
CLERK

SQL> select job from emp where deptno = 20
  2  INTERSECT ALL
  3  select job from emp where deptno = 10;

JOB      
---------
MANAGER
CLERK

SQL> select CHECKSUM(man_name), COUNT(*) 
  2  FROM rus_names;

CHECKSUM(MAN_NAME)   COUNT(*)
------------------ ----------
            570896        252

SQL> select CHECKSUM(man_name), COUNT(*) 
  2  FROM (SELECT * FROM rus_names ORDER BY dbms_random.value);

CHECKSUM(MAN_NAME)   COUNT(*)
------------------ ----------
            570896        252

SQL> select CHECKSUM(man_name), COUNT(*) 
  2  FROM (SELECT * FROM rus_names ORDER BY dbms_random.value);

CHECKSUM(MAN_NAME)   COUNT(*)
------------------ ----------
            570896        252

SQL> SELECT * FROM nls_database_parameters WHERE parameter LIKE 'NLS%CHARACTERSET';

PARAMETER                            VALUE                                                           
------------------------------------ ------------------------
NLS_NCHAR_CHARACTERSET               AL16UTF16                                                       
NLS_CHARACTERSET                     AL32UTF8                                                        

SQL> ALTER TABLE rus_names ADD man_name_nvc2 NVARCHAR2(26);
Table RUS_NAMES altered.

SQL> UPDATE rus_names SET MAN_NAME_NVC2 = MAN_NAME;
252 rows updated.

SQL> SELECT * FROM rus_names WHERE rownum <= 5;

MAN_NAME                   MAN_NAME_NVC2             
-------------------------- --------------------------
Харлампий                  Харлампий                 
Эдгард                     Эдгард                    
Эдуард                     Эдуард                    
Юлий                       Юлий                      
Юрий                       Юрий                      

SQL> COMMIT;
Commit complete.

SQL> select CHECKSUM(man_name), CHECKSUM(man_name_nvc2), COUNT(*) 
  2  FROM (SELECT * FROM rus_names ORDER BY dbms_random.value);

CHECKSUM(MAN_NAME) CHECKSUM(MAN_NAME_NVC2)   COUNT(*)
------------------ ----------------------- ----------
            570896                   20093        252

SQL> EXPLAIN PLAN FOR
  2  select job from emp where deptno = 20
  3  MINUS ALL
  4  select job from emp where deptno = 10;
Explained.

SQL> select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------
Plan hash value: 261732639
 
---------------------------------------------------------------------------------------------------
| Id  | Operation                            | Name       | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                     |            |     5 |    88 |     6  (34)| 00:00:01 |
|   1 |  MINUS ALL HASH                      |            |       |       |            |          |
|   2 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP        |     5 |    55 |     2   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN                  | EMP#R#DEPT |     5 |       |     1   (0)| 00:00:01 |
|   4 |   TABLE ACCESS BY INDEX ROWID BATCHED| EMP        |     3 |    33 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                  | EMP#R#DEPT |     3 |       |     1   (0)| 00:00:01 |

PLAN_TABLE_OUTPUT                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                      
----------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   3 - access("DEPTNO"=20)
   5 - access("DEPTNO"=10)

SQL> --SQL Macros - Scalar Valued Macros: Examples
SQL> --Print Hello <name>
SQL> --A PL/SQL function greet is defined as a scalar SQL Macro that returns the string
SQL> --'Hello, <name>! ' when called from a SQL SELECT statement.
SQL> create or replace function greet(name varchar2 default 'World')
  2  return varchar2 SQL_MACRO(Scalar) is
  3  begin
  4     return q'{ 'Hello, ' || name || '!' }';
  5  end;
  6  /
Function GREET compiled

SQL> SELECT greet from dual;

GREET        
-------------
Hello, World!

SQL> SELECT greet('uncle Bob') from dual;

GREET('UNCLEBOB')
-----------------
Hello, uncle Bob!

SQL> --SQL Macros - Table Valued Macros: Examples
SQL> --The macro function budget computes the amount of each department's budget for a given job. 
SQL> -- It returns the number of employees in each department with the specified job title.
SQL> create or replace function budget(job varchar2) return varchar2 SQL_MACRO is
  2  begin
  3  return q'{
  4  select deptno, sum(sal) AS budget
  5  from emp
  6  where job = budget.job
  7  group by deptno
  8  }';
  9  end;
 10  /

Function BUDGET compiled

SQL> SELECT * FROM budget ('MANAGER');

    DEPTNO     BUDGET
---------- ----------
        20       2975
        30       2850
        10       2450

...
 48  s 
 49  from t where id>=0;

         S
----------
      4001

............
44  )
 45  select 
 46    substr(listagg(n ON OVERFLOW TRUNCATE '>' WITH /*OUT*/ COUNT) WITHIN GROUP (ORDER BY id ),-50 ) s, 
 47    length (listagg(n ON OVERFLOW TRUNCATE '>' WITH /*OUT*/ COUNT) WITHIN GROUP (ORDER BY id ) ) len 
 48  from t where id>=0;

S                                                         LEN
-------------------------------------------------- ----------
------------------------------------------^12>(35)      32744