Search This Blog

Total Pageviews

Saturday 15 October 2011

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 ~]$



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.

Oracle DBA

anuj blog Archive