Database Reference
In-Depth Information
ELSE
open single_stmt(p_hash_value); -- retrieve only one statement
END IF;
LOOP
IF p_hash_value IS NULL THEN
FETCH multi_stmt INTO v_cur_hash_value, v_piece, v_sql_text;
EXIT WHEN multi_stmt%NOTFOUND;
ELSE
FETCH single_stmt INTO v_cur_hash_value, v_piece, v_sql_text;
EXIT WHEN single_stmt%NOTFOUND;
END IF;
IF v_piece=0 THEN -- new stmt starts
IF v_prev_hash_value IS NOT NULL THEN
-- there was a previous statement which is now finished
result_row.hash_value:=v_prev_hash_value;
pipe row(result_row);
-- trim the lob to length 0 for the next statement
dbms_lob.trim(result_row.sql_text, 0);
-- the current row holds piece 0 of the new statement - add it to
CLOB
dbms_lob.writeappend(result_row.sql_text, length(v_sql_text),
v_sql_text);
ELSE
-- this is the first row ever
result_row.hash_value:=v_cur_hash_value;
dbms_lob.writeappend(result_row.sql_text, length(v_sql_text),
v_sql_text);
END IF;
ELSE
-- append the current piece to the CLOB
result_row.hash_value:=v_cur_hash_value;
dbms_lob.writeappend(result_row.sql_text, lengthb(v_sql_text),
v_sql_text);
END IF;
v_prev_hash_value:=v_cur_hash_value;
END LOOP;
-- output last statement
pipe row(result_row);
dbms_lob.freetemporary(result_row.sql_text);
IF p_hash_value IS NULL THEN
CLOSE multi_stmt;
ELSE
CLOSE single_stmt;
END IF;
return;
END;
/
GRANT EXECUTE ON site_sys.sp_sqltext TO dba;
