Hi All,
Please help me in pointing the mistake in the following procedure. It compiles and meets the condition but does not perform any thing.
CREATE OR REPLACE PROCEDURE SP_DEL_ARCHIVE_TARGET ( archtable VARCHAR2)

IS
arch_count Number;
pversion Number;
tname_arch VARCHAR2(200):=archtable||' p,D_version V';
Condition Varchar2(200):='AND D_VERSION.VERSION_STATUS=ARCHIVE AND D_VERSION.ARCHIVE_STATUS IS NULL';
tCommitLimit NUMBER := 10000;
tCommitCount NUMBER := 0;
TYPE tCursorType IS REF CURSOR;
tDeleteCursor tCursorType;
tRowID ROWID;
tsql varchar2(2000);

--PROCEDURE: SP_DEL_ARCHIVE
--
--ENVIRONMENT/LANGUAGE: PL/SQL


--
-----------------------------------------------------------------------
--PURPOSE: This procedure deletes data from ARCHIVE tables
-- before loading the production data to this table to clean-up table
-- ensure that data loaded by a prevous imcomplete runs is cleaned-up.
--
-----------------------------------------------------------------------
--VERSION HISTORY:
--------------------------------------------------------------------------
-- Version 1.0.0
-- Date: Dec. 16, 2002
-- Author: Vikram Jit Sharma
-----------------------------------------------------------------------------

cursor C1 is SELECT VERSION_KEY FROM D_VERSION
WHERE D_VERSION.VERSION_STATUS='ARCHIVE' and
D_VERSION.ARCHIVE_STATUS is NULL;


BEGIN

Open C1;
Loop
FETCH C1 into pversion;
exit when C1%notfound;


tsql:= 'select count (*) from '||tname_arch||' WHERE p.version_key = V.version_key '||CONDITION;

EXECUTE IMMEDIATE tsql INTO arch_count;
IF arch_count > 0 THEN


OPEN tDeleteCursor FOR

'SELECT ROWID FROM ' || archtable || ' WHERE VERSION_KEY =:tversion_key' USING pversion;

LOOP
FETCH tDeleteCursor INTO tRowID;
EXIT WHEN tDeleteCursor%NOTFOUND;

EXECUTE IMMEDIATE 'DELETE ' || archtable || ' WHERE ROWID = :vrowid' USING tRowID ;

IF tCommitCount > tCommitLimit
THEN
COMMIT;
tCommitCount :=0;
ELSE
tCommitCount := tCommitCount + 1;
END IF;
END LOOP;
END IF;
CLOSE tDeleteCursor;

COMMIT;
end Loop;
Close C1;
EXCEPTION
WHEN OTHERS THEN
NULL ;
END;
/