SET ECHO ON TERM ON SERVEROUT ON;
SPOOL squtltest.lis;
REM
REM $Header: 215187.1 squtltest.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   squtltest.sql
REM
REM DESCRIPTION
REM   This script tests that UTL_FILE works properly as required
REM   by SQLTXPLAIN to read and write files.
REM
REM PRE-REQUISITES
REM   1. This script must be executed connected as SQLTXPLAIN
REM
REM PARAMETERS
REM   1. None
REM
REM EXECUTION
REM   1. Navigate to sqlt/install directory
REM   2. Start SQL*Plus connecting as SQLTXPLAIN
REM   3. Execute script squtltest.sql
REM
REM EXAMPLE
REM   # cd sqlt/install
REM   # sqlplus sqltxplan/[sqltxplain pwd]
REM   SQL> start squtltest.sql
REM
REM NOTES
REM   1. This script is executed automatically by sqcreate.sql
REM   2. For possible errors see squtltest.lis file
REM
SET ECHO ON TERM OFF;
WHENEVER SQLERROR EXIT SQL.SQLCODE;
DECLARE
  rdbms_release NUMBER;
  l_open_mode VARCHAR2(2);

  PROCEDURE open_write_close (
    p2_location IN VARCHAR2,
    p2_filename IN VARCHAR2 )
  IS
    out_file_type UTL_FILE.file_type;
  BEGIN
    out_file_type :=
    SYS.UTL_FILE.FOPEN (
       location     => p2_location,
       filename     => p2_filename,
       open_mode    => l_open_mode,
       max_linesize => 32767 );

    IF rdbms_release < 10 THEN
      SYS.UTL_FILE.PUT_LINE (
        file   => out_file_type,
        buffer => 'Hello World!');
    ELSE
      SYS.UTL_FILE.PUT_RAW (
        file   => out_file_type,
        buffer => UTL_RAW.CAST_TO_RAW('Hello World!'||CHR(10)));
    END IF;

    SYS.UTL_FILE.FCLOSE(file => out_file_type);
    DBMS_OUTPUT.PUT_LINE('Test of WRITING into file "'||p2_filename||'" in directory "'||p2_location||'" succeeded.');
  END open_write_close;

  PROCEDURE open_read_close (
    p2_directory_alias IN VARCHAR2,
    p2_file_name IN VARCHAR2 )
  IS
    l_file BFILE;
    l_file_len INTEGER := NULL;
    l_file_offset INTEGER;
    l_chunk_raw RAW(32767);
    l_chunk VARCHAR2(32767);
  BEGIN
    l_file := BFILENAME(p2_directory_alias, p2_file_name);
    DBMS_LOB.FILEOPEN (file_loc => l_file);
    l_file_len := DBMS_LOB.GETLENGTH(file_loc => l_file);
    l_file_offset := 1;

    DBMS_LOB.READ (
      file_loc => l_file,
      amount   => l_file_len,
      offset   => l_file_offset,
      buffer   => l_chunk_raw );

    l_chunk := UTL_RAW.CAST_TO_VARCHAR2 (r => l_chunk_raw);
    DBMS_LOB.FILECLOSE (file_loc => l_file);
    DBMS_OUTPUT.PUT_LINE('Test of READING file "'||p2_file_name||'" from directory "'||p2_directory_alias||'" succeeded.');
  END open_read_close;

BEGIN
  SELECT TO_NUMBER(SUBSTR(version, 1, INSTR(version, '.', 1, 2) - 1))
    INTO rdbms_release
    FROM v$instance;

  IF rdbms_release < 10 THEN
    l_open_mode := 'W';
  ELSE
    l_open_mode := 'WB';
  END IF;

  open_write_close('SQLT$STAGE', 'SQLT_UTL_FILE_Test1.txt');
  open_write_close('SQLT$UDUMP', 'SQLT_UTL_FILE_Test2.txt');

  open_read_close('SQLT$STAGE', 'SQLT_UTL_FILE_Test1.txt');
  open_read_close('SQLT$UDUMP', 'SQLT_UTL_FILE_Test2.txt');

  DBMS_OUTPUT.PUT_LINE('SQLT UTL_FILE WRITE/READ Test passed!');
END;
/
SPOOL OFF;
SET ECHO OFF TERM ON;
WHENEVER SQLERROR CONTINUE;
PRO SQUTLTEST completed. Review squtltest.lis.
