sqlplus and dbms_output.put_line in shell script

Shell Programming and Scripting
1

Hi,

I have created a pl/sql block utilises dbms_output.put_line. This script works fine when I call from sqlplus ie sqlplus @./scriptname but when I embed it within my shell script I get no output to screen so I end up with an empty spool file. I know it's executing successfully when called from the shell as I have feedback on and i get the "PL/SQL procedure successfully completed." message.My question is it possible to use dbms_output.put_line within a shell script and to spool the output to file?

Thanks,
S1

2

Please post your script

3

Part of script thats causing me problems:

sqlplus -s /nolog <<EOF
set trimspool on
set line 500
set escape on
connect /as sysdba

spool hot_backup.cmd

declare
  copy_cmnd constant varchar2(30) := 'cp';
  copy_dest constant varchar2(30) := '/u/homes/oradba/backup';

  dbname  varchar2(30);
  logmode varchar2(30);
begin
  select name, log_mode
  into   dbname, logmode
  from   sys.v_\\\$database;

  if logmode <> 'ARCHIVELOG' then
     raise_application_error(-20000,
                     'ERROR: Database must be in ARCHIVELOG mode!!!');
     return;
  end if;

  dbms_output.put_line('spool backup.'||dbname||'.'||
                       to_char(sysdate, 'ddMonyy')||'.log');

  -- Loop through tablespaces
  for c1 in (select tablespace_name ts
             from   sys.dba_tablespaces where tablespace_name='ALRX')
  loop
    dbms_output.put_line('alter tablespace '||c1.ts||' begin backup;');
    -- Loop through tablespaces' data files
    for c2 in (select file_name fil
               from   sys.dba_data_files
               where  tablespace_name = c1.ts)
    loop
      dbms_output.put_line('!'||copy_cmnd||' '||c2.fil||' '||copy_dest);
    end loop;

    dbms_output.put_line('alter tablespace '||c1.ts||' end backup;');
  end loop;

  -- Backup controlfile and switch logfiles
  dbms_output.put_line('alter database backup controlfile to trace;');
  dbms_output.put_line('alter database backup controlfile to '||''''||
                       copy_dest||'control.'||dbname||'.'||
                       to_char(sysdate,'DDMonYYHH24MI')||''''||';');
  dbms_output.put_line('alter system switch logfile;');
  dbms_output.put_line('spool off');
end;
/

spool off

set head on
set feed on
set serveroutput off



exit;
EOF
4

set serverout on is missing in ur script

5

UTL_FILE is better for this task, ex:

sqlplus -s / <<-EOF
set echo off 
set verify off
set feed off
set pagesize 0
set pause off
set linesize 4000
set trims on
set serveroutput on size 1000000
DECLARE
fichero utl_file.file_type;
BEGIN
fichero:=utl_file.fopen('/tmp','hot_backup.cmd','W');
utl_file.put_line(fichero,'LINE 1');
utl_file.put_line(fichero,'LINE 2');
utl_file.fclose(fichero);
END;
/
EOF
6

Thanks for your replys:

Serveroutput on only gives me the following "PL/SQL procedure successfully completed." and nothing else.

Klashxx: your example works fine, but we dont use utl_file_dir here and this is going to be a generic script that backs up approx 10 instances and I don't want to have to change my init.ora files as this will generate more paper work than its worth. Is there any way to get dbms_output to print to the screen when run from with the shell?

7

That's very strange

Try the following code, it worked for me:

sqlplus -s scott/tiger <<EOF
SET SERVEROUTPUT ON
SET FEED OFF
spool aaa.txt
BEGIN
DBMS_OUTPUT.PUT_LINE('xyz');
END;
/
spool off
EOF