DESCRIPTION
This script is a function named sql_text that is used within a couple other custom reports. Install this into the Oracle repository and the reports that need it can use it.
SQL*PLUS SCRIPT
CREATE OR REPLACE FUNCTION sql_text (
p_dbid IN NUMBER,
p_hash_value IN NUMBER)
RETURN VARCHAR2
AS
sSQL VARCHAR2(500);
TYPE trc IS REF CURSOR;
rc trc;
sSQLText VARCHAR2(32767) := '';
sSQLPiece VARCHAR2(4000);
BEGIN
sSQL := 'SELECT st FROM const_'||p_dbid||' WHERE h = :1 ORDER BY p';
OPEN rc FOR sSQL USING p_hash_value;
LOOP
FETCH rc INTO sSQLPiece;
EXIT WHEN rc%NOTFOUND;
IF NVL(LENGTH(sSQLText),0) + LENGTH(sSQLPiece) < 10000 THEN
sSQLText := sSQLText || sSQLPiece;
ELSE
EXIT;
END IF;
END LOOP;
CLOSE rc;
RETURN sSQLText;
EXCEPTION
WHEN VALUE_ERROR THEN
RETURN 'SQL Text Too Long';
WHEN OTHERS THEN
RETURN 'Error Getting SQLTEXT';
END sql_text;
/