SQL & PL/SQL

There is a hotbackup script that us being used since I came to this company. The database is running Oracle 9.2.0.7 in Windows 2003 SP1. The script backup and copies the dbf files to network shared drive. During the past several months, the hot backup script is working fine and is not throwing any errors. When we changed the repository of backups to the new server having high capacity in hard drive and memory, the hotbackup script started throwing the flowing errors:

ORA-20000: ORU-10028: line length overflow, limit of 255 chars per line
ORA-06512: at "SYS.DBMS_OUTPUT", line 35
ORA-06512: at "SYS.DBMS_OUTPUT", line 133
ORA-06512: at line 12

In my investigation, this is happening because of the 255 byte limit. The hotbackup.sql and hotbackup_list.sql scripts that are being created by the main script below are going beyond that limit. Please see the main script below:

@echo off
REM #####################################################################
REM PROGRAM NAME:

REM PURPOSE: This utility performs hot backup of
REM the database on Windows
REM USAGE:

REM
REM #####################################################################

REM :::::::::::::::::::: Begin Declare Variables Section

set ORA_HOME=
set CONNECT_USER=
set ORACLE_SID=
set BACKUP_DIR=
set INIT_FILE=
set ARC_DEST=
set TOOLS=F:
set LOGDIR=
set CTLFILEBKP_DIR=
set LOGFILE=%LOGDIR%\%ORACLE_SID%.log

set HFILE=%BACKUP_DIR%\log\hotbackup.sql
set ERR_FILE=%BACKUP_DIR%\log\herrors.log
set LOG_FILE=%BACKUP_DIR%\log\hbackup.log
set BKP_DIR=%BACKUP_DIR%

REM :::::::::::::::::::: End Declare Variables Section

REM :::::::::::::::::::: Begin Parameter Checking Section



REM Create backup directories if already not exist
if not exist %BACKUP_DIR%\data mkdir %BACKUP_DIR%\data
if not exist %BACKUP_DIR%\initora mkdir %BACKUP_DIR%\initora
if not exist %BACKUP_DIR%\arch mkdir %BACKUP_DIR%\arch
if not exist %BACKUP_DIR%\log mkdir %BACKUP_DIR%\log
if not exist %LOGDIR% mkdir %LOGDIR%

REM Check to see that there were no create errors
if not exist %BACKUP_DIR%\data goto backupdir
if not exist %BACKUP_DIR%\initora goto backupdir
if not exist %BACKUP_DIR%\arch goto backupdir
if not exist %BACKUP_DIR%\log goto backupdir

REM Deletes previous backup. Make sure you have it on tape.
del/q %BACKUP_DIR%\data\*
del/q %BACKUP_DIR%\initora\*
del/q %BACKUP_DIR%\arch\*
del/q %BACKUP_DIR%\log\*

echo. > %ERR_FILE%
echo. > %LOG_FILE%
(echo Hot Backup started & date/T & time/T) >> %LOG_FILE%
echo Parameter Checking Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Parameter Checking Section

REM :::::::::::::::::::: Begin Create Dynamic files Section
echo. >%HFILE%
echo set termout off heading off feedback off >>%HFILE%
echo set linesize 300 pagesize 0 >>%HFILE%
echo set serveroutput on size 1000000 >>%HFILE%
echo spool %BACKUP_DIR%\log\hotbackup_list.sql >>%HFILE%

echo Declare >>%HFILE%
echo cursor c1 is select distinct tablespace_name from dba_data_files order by tablespace_name; >>%HFILE%
echo cursor c2( ptbs varchar2) is select file_name from dba_data_files where tablespace_name = ptbs order by file_name; >>%HFILE%
echo Begin >>%HFILE%
echo dbms_output.put_line('set termout off heading off feedback off'); >>%HFILE%

echo. >>%HFILE%
echo dbms_output.put_line(chr(10) ); >>%HFILE%
echo dbms_output.put_line('host REM ******Data files' ); >>%HFILE%
echo for tbs in c1 loop >>%HFILE%
echo dbms_output.put_line(' alter tablespace '^|^| tbs.tablespace_name ^|^|' begin backup;'); >>%HFILE%
echo for dbf in c2(tbs.tablespace_name) loop >>%HFILE%
echo dbms_output.put_line(' host copy '^|^|dbf.file_name^|^|' %BKP_DIR%\data 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%'); >>%HFILE%
echo end loop; >>%HFILE%
echo dbms_output.put_line(' alter tablespace '^|^|tbs.tablespace_name ^|^|' end backup;'); >>%HFILE%
echo end loop; >>%HFILE%

echo. >>%HFILE%
echo dbms_output.put_line(chr(10) ); >>%HFILE%
echo dbms_output.put_line('host REM ******Control files ' );>>%HFILE%
echo dbms_output.put_line(' alter database backup controlfile to '^|^| ''^|^|'''%CTLFILEBKP_DIR%\control_file.ctl'''^|^|''^|^|' reuse;'); >>%HFILE%
echo dbms_output.put_line(' alter database backup controlfile to trace;'); >>%HFILE%

echo. >>%HFILE%
echo dbms_output.put_line(chr(10) ); >>%HFILE%
echo dbms_output.put_line('host REM ******Init.ora file ' ); >>%HFILE%
echo dbms_output.put_line(' host copy %INIT_FILE% %BKP_DIR%\initora 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%'); >>%HFILE%

REM echo. >>%HFILE%
REM echo dbms_output.put_line(chr(10) ); >>%HFILE%
REM echo dbms_output.put_line('host REM ******Archivelog files' ); >>%HFILE%
REM echo dbms_output.put_line(' alter system switch logfile;'); >>%HFILE%
REM echo dbms_output.put_line(' alter system archive log stop;'); >>%HFILE%
REM echo dbms_output.put_line('host copy %ARC_DEST%\* %BKP_DIR%\arch 1^>^> %LOG_FILE% 2^>^> %ERR_FILE%' ); >>%HFILE%
REM echo dbms_output.put_line(' alter system archive log start;'); >>%HFILE%


echo dbms_output.put_line('exit;'); >>%HFILE%
echo End; >>%HFILE%
echo / >>%HFILE%
echo spool off >>%HFILE%
echo exit; >>%HFILE%

echo Dynamic files Section Completed >> %LOG_FILE%
REM :::::::::::::::::::: End Create Dynamic files Section

REM :::::::::::::::::::: Begin HotBackup Section

%ORA_HOME%\bin\sqlplus -s %CONNECT_USER% @%HFILE%
REM Copy the files to backup location
%ORA_HOME%\bin\sqlplus -s %CONNECT_USER% @%BACKUP_DIR%\log\hotbackup_list.sql

(echo HotBackup Completed Successfully & date/T & time/T) >> %LOG_FILE%
(echo HotBackup Completed Successfully & date/T & time/T) >> %LOGFILE%
goto end

REM :::::::::::::::::::: End HotBackup Section


REM :::::::::::::::::::: Begin Error handling section

:usage
echo Error, Usage: hotbackup.bat SID
goto end

:backupdir
echo Error creating Backup directory structure >> %ERR_FILE%
(echo HOTBACKUP_FAIL:Error creating Backup directory structure & date/T & time/T) >> %LOGFILE%
REM :::::::::::::::::::: End Error handling section

REM :::::::::::::::::::: Cleanup Section
:end
set ORA_HOME=
set ORACLE_SID=
set CONNECT_USER=
set BACKUP_DIR=
set INIT_FILE=
set ARC_DEST=
CTLFILEBKP_DIR=


In my google search , there are 4 methods to get around this limit. One is to use DBMS_OUTPUT.NEW_LINE to split it up into lines. Second is to use a wrapper or package. Third is to create a DBMS-OUTPUT-like package. Fourth is to use UTL_FILE package. In my assessment, the first method can’t be applied as the “host copy… “ command that is being generated by the main script cannot be broken into several lines because it would create “syntax error”. The fourth method is can’t be applied too ( in my opinion ) because it only works for local drive/file. For the other methods, I’m not sure if they work.
Can anyone have experience which method works best in this situation? If yes, can you show me (if possible in details ) how you implemented the method?

Correction to my guess in UTL_FILE package. This package can be applied to network drive although it needs additional tasks as mentioned in Oracle Metalink 45172.1.