Set set serveroutput on , Dbms_output.put_line do not work when database is not open

From: <dbaplusplus_at_hotmail.com>
Date: 21 Mar 2007 19:38:22 -0700
Message-ID: <1174531102.033496.306420@p15g2000hsd.googlegroups.com>

I am using Oracle 8.1.6.1 on HP UNIX 11 (cannot change versions). I am setting up a standby database. I am writing a UNIX script for applying archived logs to standby database. I do not like managed recovery because it does not automatically delete archived logs. My question is really irrelevant to type of ecovery and probably version of Oracle.

When I use PL/SQL block and try to use dbms_output,put_line etc, I get PL/SQL errors:
sqlplus -S /nolog << !
connect / as sysdba;
set buffer edit;
set serveroutput on size 1000000;
declare
  sql_stmt varchar2(512);
  start_seq integer;
  end_seq integer;
  arcname varchar2(256);
begin

   sql_stmt := 'select max(sequence#)+1 from v\$log_history';    execute immediate sql_stmt into start_seq;    sql_stmt := 'select max(sequence#) from v\$archived_log';    execute immediate sql_stmt into end_seq;    dbms_output.put_line('start_seq = ' || start_seq || ' ' || 'end_seq
= ' || end_seq);

   for i in start_seq..end_seq loop

       sql_stmt := 'select name from v\$archived_log where sequence# = :b1';

       execute immediate sql_stmt into arcname using i;
       -- dbms_output.put_line(arcname);

   end loop;
end;
/RROR:

ORA-06550: line 1, column 7:
PLS-00201: identifier 'DBMS_OUTPUT.ENABLE' must be declared
ORA-06550: line 1, column 7:

PL/SQL: Statement ignored

   dbms_output.put_line('start_seq = ' || start_seq || ' ' || 'end_seq
= ' || end_seq);

   *
ERROR at line 11:

ORA-06550: line 11, column 4:
PLS-00201: identifier 'DBMS_OUTPUT.PUT_LINE' must be declared
ORA-06550: line 11, column 4:

PL/SQL: Statement ignored

+ exit

For quite some time, I had no idea why these error messages come, finally I figured out it is because database is not open. What is work around? I ended up only using sql statements instead of PL/SQL (seems a serious limitation). I could probably open database in read only mode and then switch to standby mode but that is cumbersome.

Any pointers are welcome. Received on Wed Mar 21 2007 - 21:38:22 CDT