SET ECHO ON TERM ON;
SPOOL sqltcdirs.lis;
REM
REM $Header: 215187.1 sqltcdirs.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   sqltcdirs.sql
REM
REM DESCRIPTION
REM   This script creates a pointer to the server directory that
REM   contains all SQLTXPLAIN output reports before being copied to
REM   the local SQL*Plus directory
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected INTERNAL (SYS) as
REM      SYSDBA
REM   2. SQLTXPLAIN must have read/write access to directory
REM      specified
REM
REM PARAMETERS
REM   1. Full path of existing staging directory (required)
REM      This value is case sensitive, and actual directory must
REM      exists in server prior to the execution of this script
REM
REM EXECUTION
REM   1. Navigate to sqlt/utl directory
REM   2. Start SQL*Plus connecting INTERNAL (SYS) as SYSDBA
REM   3. Execute script sqltcdirs.sql
REM
REM EXAMPLE
REM   # cd sqlt/utl
REM   # sqlplus /nolog
REM   SQL> connect / as sysdba
REM   SQL> start sqltcdirs.sql [full stage directory path]
REM   SQL> start sqltcdirs.sql /d02/oracle/SCMX7ST1/db/tech_st/10.2.0/admin/SCMX7ST1_kepler/udump
REM
REM NOTES
REM   1. For possible errors see sqltcdirs.lis file
REM
WHENEVER SQLERROR EXIT SQL.SQLCODE;
BEGIN
  IF USER != 'SYS' THEN
    RAISE_APPLICATION_ERROR(-20100, 'Install failed - Directory pointers should be created connected as SYS, not as '||USER);
  END IF;
END;
/

/* ---------------------------------------------------------------------- */

PRO ... Creating SQLT$ Stage Directory

DEF directory_path = '&1';

BEGIN
  IF '&&directory_path' LIKE '%?%' OR '&&directory_path' LIKE '%*%' OR '&&directory_path' LIKE '%$%' THEN
    RAISE_APPLICATION_ERROR(-20101, 'Install failed - Directory &&directory_path cannot contain "?", "*" or "$" symbols');
  END IF;
END;
/

CREATE OR REPLACE DIRECTORY SQLT$STAGE AS '&&directory_path';

GRANT READ  ON DIRECTORY SQLT$STAGE TO sqltxplain;
GRANT WRITE ON DIRECTORY SQLT$STAGE TO sqltxplain;

WHENEVER SQLERROR CONTINUE;
GRANT READ  ON DIRECTORY SQLT$STAGE TO trcanlzr;
GRANT WRITE ON DIRECTORY SQLT$STAGE TO trcanlzr;

SPOOL OFF;
UNDEFINE 1 DIRECTORY_PATH
