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]
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;
This query shows SID, username and start time of the running transaction.
In addition, number of the used blocks is shown.
SELECT a.sid, a.status, a.username, b.xidusn, b.used_urec, b.used_ublk, b.START_TIME
FROM v$session a, v$transaction b
WHERE a.saddr = b.ses_addr
order by START_TIME desc;
Here is the list of the Oracle SQL types and their internal codes:
SELECT t.typecode,o.name
FROM sys.type$ t, sys.obj$ o
WHERE
BITAND (t.properties, 16) = 16
AND t.toid = o.oid$
ORDER BY t.typecode
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;
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.