SET DEF ON ECHO ON TERM ON SQLP NUMF "" SQL>;
SPO sqltxtrxec.log;
REM
REM $Header: 215187.1 sqltxtrxec.sql 11.2.9.8 2009/08/31 csierra $
REM
REM Copyright (c) 2000-2009, Oracle Corporation. All rights reserved.
REM
REM AUTHOR
REM   carlos.sierra@oracle.com
REM
REM SCRIPT
REM   sqltxtrxec.sql
REM
REM DESCRIPTION
REM   This script concatenates the execution of the methods XTRACT
REM   and XECUTE. It requires as input the hash_value or the sql_id
REM   of one memory-resident (gv$sql) SQL statement.
REM
REM PRE-REQUISITES
REM   1. Install the SQLT tool as per instructions.txt provided
REM   2. Determine the hash_value or sql_id of one SQL that still
REM      resides in memory (v$sql or gv$sql).
REM   3. To get plan statistics, STATISTICS_LEVEL must be set to ALL
REM      before the desired SQL is executed
REM   4. The user that executes this method must comply with:
REM        o  Be the application schema owner that originated the SQL
REM        o  Must be granted those privileges shown in script
REM           sqlt/install/sqguser.sql
REM
REM PARAMETERS
REM   1. Unique id of the SQL to be extracted (required)
REM      (hash_value or sql_id would work)
REM
REM EXECUTION
REM   1. Navigate to sqlt/run server directory
REM   2. Start SQL*Plus in server connecting as application user
REM   3. Execute script sqltxtrxec.sql passing hash_value or sql_id
REM      of one memory-resident SQL
REM   4. Provide all generated files to the requestor
REM
REM EXAMPLE
REM   # cd sqlt/run
REM   # sqlplus [apps user]/[apps pwd]
REM   SQL> start sqltxtrxec.sql [hash_value or sql_id for SQL]
REM   SQL> start sqltxtrxec.sql 2524255098  <== SQL hash_value
REM   SQL> start sqltxtrxec.sql 0w6uydn50g8cx  <== SQL sql_id
REM
REM NOTES
REM   1. XTRACT method generates a test case script which is then
REM      input to XECUTE.
REM   2. If XTRACT and XECUTE complete with no errors, a zip file
REM      with output of both is generated.
REM   3. If XECUTE exists by closing the session, review test case
REM      generated by XTRACT since it may need manual adjustments.
REM   4. This script is for 10g and 11g. If used on 9i and the SQL
REM      contains binds, it may fail on XECUTE phase.
REM
SET ECHO OFF;
PRO
PRO Parameter 1: SQL_ID or HASH_VALUE of the SQL to be extracted (required)
DEF unique_id = '&1';
PRO
PRO Value passed to sqltxtrxec.sql:
PRO ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
PRO UNIQUE_ID: &&unique_id
PRO
SPO OFF;
REM Phase 1: XTRACT for given SQL
@@sqltxtract.sql &&unique_id.
REM Get test case script (created by XTRACT) out of zip
HO unzip -n sqlt_s&&statement_id2. &&tc_script_file_name.
REM Phase 2: XECUTE using test case script
@@sqltxecute.sql &&tc_script_file_name.
REM Return script to zip file (just to be deleted from working directory)
HO zip -mf sqlt_s&&statement_id2. &&tc_script_file_name.
REM Creates zip file with XTRACT and XECUTE files
HO zip -mT sqlt_s&&statement_id2._s&&statement_id3. sqlt_s&&statement_id2..zip sqlt_s&&statement_id3..zip sqltxtrxec.log
CL COL;
UNDEFINE statement_id2 statement_id3 tc_script_file_name;
SET DEF ON
PRO SQLTXTRXEC completed.
