Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Usenet -> c.d.o.server -> Re: Set set serveroutput on , Dbms_output.put_line do not work when database is not open

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

From: <fitzjarrell_at_cox.net>
Date: 22 Mar 2007 08:18:58 -0700
Message-ID: <1174576738.050320.208750@n76g2000hsh.googlegroups.com>


On Mar 21, 9:38 pm, dbaplusp..._at_hotmail.com wrote:
> 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.

Why re-invent the wheel when managed recovery exists? Oh, yes, you 'don't like it' because it doesn't delete your archive logs. I don't know of ANY standby/Data Guard implementation where the standby/Data Guard configuration from Oracle deletes your archivelogs; that's what your cron job is supposed to do. You're far better off using managed recovery rather than try to kludge some script to do the job..

David Fitzjarrell Received on Thu Mar 22 2007 - 10:18:58 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US