Statspack - Secrets of the Oracle Database - page 320

The following SQL script retrieves the statement text without forced line breaks and saves
it in a spool file named sp_sqltext_ hash_value .lst , where hash_value is the argument passed
to the script:

$ cat sp_sqltext_get.sql
define hash_value=&1
set verify off
set long 100000
set trimout on
set trimspool on
set feedback off
set heading off
set linesize 32767
col sql_text format a32767
spool sp_sqltext_&hash_value..lst
select sql_text from table(site_sys.sp_sqltext(&hash_value));
spool off
exit

Let's test the script with the hash value 1455318379 of the statement in question.

$ sqlplus -s system/secret @sp_sqltext_get.sql 1 455318379

SELECT emp.last_name, emp.first_name, j.job_title, d.department_name, l.city,
l.state_province, l.postal_code, l.street_address, emp.email,
emp.phone_number, emp.hire_date, emp.salary, mgr.last_name
FROM hr.employees emp, hr.employees mgr,
hr.departments d, hr.locations l, hr.jobs j
WHERE emp.manager_id=mgr.employee_id
AND emp.department_id=d.department_id
AND d.location_id=l.location_id
AND emp.job_id=j.job_id

The entire statement is now on a single line of text 6 (SQL*Plus inserts a blank line at the
beginning of the file, such that the total line count amounts to 2).

$ wc -l sp_sqltext_1455318379.lst
2 sp_sqltext_1455318379.lst

We finally achieved our goal of retrieving the statement text with correct SQL syntax. When
called without an argument or a NULL argument, the function SP_SQLTEXT retrieves all statements
from STATS$SQLTEXT .

The UNIX command wc counts lines and characters in files. If working on Windows, install the compli-
mentary UNIX-like environment Cygwin from http://www.cygwin.com to get access to wc and many
other UNIX utilities such as awk , grep , and find .

6.