EXCEPTION CASE_NOT_FOUND

Oracle function Case
Oracle case



SET SERVEROUT ON

DECLARE

p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;

BEGIN

BEGIN
CASE
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 5;
END;

DBMS_OUTPUT.PUT_LINE('q = ' || q);

END;

/



case for null


SET SERVEROUT ON

DECLARE

p BINARY_INTEGER:= null;
-- p BINARY_INTEGER:= 0;
q BINARY_INTEGER := 0;

BEGIN

BEGIN
CASE
WHEN p is null then q:=10000;
WHEN p>100 THEN q:= 1;
WHEN p>50 THEN q:= 2;
WHEN p>10 THEN q:= 3;
END CASE;
EXCEPTION
WHEN CASE_NOT_FOUND THEN
q := 4;
END;

DBMS_OUTPUT.PUT_LINE('q = ' || q);

END;

/

ORA-01476: divisor is equal to zero

to avoid ORA-01476: divisor is equal to zero


declare
n1 number := 155;
n2 number := 0;

begin
n1 := n1/n2; -- divide by zero
dbms_output.put_line(n1);
exception
when ZERO_DIVIDE
then dbms_output.put_line('You Divided by Zero');
end;
/

DBMS_STATS.GATHER_SCHEMA_STATS

Schema stats

DBMS_STATS state gather
DBMS_STATS

declare
olist dbms_stats.objecttab ;
begin

declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;



method_opt=>'for all indexed columns size skewonly'
method_opt=>'for all columns size repeat'
method_opt=>'for columns size auto'


There are several values for the OPTIONS parameter that we need to know about:

GATHER - Reanalyzes the whole schema

GATHER EMPTY - only analyzes tables that have no existing statistics

GATHER STALE - only reanalyzes tables with more than 10 percent modifications (inserts, updates, deletes)

GATHER AUTO - will reanalyze objects that currently have no statistics and objects with stale statistics. Using GATHER AUTO is like combining GATHER STALE and GATHER EMPTY.



Example :

exec DBMS_STATS.gather_schema_stats(
ownname => 'ANUJ',
options => 'GATHER AUTO',
estimate_percent => dbms_stats.auto_sample_size,
method_opt => 'for all columns size repeat',
cascade => true )







SQL> show user
USER is "SCOTT"
SQL> /
Objects Analyzed: 0

PL/SQL procedure successfully completed.

SQL> r
1 declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER STALE',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14* end;
Objects Analyzed: 0

PL/SQL procedure successfully completed.


ORA-20001: Illegal option GATHER REANALYZES: must be GATHER | GATHER AUTO | GATHER STALE | GATHER EMPTY | LIST AUTO | LIST STALE | LIST EMPTY



'GATHER REANALYZES' reanalyzes


declare
olist dbms_stats.objecttab ;
begin



declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;




SQL> show user
USER is "SCOTT"
SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER AUTO',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15
16
17 /
Objects Analyzed: 15
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: MYEMP_WORK
Object Name: SALGRADE
Object Name: MYEMP_IDX
Object Name: PK_DEPT
Object Name: SYS_C0022541
Object Name: SYS_C0022542
Object Name: SYS_C0022543

PL/SQL procedure successfully completed.




declare
olist dbms_stats.objectTab;

begin
dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);

dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
if olist.COUNT > 0 then
FOR x in 1..olist.COUNT LOOP
dbms_output.put_line('Object Name: ' || olist(x).objname );
END LOOP;
end if;

end;


SQL> declare
2 olist dbms_stats.objectTab;
3
4 begin
5 dbms_stats.gather_schema_stats(user,cascade=>TRUE,method_opt=> 'FOR ALL INDEXED COLUMNS',options=>'GATHER',objlist=>olist);
6
7 dbms_output.put_line('Objects Analyzed: ' || olist.COUNT );
8 if olist.COUNT > 0 then
9 FOR x in 1..olist.COUNT LOOP
10 dbms_output.put_line('Object Name: ' || olist(x).objname );
11 END LOOP;
12 end if;
13
14 end;
15 /
Objects Analyzed: 7
Object Name: ANUJ10
Object Name: BONUS
Object Name: DEPT
Object Name: EMP
Object Name: MYEMP
Object Name: MYEMP_WORK
Object Name: SALGRADE

PL/SQL procedure successfully completed.



SQL> declare
2 a dbms_stats.objecttab;
3 begin
4 dbms_stats.gather_database_stats (OPTIONS=>'LIST EMPTY',OBJLIST=>a);
5
6 for i in 1 .. a.count
7 loop
8 dbms_output.put_line( a(i).ownname );
9 dbms_output.put_line( a(i).objType );
10 dbms_output.put_line( a(i).objName );
11 dbms_output.put_line( a(i).PartName );
12 dbms_output.put_line( a(i).subPartName );
13 dbms_output.put_line ( '-------------------------' );
15 end loop;
16 end;
17 /
SH
INDEX
SYS_IOT_TOP_74375
-------------------------
SH
INDEX
SYS_IOT_TOP_74380
-------------------------

PL/SQL procedure successfully completed.

===============

#!/bin/bash

. /home/oracle/.bash_profile

export ORACLE_HOME=/u01/app/oracle/product/12.2/db_home

export ORACLE_BASE=/u01/app/oracle

export ORACLE_SID=orcl

export DATE=$(date +%y-%m-%d_%H%M%S)

# Gather ANUJ schema stats 

sqlplus / as sysdba << EOF > /tmp/ANUJ_stats_gather_$DATE.log

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

EXEC DBMS_STATS.gather_schema_stats('ANUJ');

EOF

echo "Stats gathered succeeded"

********

exec DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO ;

/home/oracle/bin/run_gather_database_stats.sh orcl &> /dev/null

 cat /home/oracle/bin/run_gather_database_stats.sh

#! /bin/bash

#

source /home/oracle/.bash_profile

sid=$1

export OUTFILE=/var/log/oracle/gather_database_stats_$sid.log

export ORACLE_SID=${sid}

$echo $ORACLE_SID

sqlplus /nolog << EOF

connect / as sysdba

set line 999

set pagesize 999

set trimspool on

set echo on

spool $OUTFILE

@/home/oracle/bin/gather_databse_stats.sql

spool off

exit

 cat /home/oracle/bin/gather_databse_stats.sql

alter session set nls_date_format='DD-MON-YYYY HH24:MI:SS';

select sysdate from dual;

exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER EMPTY');

select sysdate from dual;

exec DBMS_STATS.GATHER_DATABASE_STATS (options=> 'GATHER STALE');

select sysdate from dual;

[oracle@lon1-rac01 ~]$

=============

on root and pdbs !!!!

===================

set verify off time on timing on

 alter session set nls_date_format='dd-mon-yyyy';

select sysdate from dual;

 

 

prompt ========================================

prompt DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ()

prompt ========================================

 

EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ();

 

 

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM')

prompt ========================================

 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYSTEM');

 

 

prompt ========================================

prompt DBMS_STATS.GATHER_SCHEMA_STATS('SYS')

prompt ========================================

 

 

EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SYS');

 

 

prompt ========================================

prompt DBMS_STATS.GATHER_DICTIONARY_STATS

prompt ========================================

 

EXEC DBMS_STATS.GATHER_DICTIONARY_STATS ();

 

In Oracle Null=null ? ---- False

In Oracle Null is not equal to null :) !!!!!!!!!!



select case when null = null
then 1 ----true
else 2 ----flase
end
from dual ;

CASEWHENNULL=NULLTHEN1----TRUEELSE2----FLASEEND
-----------------------------------------------
2 ---- so false


so you can not equate the null with null ( null=null) --<<--- false





select decode(null,null, 1, 2) from dual; --<<<<--- in decode

DECODE(NULL,NULL,1,2)
---------------------
1 <<<---- true





select case when null = null
then 1
else 2
end "Case",
decode(null,null, 1, 2) "Decode" from dual
SQL> /

Case Decode
---------- ----------
2 1






SELECT * FROM dual WHERE NULL = NULL;

No rows selected.

SELECT * FROM dual WHERE NULL <> NULL;

No rows selected.