Hello Everyone,

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()

BEGIN

DECLARE V_SQL_STMT CHAR(2000) ;

FOR DBCURSOR AS TLC CURSOR FOR

SELECT DISTINCT DATABASENAME
FROM DBC.ArchiveLoggingObjsV

DO

SET V_SQL_STMT = 'LOGGING ONLINE ARCHIVE OFF FOR "' || UPPER(DBCURSOR.DATABASENAME) || '"; ' ;

CALL DBC.SYSEXECSQL(:V_SQL_STMT );

END FOR;

END;

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?

Thank you for your time!

- Thomas Coffing III