Database Reference
In-Depth Information
declare
version varchar2(30);
compatibility varchar2(30);
begin
dbms_utility.db_version(version, compatibility);
if to_number(substr(version,1,2)) >= 10 then
execute immediate 'CREATE OR REPLACE VIEW site_sys.stats$sqltext
(hash_value, piece, sql_text) AS
SELECT old_hash_value, piece, sql_text
FROM perfstat.stats$sqltext';
else
execute immediate 'CREATE OR REPLACE SYNONYM site_sys.stats$sqltext
FOR perfstat.stats$sqltext';
end if;
end;
/
/*
p_hash_value is either the hash value of a specific statement in
STATS$SQLTEXT to retrieve or NULL.
When NULL, all statements in the Statspack repository are retrieved.
The column is called old_hash_value in Oracle10g
*/
CREATE OR REPLACE function site_sys.sp_sqltext(p_hash_value number default null)
RETURN sqltext_type_tab PIPELINED
AS
result_row sqltext_type:=sqltext_type(null, empty_clob);
cursor single_stmt(p_hash_value number) is
select hash_value, piece, sql_text from stats$sqltext
where p_hash_value=hash_value
order by piece;
cursor multi_stmt is
select hash_value, piece, sql_text from stats$sqltext
order by hash_value, piece;
v_sql_text stats$sqltext.sql_text%TYPE;
v_piece binary_integer;
v_prev_hash_value number:=NULL;
v_cur_hash_value number:=0;
BEGIN
dbms_lob.CREATETEMPORARY(result_row.sql_text, true);
IF p_hash_value IS NULL THEN
open multi_stmt; -- caller asked for all statements
