Statspack - Secrets of the Oracle Database - page 317

Pipelined table functions require the keyword PIPELINED after the RETURN clause. This keyword
indicates that the function returns rows iteratively. The return type of the pipelined table function
must be a collection type. This collection type can be declared at the schema level with CREATE TYPE
or inside a package. The function iteratively returns individual elements of the collection type. The
elements of the collection type must be supported SQL data types, such as NUMBER and VARCHAR2 .
PL/SQL data types, such as PLS_INTEGER and BOOLEAN , are not supported as collection elements in a
pipelined table function. We will use the following object type for pipelining:
CREATE OR REPLACE TYPE site_sys.sqltext_type AS OBJECT (
hash_value NUMBER,
sql_text CLOB
);
/
CREATE OR REPLACE TYPE site_sys.sqltext_type_tab AS TABLE OF sqltext_type;
/
The code of the pipelined table function SP_SQLTEXT is reproduced in the next code example.
Objects are created in schema SITE_SYS , since SYS is reserved for the data dictionary and objects in
schema SYS are not covered by a full export. Some extra work is required to obtain compatibility
of the function with both Oracle9 i and Oracle10 g . In Oracle10 g and subsequent releases, the
column HASH_VALUE in the table STATS$SQLTEXT was renamed to OLD_HASH_VALUE due to a like-
wise rename in the views V$SQL and V$SQLAREA . For Oracle9 i a synonym is used, whereas a view
is created to compensate for the renamed column in Oracle10 g and subsequent releases. Thus the
code of the function SITE_SYS.SP_SQLTEXT can remain constant for both releases and dynamic SQL
with DBMS_SQL must not be used.
$ cat sp_sqltext.sql
-- run as a DBA user
CREATE USER site_sys IDENTIFIED BY secret PASSWORD EXPIRE ACCOUNT LOCK;
/* note that show errors does not work when creating objects in a foreign schema.
If you get errors either run this script as SITE_SYS after unlocking
the account or access DBA_ERRORS as below:
col text format a66
SELECT line,text from dba_errors where name='SP_SQLTEXT' ORDER BY line;
*/
-- cleanup, e.g. for database upgraded to 10g
begin
execute immediate 'DROP SYNONYM site_sys.stats$sqltext';
execute immediate 'DROP VIEW site_sys.stats$sqltext';
exception when others then null;
end;
/
GRANT SELECT ON perfstat.stats$sqltext TO site_sys;
/* for 9i:
CREATE OR REPLACE SYNONYM site_sys.stats$sqltext FOR perfstat.stats$sqltext;
for 10g, create this view:
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;
*/