Creating a Procedure to turn off Online Archiving - Need help!
We've been having some problems lately with Online Archiving being kept on after a backup job has failed. Because of this, I've been trying to create a stored procedure that will a) check for any databases that have this feature turned on, and b) dynamically build and execute "LOGGING ONLINE ARCHIVE OFF FOR" statements for each of the databases listed.
I can manually turn on and off Online Archiving with the following statements:
LOGGING ONLINE ARCHIVE ON FOR DBASE1; LOGGING ONLINE ARCHIVE OFF FOR DBASE1;
Here's what I've got for my stored procedure:
REPLACE PROCEDURE SYSDBA.ONLINE_ARCHIVING_OFF()
DECLARE V_SQL_STMT CHAR(2000) ;
FOR DBCURSOR AS TLC CURSOR FOR
SELECT DISTINCT DATABASENAME FROM DBC.ArchiveLoggingObjsV
SET V_SQL_STMT = 'LOGGING ONLINE ARCHIVE OFF FOR "' || UPPER(DBCURSOR.DATABASENAME) || '"; ' ;
CALL DBC.SYSEXECSQL(:V_SQL_STMT );
However when I call this procedure, it doesn't turn off online archiving. My questions are:
1) Does the "LOGGING ONLINE ARCHIVE ON FOR" statement work in stored procedures? If so, where am I making my mistake? 2) If this ends up not being able to be done through stored procedures, can anyone suggest a better way to approach this task?