Oracle: eval function

The function takes some expression as the argument and executes it, returning output in the varchar string


create or replace function eval (expr varchar2) return varchar2
as
ret varchar2(4000);
begin
execute immediate 'begin :result := ' || expr || '; end;' using out ret;
return ret;
end;
/

The discussion and examples could be found there

[amazon-product]1590599683[/amazon-product]

Oracle: list of the system events

This piece of code show the list of events, set in the Oracle database


set serveroutput on
declare
event_level number;
begin
dbms_output.enable(20000) ;
for i in 10000..33999 loop
sys.dbms_system.read_ev(i,event_level);
if (event_level > 0) then
dbms_output.put_line('Event '||to_char(i)||' set at level '||
to_char(event_level));
end if;
end loop;
end;

Oracle: wait statisics by block class

This query helps to get information about the wait statistics (in fact, this query is contained in GV_$WAITSTAT)


SELECT
inst_id,
DECODE (indx,
1,'data block',
2,'sort block',
3,'save undo block',
4,'segment header',
5,'save undo header',
6,'free list',
7,'extent map',
8,'1st level bmb',
9,'2nd level bmb',
10,'3rd level bmb',
11,'bitmap block',
12,'bitmap index block',
13,'file header block',
14,'unused',
15,'system undo header',
16,'system undo block',
17,'undo header',
18,'undo block'
), count, time
FROM x$kcbwait
WHERE indx != 0;

Reading password in Unix shell


print -n "Enter Your password:"
stty_orig=`stty -g`
trap "stty ${stty_orig}; exit" 1 2 3 15
stty -echo >&- 2>&-
read PASS
stty ${stty_orig} >&- 2>&-
trap 1 2 3 15
print

trap :catches interruptions. I.e. if the user presses Ctrl+C, the normal stty mode is set before stopping the program
stty -echo :switches off the display echo
>&- 2>&- :helps to avoid “stty: Not a typewriter” message for non-interactive scripts.