Truncates in a SQR loop

["Cullina, Adam" <cullinaa@UMSYSTEM.EDU>]

Trying to par down an Oracle(8) database instance I wrote the following
code.   Much to my annoyance procedure 'TWO' fails to work correctly (while
'ONE' works fine).  If I run procedure 'TWO', but remove the looping
truncate calls, it gives me the correct list of tables, but if I leave the
'DO TRUNCATES' in it will just get stuck on the first table and not proceed
any farther.   Is there some unwritten law I am unaware of that tells you
not to nest truncate statements?




BEGIN-PROGRAM
DO ONE
DO TWO
END-PROGRAM


BEGIN-PROCEDURE ONE
BEGIN-SELECT
TABLE_NAME

            LET $TABLE_NAME = &TABLE_NAME

            show $TABLE_NAME
            DO DELETES

FROM ALL_TAB_COLUMNS A, ALL_OBJECTS B
WHERE A.COLUMN_NAME = 'EMPLID'
AND A.OWNER = 'SYSADM'
AND A.TABLE_NAME = B.OBJECT_NAME
AND B.OBJECT_TYPE = 'TABLE'
AND SUBSTR(A.TABLE_NAME, 0, 3) = 'PS_'
END-SELECT
END-PROCEDURE ONE

BEGIN-PROCEDURE TWO
BEGIN-SELECT DISTINCT
TABLE_NAME &T2

            LET $TABLE_NAME = &T2
            LET $TABLE_NAME = 'SYSADM.' || $TABLE_NAME
            show $TABLE_NAME

            DO TRUNCATES

FROM ALL_TAB_COLUMNS A, ALL_OBJECTS B
WHERE A.OWNER = 'SYSADM'
AND A.TABLE_NAME = B.OBJECT_NAME
AND B.OBJECT_TYPE = 'TABLE'
AND ( SUBSTR(A.TABLE_NAME, 0, 7) = 'PS_CRSE' OR SUBSTR(A.TABLE_NAME, 0, 8) =
'PS_CLASS')
END-SELECT
END-PROCEDURE TWO


BEGIN-PROCEDURE DELETES
BEGIN-SQL ON-ERROR=SQR-ERROR
 DELETE FROM [$TABLE_NAME] A WHERE EMPLID NOT LIKE 'R%';
 COMMIT
END-SQL
END-PROCEDURE

BEGIN-PROCEDURE TRUNCATES
BEGIN-SQL
 TRUNCATE TABLE [$TABLE_NAME]
END-SQL
END-PROCEDURE