--***************************************************************************
-- Copyright (c) Microsoft Corporation.
--
-- @File: instorcl.sql
--
-- Purpose:
-- 	Oracle install script to load heterogeneous replication package
-- 	
-- Notes:
--
-- @EndHeader@
--
--***************************************************************************
-- Create package HREPL
CREATE OR REPLACE PACKAGE HREPL
AS
	SQLORIGINATOR BOOLEAN := FALSE;
	
	BASETIMET CONSTANT DATE := TO_DATE('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MI:SS');

	PROCEDURE RefreshXactSetJob(argXactSetJobEnabled IN INTEGER, argXactSetJobInterval IN INTEGER, argXactSetJobThreshold IN INTEGER,
		argXactSetJobLRInterval IN INTEGER, argXactSetJobLRThreshold IN INTEGER);

	PROCEDURE PollBegin(argLSN IN RAW);

	PROCEDURE XactSetJob;

	PROCEDURE PollEnd(argLSN IN RAW);

	PROCEDURE SnapshotBegin(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argPublicationID IN NUMBER,
		argArticleID IN NUMBER, argDirectory IN VARCHAR2, argScriptSch IN VARCHAR2, argScriptIdx IN VARCHAR2,
		argSyncCommand IN VARCHAR2, argPreScript IN VARCHAR2, argPostScript IN VARCHAR2,
		argCreationScriptPath IN VARCHAR2, argFtpAddress IN VARCHAR2, argFtpPort IN VARCHAR2,
		argFtpSubdirectory IN VARCHAR2, argFtpLogin IN VARCHAR2, argFtpPassword IN VARCHAR2,
		argAlternateSnapshotFolder IN VARCHAR2, argCompressSnapshot IN VARCHAR2);

	PROCEDURE SnapshotEnd(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argPublicationID IN NUMBER,
		argArticleID IN NUMBER);
	
	PROCEDURE RowCnt(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argPublicationID IN NUMBER,
		argArticleID IN NUMBER, argCommandType IN NUMBER, argCommand IN VARCHAR2, argSubscriptionLevel IN NUMBER);

	PROCEDURE MarkSubscription( argPublicationID IN NUMBER,	argArticleID IN NUMBER, argCommandType IN NUMBER, argCommand IN VARCHAR2);

	PROCEDURE PublishTable(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER, argTriggerStyle IN NUMBER,
		argRecreateTriggers IN NUMBER, argArticleView IN VARCHAR2, argReplFreq IN NUMBER, argTimestamp IN VARCHAR2, argInstance IN NUMBER, argColumnMask IN RAW, argTriggerMask IN RAW, argFilterClause IN LONG);

	PROCEDURE AlterTableLog(argTableID IN NUMBER, argTablespace IN VARCHAR2);

	PROCEDURE ValidateRowFilter(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argColumnMask IN RAW, argFilterClause IN LONG);
	
	PROCEDURE UnPublishTable(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER, argArticleView IN VARCHAR2, argDropTriggers IN NUMBER, argTimestamp IN VARCHAR2);

	PROCEDURE InitPublisher;
	
	PROCEDURE SetSqlOriginator;
	
	PROCEDURE Trace(argPublicationID IN NUMBER, argArticleID IN NUMBER, argTracerCmdType IN NUMBER, argTracerStr IN VARCHAR2);

	PROCEDURE CompileTriggers;
	
END HREPL;
/
SHOW ERRORS PACKAGE HREPL
CREATE OR REPLACE PACKAGE BODY HREPL
AS
-----------------------------------------------------------------------------------
--  Declare PL/SQL table types
-----------------------------------------------------------------------------------
	TYPE string_tab IS TABLE OF VARCHAR2(128) 
	INDEX BY BINARY_INTEGER;
	TYPE number_tab IS TABLE OF NUMBER
	INDEX BY BINARY_INTEGER;
-----------------------------------------------------------------------------------
--  Declare Constants
-----------------------------------------------------------------------------------
	DeleteOp CONSTANT INTEGER := 1;
	InsertOp CONSTANT INTEGER := 2;
	UpdateOldOp CONSTANT INTEGER := 3;
	UpdateNewOp CONSTANT INTEGER := 4;
	DeleteUnqOp CONSTANT INTEGER := 15;
	InsertUnqOp CONSTANT INTEGER := 16;
	NoPollInProcess CONSTANT INTEGER := 0;
	ProcessingChanges CONSTANT INTEGER := 1;
	ProcessingSyncDone CONSTANT INTEGER := 2;  
	UpdateCommand CONSTANT INTEGER := 1;
	DeleteCommand CONSTANT INTEGER := 2;
	UpdateAndDeleteCommand CONSTANT INTEGER := 3;
	NewCommand CONSTANT INTEGER := 4;
	SYNCINIT CONSTANT NUMBER := 5;
	SYNCDONE CONSTANT NUMBER := 6;
	SNAPSHOT CONSTANT NUMBER := 7;
	TABLEROWCNT CONSTANT NUMBER := 10;
	INRECONCILIATION CONSTANT NUMBER := 11;
	PENDINGSUBSCRIPTIONMARKER CONSTANT NUMBER := 12;
	ACTIVESUBSCRIPTIONMARKER CONSTANT NUMBER := 13;
	TRACERTOKEN CONSTANT NUMBER := 14;
	Language CONSTANT INTEGER := DBMS_SQL.native;
	MAXLEN CONSTANT INTEGER := 255;
	ArticleLogTemplate CONSTANT VARCHAR2(30) := 'HREPL_ARTICLEXLOG_Y';
	LogViewTemplate CONSTANT VARCHAR2(30) := 'HREPL_LOG_X_Y_Z';
	ArticleViewTemplate CONSTANT VARCHAR2(30) := 'HREPL_ARTICLE_X_Y';
	ArticleTriggerRowTemplate CONSTANT VARCHAR2(30) := 'HREPL_ARTICLEX_TRIGGER_ROW';
	ArticleTriggerStmtTemplate CONSTANT VARCHAR2(30) := 'HREPL_ARTICLEX_TRIGGER_STMT';
	ArticleTempView CONSTANT VARCHAR2(30) := 'HREPL_TempViewX';
	ArticleFuncLongLen CONSTANT VARCHAR2(30) := 'HREPL_LEN_X_Y_Z';
	MatchString CONSTANT VARCHAR2(1) := 'X';
	MatchStringY CONSTANT VARCHAR2(1) := 'Y';
	MatchStringZ CONSTANT VARCHAR2(1) := 'Z';
	SecondsInaDay CONSTANT NUMBER := 86400;
	BASETIME CONSTANT DATE := TO_DATE('2000-01-01 01:00:00', 'YYYY-MM-DD HH:MI:SS');
-----------------------------------------------------------------------------------
--  Declare Error values
-----------------------------------------------------------------------------------
	EPollInProgress		CONSTANT NUMBER	:= -20001;
	EPublisherLSNMismatch   CONSTANT NUMBER := -20002;
	ECompilationError	CONSTANT NUMBER := -20003;
	EInconsistentDatatypes  CONSTANT NUMBER := -20932;
-----------------------------------------------------------------------------------
--  Declare Error strings
-----------------------------------------------------------------------------------
	StrPollInProgress	CONSTANT VARCHAR2(200)	:= 'Poll already in process';
	StrPublisherLSNMismatch CONSTANT VARCHAR2(200) := 'The LSN associated with this poll interval at the publisher did not match the LSN sent with the PollEnd.';
	StrInconsistentDatatypes CONSTANT VARCHAR2(200) := 'Inconsistent datatypes';   				  				
-----------------------------------------------------------------------------------
	FUNCTION is_string (data_type IN VARCHAR2) RETURN BOOLEAN
	IS
	BEGIN
		RETURN (data_type IN ('CHAR', 'VARCHAR2', 'NCHAR', 'NVARCHAR2'));
	END;

	FUNCTION is_number (data_type IN VARCHAR2) RETURN BOOLEAN
	IS
	BEGIN
		RETURN (data_type IN ('NUMBER', 'FLOAT'));
	END;

	FUNCTION is_raw (data_type IN VARCHAR2) RETURN BOOLEAN
	IS
	BEGIN
		RETURN (data_type IN ('RAW'));
	END;
-----------------------------------------------------------------------------------
--  Declare internal procedures
-----------------------------------------------------------------------------------
	FUNCTION HREPLPOLLIsEmpty RETURN NUMBER;

	FUNCTION HREPLPOLLHasChanges(argPollid IN NUMBER) RETURN NUMBER;
	
	FUNCTION HREPLPOLLHasTableChanges(argPollid IN NUMBER) RETURN NUMBER;
	
	FUNCTION HREPLPOLLHasOnlyInrecCommands(argPollid IN NUMBER) RETURN NUMBER;
	
	FUNCTION HREPLPUBLISHEDTABLESIsEmpty RETURN NUMBER;
	
	FUNCTION HREPLEVENTHasEntries(argPollid IN NUMBER, argOperation IN NUMBER) RETURN NUMBER;

	PROCEDURE PopulatePollTable;
	
	PROCEDURE SeqFixupForUniqueColumnUpdates;
	
	PROCEDURE GetTableIDs(argTableIDs IN OUT number_tab, argInstanceIDs IN OUT number_tab);

	PROCEDURE CreateTableTriggers(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER, argLogTable IN VARCHAR2, argTriggerStyle IN NUMBER,
		argColumnCount IN NUMBER, argColNames IN string_tab, argColTypes IN string_tab, argColMask IN number_tab);

	PROCEDURE CreateRowTrigger(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER, argLogTable IN VARCHAR2, argColumnCount IN NUMBER,
		argColNames IN string_tab, argColTypes IN string_tab, argTriggerStyle IN NUMBER, argColMask IN number_tab);

	PROCEDURE GenerateRowTrigger(argCommandTable IN OUT DBMS_SQL.VARCHAR2S, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2,
		argTableID IN NUMBER, argLogTable IN VARCHAR2, argColumnCount IN NUMBER, argColNames IN string_tab,
		argColTypes IN string_tab, argTriggerStyle IN NUMBER, argColMask IN number_tab);
		
	PROCEDURE CreateStmtTrigger(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER);

	PROCEDURE GenerateStmtTrigger(argCommandTable IN OUT DBMS_SQL.VARCHAR2S, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2,
		 argTableID IN NUMBER);

	PROCEDURE GenerateValueClause(argCommandTable IN OUT DBMS_SQL.VARCHAR2S, argType IN VARCHAR2, argColumnCount IN NUMBER, 
		argColNames IN string_tab, argColTypes IN string_tab, argTriggerStyle IN NUMBER, argCommand IN INTEGER, argTableID IN NUMBER,
		argLogTable IN VARCHAR2, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argColMask IN number_tab);

	PROCEDURE GeneratePKClause(argCommandTable IN OUT DBMS_SQL.VARCHAR2S, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2);

	PROCEDURE GeneratePKList(argCommandTable IN OUT DBMS_SQL.VARCHAR2S, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2);

	PROCEDURE GenerateCreateCommand(argLogTable IN VARCHAR2, argColumnCount IN NUMBER,
		argSQLCommand IN OUT DBMS_SQL.VARCHAR2S, argColNames IN string_tab, argColTypes IN string_tab,
		argTriggerStyle IN NUMBER, argColMask IN number_tab);

	PROCEDURE GenerateIndexCommand(argLogTable IN VARCHAR2, argSQLCommand IN OUT DBMS_SQL.VARCHAR2S);

	PROCEDURE CreateView(argTableName IN VARCHAR2, argTableOwner IN VARCHAR2, argViewName IN VARCHAR2, 
		argColumnCount IN NUMBER, argColNames IN string_tab, argColTypes IN string_tab,
		argColMask IN number_tab, argFilterClause IN LONG);
		
	PROCEDURE GenerateView(argTableName IN VARCHAR2, argTableOwner IN VARCHAR2, argViewName IN VARCHAR2, argColumnCount IN NUMBER,
		argSQLCommand IN OUT DBMS_SQL.VARCHAR2S, argColNames IN string_tab, argColTypes IN string_tab,
		argColMask IN number_tab, argFilterClause IN LONG);

	PROCEDURE CreateGetLongLenFunction(argArticleID IN NUMBER, argTableID IN NUMBER, argArticleInstance IN NUMBER,
		argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argColumnCount IN NUMBER,	argColNames  IN string_tab,
		argColTypes IN string_tab, argColMask IN number_tab);
		
	PROCEDURE GenerateFunctionGetLongLen(argFuncName IN VARCHAR2, argTableOwner IN VARCHAR2, argTableName IN VARCHAR2,
		argColInd IN NUMBER, argSQLCommand IN OUT DBMS_SQL.VARCHAR2S, argColNames  IN string_tab, argColTypes IN string_tab,
		argColMask IN number_tab);

	PROCEDURE AddToSQLCommand(argString IN VARCHAR2, argCommandTable IN OUT DBMS_SQL.VARCHAR2S);

	FUNCTION NextRow(argStringIn IN VARCHAR2, argStartInOut IN OUT BINARY_INTEGER, argLenIn IN BINARY_INTEGER) RETURN VARCHAR2; 

	PROCEDURE CreateTableLog( argLogTable IN VARCHAR2, argTriggerStyle IN NUMBER,
		argColCount IN NUMBER, argColNames IN string_tab, argColTypes IN string_tab, argColMask IN number_tab);

	PROCEDURE CreateTableIndex( argLogTable IN VARCHAR2);

	PROCEDURE PopulateColumnTables(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argColCount OUT NUMBER,
		argColNames IN OUT string_tab, argColTypes IN OUT string_tab);

	PROCEDURE TableCleanup(argTableOwner IN VARCHAR2, argTableName IN VARCHAR2, argTableID IN NUMBER);
	
	PROCEDURE DropView(argArticleView IN VARCHAR2);

	PROCEDURE DropLog(argArticleLog IN VARCHAR2);
	
	PROCEDURE DropFunction(argFunctionName IN VARCHAR2);
	
	PROCEDURE SetColumnsIncluded(argColumnMask IN RAW, argColumnsIncluded IN OUT number_tab);
	
	FUNCTION HasValidTriggers (argTableName IN VARCHAR2, argTableOwner IN VARCHAR2, argTableID IN NUMBER,
		argLogInstance IN NUMBER) RETURN NUMBER;

	FUNCTION ToNumFromHex (argChar IN CHAR) RETURN NUMBER;

	PROCEDURE ExecuteCommand(argSQLCommand IN VARCHAR2);
	
	PROCEDURE ExecuteCommandTable(argSQLCommandTable IN DBMS_SQL.VARCHAR2S);
	
	PROCEDURE ExecuteCommandForPollID(argSQLCommand	IN VARCHAR2, argPollidValue IN NUMBER);

	PROCEDURE ExecuteCommandForPollIDTableID(argSQLCommand	IN VARCHAR2, argPollidValue IN NUMBER, argTableidValue IN NUMBER);

	PROCEDURE ExecuteCommandTableForPollID(argSQLCommandTable IN DBMS_SQL.VARCHAR2S, argPollidValue IN NUMBER);

	PROCEDURE CheckCompilationErrors(argObjectName IN VARCHAR2, argObjectType IN VARCHAR2);
	
	PROCEDURE CleanupLogsandViews;

-----------------------------------------------------------------------------------
--
--  Name:    HREPLPOLLIsEmpty
--
--  Purpose: This procedure checks HREPL_POLL to determine whether it has
--           any rows.  It is a more efficient way to determine whether HREPL_POLL
--           is empty than using COUNT(*), since COUNT can be extremely costly if
--			 HREPL_POLL has a large number of entries.
--  Input:   None.
--	     
--  Returns: 1 = HREPL_POLL is empty
--           0 = HREPL_POLL has at least one row
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLPOLLIsEmpty
RETURN NUMBER
IS
	IsEmpty NUMBER;
	pollid NUMBER;	
	CURSOR	poll_cur
	IS
		SELECT Poll_Pollid FROM HREPL_POLL;
	
BEGIN
	OPEN poll_cur;
	FETCH poll_cur INTO pollid;

	IF poll_cur%NOTFOUND
	THEN
		IsEmpty := 1;
	ELSE
		IsEmpty := 0;
	END IF;
	
	CLOSE poll_cur;
				
	RETURN IsEmpty;
END HREPLPOLLIsEmpty;
-----------------------------------------------------------------------------------
--
--  Name:    HREPLPOLLHasChanges
--
--  Purpose: This procedure checks HREPL_POLL to determine whether it has any rows
--           whose POLLID is the current pollid.  It is a more efficient way to determine 
--           whether HREPL_POLL has changes to be processed than using COUNT(*), since COUNT 
--			 can be extremely costly if HREPL_POLL has a large number of entries.
--  Input:   
--			 argPollid  current pollid to check
--	     
--  Returns: 1 = HREPL_POLL has changes to process for the identified poll interval
--           0 = HREPL_POLL does not have changes to process for the identified poll interval
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLPOLLHasChanges
(
	argPollid	IN NUMBER
)
RETURN NUMBER
AS
	HasChanges NUMBER;
	pollid NUMBER;
	
	CURSOR	poll_cur
	IS
		SELECT Poll_Pollid FROM HREPL_POLL
		WHERE Poll_Pollid = argPollid;
BEGIN
	OPEN poll_cur;
	FETCH poll_cur INTO pollid;

	IF poll_cur%NOTFOUND
	THEN
		HasChanges := 0;
	ELSE
		HasChanges := 1;
	END IF;
	
	CLOSE poll_cur;			
	
	RETURN HasChanges;
	
END HREPLPOLLHasChanges;
-----------------------------------------------------------------------------------
--
--  Name:    HREPLPOLLHasTableChanges
--
--  Purpose: This procedure checks HREPL_POLL to determine whether it has any table changes
--           whose POLLID is the current pollid.  It is a more efficient way to determine 
--           whether HREPL_POLL has table changes to be processed than using COUNT(*), since COUNT 
--			 can be extremely costly if HREPL_POLL has a large number of entries.
--  Input:   
--			 argPollid  current pollid to check
--	     
--  Returns: 1 = HREPL_POLL has table changes to process for the identified poll interval
--           0 = HREPL_POLL does not have table changes for the identified poll interval
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLPOLLHasTableChanges
(
	argPollid	IN NUMBER
)
RETURN NUMBER
IS
	HasTableChanges NUMBER;
	pollid NUMBER;	

	CURSOR	poll_cur
	IS
		SELECT Poll_Pollid FROM HREPL_POLL
		WHERE Poll_Pollid = argPollid
		AND Poll_UpdateFlag IN (0, 1, 2);
		
BEGIN
	OPEN poll_cur;
	FETCH poll_cur INTO pollid;

	IF poll_cur%NOTFOUND
	THEN
		HasTableChanges := 0;
	ELSE
		HasTableChanges := 1;
	END IF;
	
	CLOSE poll_cur;			
	
	RETURN HasTableChanges;
	
END HREPLPOLLHasTableChanges;
-----------------------------------------------------------------------------------
--
--  Name:    HREPLPOLLHasOnlyInrecCommands
--
--  Purpose: This procedure checks HREPL_POLL to determine whether it has only in reconciliation
--           commands whose POLLID is the current pollid.  It is a more efficient way to determine 
--           whether HREPL_POLL has primary key updates to be processed than using COUNT(*), 
--			 since COUNT can be extremely costly if HREPL_POLL has a large number of entries.
--  Input:   
--			 argPollid  current pollid to check
--	     
--  Returns: 1 = HREPL_POLL has either no entries or only in reconciliation entries
--           0 = HREPL_POLL does not have only in reconciliation entries
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLPOLLHasOnlyInrecCommands
(
	argPollid	IN NUMBER
)
RETURN NUMBER
AS
	HasOnlyInrecCommands NUMBER;
	pollid NUMBER;
		
	CURSOR	poll_cur
	IS
		SELECT Poll_Pollid FROM HREPL_POLL
		WHERE Poll_Pollid = argPollid
		AND Poll_UpdateFlag <> INRECONCILIATION;
		
BEGIN
	OPEN poll_cur;
	FETCH poll_cur INTO pollid;

	IF poll_cur%NOTFOUND
	THEN
		HasOnlyInrecCommands := 1;
	ELSE
		HasOnlyInrecCommands := 0;
	END IF;
	
	CLOSE poll_cur;			
	
	RETURN HasOnlyInrecCommands;
	
END HREPLPOLLHasOnlyInrecCommands;
-----------------------------------------------------------------------------------
--
--  Name:    HREPLPUBLISHEDTABLESIsEmpty
--
--  Purpose: This procedure checks HREPL_PUBLISHEDTABLES to determine whether it has
--           any rows.  It is a more efficient way to determine whether HREPL_PUBLISHEDTABLES
--           is empty than using COUNT(*), since COUNT can be extremely costly if
--			 HREPL_PUBLISHEDTABLES has a large number of entries.
--  Input:   none
--	     
--  Returns: 1 = HREPL_PUBLISHEDTABLES is empty
--           0 = HREPL_PUBLISHEDTABLES has at least one row
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLPUBLISHEDTABLESIsEmpty
RETURN NUMBER
IS
	IsEmpty NUMBER;
	tableid NUMBER;	
	CURSOR	tab_cur
	IS
		SELECT Published_TableID FROM HREPL_PUBLISHEDTABLES
		WHERE Published_LogDropPending = 0
		AND	  Published_ArticleDropPending = 0;
	
BEGIN
	OPEN tab_cur;
	FETCH tab_cur INTO tableid;

	IF tab_cur%NOTFOUND
	THEN
		IsEmpty := 1;
	ELSE
		IsEmpty := 0;
	END IF;
	
	CLOSE tab_cur;
				
	RETURN IsEmpty;
END HREPLPUBLISHEDTABLESIsEmpty;
-----------------------------------------------------------------------------------
--
--  Name:    HREPLEVENTHasEntries
--
--  Purpose: This procedure checks HREPL_EVENT to determine whether it has any entries having
--           a given pollid and operation type.  It is a more efficient way to determine 
--           whether HREPL_EVENT has entries associated with a given operation to be processed  
--			 than using COUNT(*), since COUNT can be extremely costly if HREPL_EVENT has a large
--			 number of entries.
--  Input:   
--			 argPollid		Pollid of entry
--			 argOperation	Event operation of entry
--	     
--  Returns: 1 = HREPL_EVENT has entries associated with the passed pollid and operation
--           0 = HREPL_EVENT does not have entries associated with the passed pollid and operation
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
FUNCTION HREPLEVENTHasEntries
(
	argPollid		IN NUMBER,
	argOperation	IN NUMBER
)
RETURN NUMBER
AS
	HasEntries NUMBER;
	pollid NUMBER;
		
	CURSOR	event_cur 
	IS
		SELECT Poll_Pollid FROM HREPL_EVENT e, HREPL_POLL p
		WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID
		AND Event_Operation = argOperation
		AND p.Poll_Pollid = argPollid;
		
BEGIN
	OPEN event_cur; 
	FETCH event_cur INTO pollid;

	IF event_cur%NOTFOUND
	THEN
		HasEntries := 0;
	ELSE
		HasEntries := 1;
	END IF;
	
	CLOSE event_cur;			
	
	RETURN HasEntries;
	
END HREPLEVENTHasEntries;
-----------------------------------------------------------------------------------
--
--  Name:    RefreshXactSetJob
--  Input:
--       argXactSetJobEnabled IN INTEGER	Flag to indicate whether Poll Job is enable
--                                          0 = Enable, 1 = Disabled
--       argXactSetJobInterval IN INTEGER	Number of minutes between successive executions
--                                          of the Poll Job
--       argXactSetJobThreshold IN INTEGER	Number of changes that need to be exceeded in order
--                                          to force a new poll interval
--       argXactSetJobLRInterval IN INTEGER Number of minutes between successive executions
--                                          of the Poll Job when log reader is active
--       argXactSetJobLRThreshold IN INTEGER Number of changes that need to be exceeded in order
--                                          to force a new poll interval when log reader is active
--  Output:
--  Purpose: Refresh the publisher with current parameter settings for the Poll Job.
--           The input parameters are used to reset the poll job parameters in HREPL_XactSetJob.
--
--           The job queue is interrogated to determine whether the poll job is currently
--           in the queue:
--
--           (1)  If the job is not in the job queue and the job is now marked as
--                disabled, no action is taken.
--
--           (2)  If the job is not in the job queue and the job is now marked as
--                enabled, the job is started using the current parameters.
--
--           (3)  If the job is in the job queue and the job is now marked as disabled,
--                the job is removed from the job queue.
--
--           (4)  If the job is in the job queue and the job is now marked as enabled,
--                the old and new interval values are compared.  If there has been a
--                change, the job is removed from the job queue, and the job is reentered
--                using the current parameters.
--
--  Notes:   HREPL_XactSetJob is updated with current information on the job
--
-----------------------------------------------------------------------------------
PROCEDURE RefreshXactSetJob
(
	argXactSetJobEnabled IN INTEGER,
	argXactSetJobInterval IN INTEGER,
	argXactSetJobThreshold IN INTEGER,
	argXactSetJobLRInterval IN INTEGER,
	argXactSetJobLRThreshold IN INTEGER
)
AS
	Jobno BINARY_INTEGER := 0;
	XactSetJobEnabled INTEGER;
	XactSetJobInterval INTEGER;
	XactSetJobLRInterval INTEGER;
	XactSetJobLRIsActive INTEGER;
	Interval INTEGER;

BEGIN
	-- Update non null poll job parameters
	IF NOT argXactSetJobEnabled IS NULL THEN
		UPDATE HREPL_XactSetJob SET XactSetJob_Enabled = argXactSetJobEnabled;
	END IF;	
	IF NOT argXactSetJobInterval IS NULL THEN
		UPDATE HREPL_XactSetJob SET XactSetJob_Interval = argXactSetJobInterval;
	END IF;	
	IF NOT argXactSetJobThreshold IS NULL THEN
		UPDATE HREPL_XactSetJob SET XactSetJob_Threshold = argXactSetJobThreshold;
	END IF;	
	IF NOT argXactSetJobLRInterval IS NULL THEN
		UPDATE HREPL_XactSetJob SET XactSetJob_LRInterval = argXactSetJobLRInterval;
	END IF;	
	IF NOT argXactSetJobLRThreshold IS NULL THEN
		UPDATE HREPL_XactSetJob SET XactSetJob_LRThreshold = argXactSetJobLRThreshold;
	END IF;	
	
	-- Obtain current XactSet job parameters from HREPL_XactSetJob
	SELECT	XactSetJob_Enabled,	
			XactSetJob_Interval,	
			XactSetJob_LRInterval,	
			XactSetJob_LRIsActive
	INTO	XactSetJobEnabled,
			XactSetJobInterval,
			XactSetJobLRInterval,
			XactSetJobLRIsActive
	FROM	HREPL_XactSetJob;
	
	-- Set Interval based upon parity of LRIsActive
	IF XactSetJobLRIsActive = 1 THEN
		Interval := XactSetJobLRInterval;
	ELSE
		Interval := XactSetJobInterval;
	END IF;	

	-- Determine whether the job is currently in the job queue
	BEGIN
		SELECT	JOB INTO Jobno
		FROM	USER_JOBS 
		WHERE	LOG_USER = USER
		AND	WHAT = 'HREPL.XactSetJob;';
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN RAISE;
	END;	

	IF (Jobno = 0) THEN
		-- If the job is not currently in the job queue
		-- and the job is enabled, enter the job in the
		-- queue.
		IF (XactSetJobEnabled = 1) THEN
			
			-- Enter Poll Job in job queue
			DBMS_JOB.SUBMIT(
				Jobno,
				'HREPL.XactSetJob;',
				SYSDATE + Interval/1440,
				'SYSDATE + ' || TO_CHAR(Interval) || '/1440'
			);
			COMMIT;

		END IF;

	ELSE
		-- If the job is currently in the job queue 
		-- and the job is disabled, remove the job from
		-- the queue.
		IF (XactSetJobEnabled = 0) THEN

			-- Remove Poll Job from job queue
			DBMS_JOB.REMOVE(Jobno);
			COMMIT;
			
		END IF;

	END IF;
	
	-- Commit transaction
	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
	
END RefreshXactSetJob;
-----------------------------------------------------------------------------------
--
--  Name:    PollBegin
--  Input:
--       argLSN       	IN RAW(10)  LSN from distributor that will be associated
--                              	with this poll interval   
--  Output:
--  Purpose: Identify the next poll interval for the Log Reader to process and 
--           update Publisher Status Table
--  Input:
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE PollBegin
(
	argLSN      IN RAW
)
AS
	PollInProcess 	NUMBER;
	ChangeCount     NUMBER;
	HasChanges		NUMBER;
	HasTableChanges	NUMBER;
	HasSyncInits	NUMBER;
	HasSyncDones	NUMBER;
	HasRowCounts	NUMBER;
	HasInReconciliations NUMBER;
	HasTracerTokens NUMBER;
	TableCount		NUMBER := 0;
	CurrentPollid	NUMBER := 0;
	Jobno			BINARY_INTEGER := 0;
	XactSetJobEnabled		INTEGER := 0;
	XactSetEnabled	INTEGER := 0;
	LRInterval		NUMBER;
	XactSetBeginTime	NUMBER;
BEGIN
	-- Set the begin time for XactSet processing
	UPDATE	HREPL_Publisher
	SET		Publisher_XactSetBeginTime = round(to_number((SYSDATE - BASETIME) * SecondsInaDay));
	
	SELECT Publisher_PollInProcess,
	       Publisher_XactSetEnabled
	INTO   PollInProcess,
	       XactSetEnabled
	FROM HREPL_Publisher;

	-- Make sure we have received the Poll End from the last poll request
	IF	NOT PollInProcess = NoPollInProcess THEN
		RAISE_APPLICATION_ERROR(EPollInProgress,StrPollInProgress);
	END IF;
	
	-- Make sure that we have at least one published table
	BEGIN
		IF HREPLPUBLISHEDTABLESIsEmpty = 1 THEN
		BEGIN
			-- If there are no tables currently being published,
			-- update publisher status variables and return
			Update HREPL_Publisher set Publisher_LSN = argLSN,
       			Publisher_HasChanges = 0,
				Publisher_SyncInits = 0,
				Publisher_SyncDones = 0,
       			Publisher_RowCntValidations = 0,
       			Publisher_TableChanges = 0,
       			Publisher_InReconciliation = 0,
       			Publisher_TracerTokens = 0,
				Publisher_PollInProcess = 0;
            COMMIT;
            RETURN;
   		END;
   		END IF;
   	END;
   	
	IF (HREPLPOLLIsEmpty = 1) AND XactSetEnabled = 1 THEN
		-- If there are no Xact sets currently defined in HREPL_Poll
		-- and the creation of Xact sets is currently enabled, create
		-- a new Xact set, setting the Pollid value to be used by Log
		-- Reader.
		
		-- Create a critical section around HREPL.PopulatePollTable by
		-- obtaining an exclusive write lock on HREPL_MUTEX.  This is needed,
		-- since both the Log Reader during PollBegin processing and the
		-- asynchronous Poll Job can execute PopulatePollTable.
		BEGIN
			-- If the lock is held, wait for it to be released
			COMMIT;
			LOCK TABLE HREPL_MUTEX IN EXCLUSIVE MODE; 
				
			-- Commit here to release the lock.  It will be
			-- acquired in PopulatePollTable.
			COMMIT;
			
			-- If HREPL_POLL is still not populated, try
			-- to populate it now, while the lock is held.
			-- The lock is released when PopulatePollTable
			-- issues a commit or rollback.
			IF HREPLPOLLIsEmpty = 1 THEN
				HREPL.PopulatePollTable;
			END IF;	

		END;
	END IF;
	
	-- Set the current pollid to the minimum value found in HREPL_Poll.
	SELECT MIN(Poll_PollID) INTO CurrentPollid FROM HREPL_Poll;
	
	-- If the only changes represented in the poll table for this interval are 
  	-- INRECONCILIATION entries, DELETE them from the poll table and event table.
	IF ( HREPLPOLLHasOnlyInrecCommands(CurrentPollid) = 1 ) THEN
		BEGIN
		
			FOR POLLID IN (SELECT CurrentPollid FROM DUAL)
			LOOP
				BEGIN
					DELETE HREPL_EVENT e
					WHERE Event_Operation = INRECONCILIATION
					AND EXISTS ( SELECT p.POLL_POLLID 
					             FROM HREPL_POLL p
					             WHERE p.POLL_ROWID = CHARTOROWID(e.ROWID)
					             AND p.Poll_Pollid = POLLID.CurrentPollid);
				EXCEPTION
					WHEN NO_DATA_FOUND THEN NULL;	
					WHEN OTHERS THEN RAISE;
				END;

				BEGIN
					DELETE HREPL_POLL
					WHERE Poll_UpdateFlag = INRECONCILIATION
					AND Poll_Pollid = POLLID.CurrentPollid;
				EXCEPTION
					WHEN NO_DATA_FOUND THEN NULL;	
					WHEN OTHERS THEN RAISE;
				END;
			END LOOP;
					
		END;
	END IF;
	
	-- Set the current pollid in the publisher table
	UPDATE HREPL_PUBLISHER
	SET	Publisher_CurrentPollid = CurrentPollid;
	
	-- At this point, if HREPL_POLL is empty, there is
	-- nothing to do.
	IF HREPLPOLLIsEmpty = 1 THEN
		-- If there are no changes to process,
		-- update publisher status variables and return
		Update HREPL_Publisher set Publisher_LSN = argLSN,
   			Publisher_HasChanges = 0,
			Publisher_SyncInits = 0,
			Publisher_SyncDones = 0,
   			Publisher_RowCntValidations = 0,
   			Publisher_TableChanges = 0,
   			Publisher_InReconciliation = 0,
   			Publisher_TracerTokens = 0,
			Publisher_PollInProcess = 0;
        COMMIT;
        RETURN;
   	END IF;

	-- Generate sequence value fixup for unique column updates
	HREPL.SeqFixupForUniqueColumnUpdates;

	IF HREPLPOLLHasChanges(CurrentPollid) = 1 THEN
		HasChanges := 1;
		PollInProcess := 1;
	ELSE
		HasChanges := 0;
		PollInProcess := 0;
	END IF;
	
	IF HREPLPOLLHasTableChanges(CurrentPollid) = 1 THEN
		HasTableChanges := 1;
	ELSE
		HasTableChanges := 0;
	END IF;
	
	IF HREPLEVENTHasEntries(CurrentPollid, INRECONCILIATION) = 1 THEN
		HasInReconciliations := 1;
	ELSE
		HasInReconciliations := 0;
	END IF;
					                
	IF HREPLEVENTHasEntries(CurrentPollid, SYNCINIT) = 1 THEN
		HasSyncInits := 1;
	ELSE
		HasSyncInits := 0;
	END IF;
	
	IF HREPLEVENTHasEntries(CurrentPollid, SYNCDONE) = 1 THEN
		HasSyncDones := 1;
	ELSE
		HasSyncDones := 0;
	END IF;
	
	IF HREPLEVENTHasEntries(CurrentPollid, TABLEROWCNT) = 1 THEN
		HasRowCounts := 1;
	ELSE
		HasRowCounts := 0;
	END IF;
	
	IF HREPLEVENTHasEntries(CurrentPollid, TRACERTOKEN) = 1 THEN
		HasTracerTokens := 1;
	ELSE
		HasTracerTokens := 0;
	END IF;

	-- Update remaining publisher status variables
	UPDATE HREPL_Publisher SET
       		Publisher_LSN				= argLSN,
       		Publisher_HasChanges        = HasChanges,
       		Publisher_TableChanges		= HasTableChanges,	
       		Publisher_SyncInits         = HasSyncInits,
       		Publisher_SyncDones         = HasSyncDones,
       		Publisher_RowCntValidations = HasRowCounts,
       		Publisher_InReconciliation  = HasInReconciliations,
       		Publisher_TracerTokens      = HasTracerTokens,
            Publisher_PollInProcess		= PollInProcess;
      
	-- Determine whether the poll job is currently enabled
	SELECT	XactSetJob_Enabled,
			XactSetJob_LRInterval
	INTO	XactSetJobEnabled,
			LRInterval
	FROM	HREPL_XactSetJob;
	LRInterval := LRInterval/1440;				

	IF (XactSetJobEnabled = 1) THEN
		BEGIN
			-- Get the Job number of the job from the job queue
			BEGIN
				SELECT	JOB INTO Jobno
				FROM	USER_JOBS 
				WHERE	LOG_USER = USER
				AND	WHAT = 'HREPL.XactSetJob;';
			EXCEPTION
				WHEN NO_DATA_FOUND THEN NULL;	
				WHEN OTHERS THEN RAISE;
			END;
			
			-- Set the LRIsActive flag to true and the InactivityCount
			-- to 0 in HREPL_XactSetJob table
			UPDATE HREPL_XactSetJob
			SET XactSetJob_LRIsActive = 1, XactSetJob_InactivityCount = 0;
		
			-- If the XactSet job is enabled and the job is in the job queue,
			-- reschedule it to run using the interval appropriate when the
			-- log reader is active. We can ignore errors here, since if the
			-- XactSet job doesn't create a XactSet, one will get created by the
			-- Log Reader when one is needed.
			IF (Jobno <> 0) THEN
				BEGIN
					-- Update job interval to reflect LR active interval
					DBMS_JOB.CHANGE(
						Jobno,
						NULL,
						SYSDATE + LRInterval,
						'SYSDATE + ' || TO_CHAR(LRInterval)  
					);
					COMMIT;
				EXCEPTION
					WHEN OTHERS THEN NULL;
				END;
			END IF;	
		END;
	END IF;
	
  	-- Commit transaction
	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
	
END PollBegin;
-----------------------------------------------------------------------------------
--
--  Name:    XactSetJob
--  Input:
--
--  Output:
--  Purpose: HREPL_Poll is queried to determine the number of pending XactSets to be
--			 processed by the log reader.  If the number exceeds 3, the LRIsActive
--           flag in HREPL_XactSetJob is set to false to indicate that the log reader
--           is not keeping up or is not active.  The interval and threshold values
--           are then set based upon the parity of LRIsActive.  The current number
--           of changes not assigned to an XactSet is then compared to the threshold
--           value.  If the number of changes exceeds the threshold value,
--           PopulatePollTable is called to create a new XactSet in HREPL_Poll.
--           Each time the XactSet job runs and its threshold is insufficient to cause
--           PopulatePollTable to be called, an inactivity counter is incremented.
--           When the threshold is met or exceeded, the inactivity counter is zeroed.
--           Whenever the activity counter exceeds 3, LRIsActive is set to 0 to resume
--           less aggressive scheduling.  Before exiting, the XactSet job sets its
--           interval of execution to reflect the current job interval.
--
--  Input:
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE XactSetJob
AS
	TableIDs	number_tab;
	InstanceIDs	number_tab;
	IDCount 	BINARY_INTEGER;
	LogTable	VARCHAR2(255);
	SelectCmd	VARCHAR2(500);
	ChangeCnt	NUMBER := 0;
	ChangeTotal	NUMBER := 0;
	Jobno		BINARY_INTEGER := 0;
	Interval	NUMBER := 0;
	Threshold	NUMBER := 0;
	PendingCnt  NUMBER := 0;
	XactSetJobInterval INTEGER;
	XactSetJobThreshold INTEGER;
	XactSetJobLRInterval INTEGER;
	XactSetJobLRThreshold INTEGER;
	XactSetJobLRIsActive INTEGER;
	XactSetJobInactivityCount INTEGER;
	XactSetEnabled INTEGER;

BEGIN
	-- Obtain the current XactSet job parameters from HREPL_XactSetJob
	-- and the XactSet enabled flag from HREPL_Publisher
	SELECT	XactSetJob_Interval,	
			XactSetJob_LRInterval,	
			XactSetJob_Threshold,	
			XactSetJob_LRThreshold,
			XactSetJob_LRIsActive,
			XactSetJob_InactivityCount,
			Publisher_XactSetEnabled
	INTO	XactSetJobInterval,
			XactSetJobLRInterval,
			XactSetJobThreshold,
			XactSetJobLRThreshold,
			XactSetJobLRIsActive,
			XactSetJobInactivityCount,
			XactSetEnabled
	FROM	HREPL_XactSetJob, HREPL_Publisher;
	
	-- If XactSet creation is not enabled, reset the job execution interval
	-- to its long cycle and exit.  No XactSets should be created.
	IF XactSetEnabled = 0 THEN
		Interval := XactSetJobInterval/1440;
	ELSE		
	BEGIN
		-- If the change threshold is 0, set ChangeTotal to 1 and bypss
		-- counting the number of pending changes, since an xactset should
		-- always be created.
		IF Threshold = 0  THEN
			ChangeTotal := 1;
		ELSE 
			-- Get the Table IDs of the published tables
			HREPL.GetTableIDs(TableIDs, InstanceIDs);
			IDCount := TableIDs.COUNT;

			-- Empty the change table, and insert one entry
			DELETE from HREPL_Changes;
			INSERT INTO HREPL_Changes VALUES(0);

			-- For each published table, determine the number of change entries in the
			-- article log that belong to the current poll interval.  
			FOR id_ind IN 1 .. IDCount
			
			LOOP
			
				LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
																 MatchStringY, TO_CHAR(InstanceIDs(id_ind)));
				
				SelectCmd :=
				'UPDATE HREPL_Changes SET ChangeCount = (SELECT COUNT(*) FROM ' || LogTable || ' l ' ||
				' WHERE NOT EXISTS ( SELECT p.Poll_POLLID FROM HREPL_POLL p WHERE CHARTOROWID(l.ROWID) = p.POLL_ROWID ))';
				HREPL_ExecuteCommand(SelectCmd);
	
				SELECT ChangeCount INTO ChangeCnt FROM HREPL_Changes;
		
				-- Accumulate changes in total
				ChangeTotal := ChangeTotal + ChangeCnt;
				
				-- Stop counting changes when the threshold has been met
				EXIT WHEN ChangeTotal > Threshold;

			END LOOP;
			
		END IF;
	
		-- Determine the number of defined XactSets that are pending
		SELECT COUNT(DISTINCT Poll_PollID) INTO PendingCnt FROM HREPL_POLL;
	
		-- If the number of XactSets exceeds 3, set LRIsActive in HREPL_XactSetJob
		-- to 0.  Either the log reader is not running and XactSets are accumulating,
		-- or the log reader is not keeping up.  In either case, we can fall back to
		-- the larger Interval and Threshold values used when the log reader is not
		-- actively processing XactSets.
		IF PendingCnt > 3 THEN
			UPDATE HREPL_XactSetJob
			SET XactSetJob_LRIsActive = 0;
			XactSetJobLRIsActive := 0;
		END IF;
		
		-- Set Interval based upon parity of LRIsActive
		IF XactSetJobLRIsActive = 1 THEN
			Interval := XactSetJobLRInterval/1440;	
			Threshold := XactSetJobLRThreshold;
		ELSE
			Interval := XactSetJobInterval/1440;	
			Threshold := XactSetJobThreshold;
		END IF;	
	
		-- If the total change count for the current interval exceeds the threshold value
		-- create a new XactSet and set inactivity count = 0. If not, increment the inactivity count.
		IF (ChangeTotal > Threshold)  THEN
			BEGIN
				-- Populate the poll table with new XactSet
				HREPL.PopulatePollTable;
		
			EXCEPTION
				WHEN OTHERS THEN NULL;	
			END;

			UPDATE HREPL_XactSetJob
			SET XactSetJob_InactivityCount = 0;
		
		ELSE
			XactSetJobInactivityCount := XactSetJobInactivityCount + 1;
		
			-- If the inactivity count > 3, the change volume is not large enough to support
			-- aggressive scheduling of the XactSet job.  Insure that less aggressive
			-- scheduling is being used.
			IF XactSetJobInactivityCount > 3 THEN 
		
				UPDATE HREPL_XactSetJob
				SET XactSetJob_InactivityCount = 0,
					XactSetJob_LRIsActive = 0;
			    
				Interval := XactSetJobInterval/1440;	 
			    
			ELSE
		
				UPDATE HREPL_XactSetJob
				SET XactSetJob_InactivityCount = XactSetJobInactivityCount;
			
			END IF;
		END IF;	
	END;	
	END IF;
		
	-- Get the Job number of the job from the job queue
	BEGIN
		SELECT	JOB INTO Jobno
		FROM	USER_JOBS 
		WHERE	LOG_USER = USER
		AND	WHAT = 'HREPL.XactSetJob;';
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN RAISE;
	END;
	
	-- Update job interval to reflect current interval
	DBMS_JOB.INTERVAL(
		Jobno,
		'SYSDATE + ' || TO_CHAR(Interval) 
	);
	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
	
END XactSetJob;
-----------------------------------------------------------------------------------
--
--  Name:    PollEnd
--  Purpose: PollEnd request signifies that the change entries identified with the current 
--           interval have been successfully entered into the store and forward database
--           and can be deleted from the article log tables.
--  Input:
--           argLSN       	IN RAW(10)      LSN from distributor that was associated
--                              	        with this poll interval   	
--  Output:
--  Notes:   This request causes those entries of the article log tables represented in the
--           Poll Table and having the current pollid to be deleted from both their article log
--           tables and from the Poll Table. The last request value is updated to reflect a
--           PollEnd request.   
--
-----------------------------------------------------------------------------------
PROCEDURE PollEnd
(
	argLSN      IN RAW
)
AS
	SQLCommand		VARCHAR2(500);
	LogTable		VARCHAR2(255);
	CurrentPollID	NUMBER;
	TableIDs 		number_tab;
	InstanceIDs		number_tab;
	IDCount 		BINARY_INTEGER;
	PublisherLSN 	RAW(10);

BEGIN
	-- Put the published tableIDs in a PL/SQL table of IDs
	HREPL.GetTableIDs(TableIDs, InstanceIDs);

	-- Get the current Poll ID
	SELECT Publisher_CurrentPollid INTO CurrentPollID FROM HREPL_Publisher;
	
	IDCount := TableIDs.COUNT;
	-- For each table represented in the ID list  
	FOR id_ind IN 1 .. IDCount
   	LOOP
   	
   		LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
   														 MatchStringY, TO_CHAR(InstanceIDs(id_ind)));
   	
		BEGIN
			-- Generate command to delete from the article log those entries appearing in the
			-- Poll Table with the current PollID 
			SQLCommand := 'DELETE FROM ' || LogTable || ' l ' ||
		                      'WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p ' ||
		                      '              WHERE CHARTOROWID(l.ROWID) = p.Poll_ROWID '  ||
 		                      '              AND p.Poll_PollID = :Pollid)';

			HREPL.ExecuteCommandForPollID(SQLCommand, CurrentPollID);
				
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;
	END LOOP;
	
	FOR POLLID IN (SELECT CurrentPollid FROM DUAL)
	LOOP
		-- Delete from HREPL_Event those entries appearing in the Poll Table 
		-- with the current PollID.
		DELETE FROM HREPL_Event e
		WHERE EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p
		              WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID
 		              AND p.Poll_PollID = POLLID.CurrentPollID);
		
		-- Delete entries from the Poll Table having the current Pollid
		DELETE FROM HREPL_Poll
		WHERE Poll_PollID = POLLID.CurrentPollID;
	END LOOP;	
	
	-- Drop all views associated with articles that are marked as UnPublishPending.
	-- Note:  We cannot perform these drops in UnPublish table, since UnPublish
	--        table can execute concurrently with PollBegin and the querying 
	--        of published tables by the log reader.  PollEnd, however, executes
	--        synchronously with respect to these activities, so can be used
	--        to cleanup log tables and views that are no longer needed.
	HREPL.CleanupLogsandViews;
	
	-- Mark the last request as PollEnd, and update the Publisher LSN
	-- to reflect the LSN committed at the publisher.  
	UPDATE	HREPL_Publisher
	SET	Publisher_PollInProcess = NoPollInProcess,
	    Publisher_LSN = argLSN;

	-- Commit transaction
	COMMIT;

EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
	
END PollEnd;
-----------------------------------------------------------------------------------
--
--  Name:    CleanupLogsandViews
--  Purpose: To drop all log views and log tables that have a drop pending 
--  Input:
--           none 	
--  Output:
----------------------------------------------------------------------------------
PROCEDURE CleanupLogsandViews
AS
	SQLCommand	VARCHAR2(500);
	LogView		VARCHAR2(30);
	LogTable	VARCHAR2(30);
	FuncName	VARCHAR2(30);

	CURSOR		view_cur
	IS
		SELECT  Published_ArticleID,
				Published_TableID,
				Published_ArticleInstance	
		FROM HREPL_PublishedTables
		WHERE Published_ArticleDropPending = 1;
		
	CURSOR		log_cur
	IS
		SELECT  Published_ArticleID,
				Published_TableID,
				Published_LogInstance
		FROM HREPL_PublishedTables
		WHERE Published_LogDropPending = 1;
	
BEGIN
	FOR view_rec IN view_cur

	LOOP
		-- Generate the log view for the article
		LogView     :=	REPLACE(LogViewTemplate, MatchString, TO_CHAR(view_rec.Published_ArticleID));
		LogView     :=	REPLACE(LogView, MatchStringY, TO_CHAR(view_rec.Published_TableID));
		LogView     :=	REPLACE(LogView, MatchStringZ, TO_CHAR(view_rec.Published_ArticleInstance)); 
	
		-- Delete the log view for the unpublished article 
		BEGIN
			SQLCommand := 'DROP VIEW ' || LogView;
			HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			-- Ignore errors
			WHEN OTHERS THEN NULL;	
		END;
		
		-- Generate the function to obtain long length for the article
		FuncName     :=	REPLACE(ArticleFuncLongLen, MatchString, TO_CHAR(view_rec.Published_ArticleID));
		FuncName     :=	REPLACE(FuncName, MatchStringY, TO_CHAR(view_rec.Published_TableID));
		FuncName     :=	REPLACE(FuncName, MatchStringZ, TO_CHAR(view_rec.Published_ArticleInstance)); 
	
		-- Delete the function for the unpublished article 
		BEGIN
			SQLCommand := 'DROP FUNCTION ' || FuncName;
			HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			-- Ignore errors
			WHEN OTHERS THEN NULL;	
		END;
		
	END LOOP;
	
	FOR log_rec IN log_cur

	LOOP
		-- Generate the log table name for the article
		LogTable    :=	REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(log_rec.Published_TableID));
		LogTable    :=	REPLACE(LogTable, MatchStringY, TO_CHAR(log_rec.Published_LogInstance)); 
	
		-- Delete the log table for the dropped article 
		BEGIN
			SQLCommand := 'DROP TABLE ' || LogTable;
			HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			-- Ignore errors
			WHEN OTHERS THEN NULL;	
		END;
	END LOOP;
		
	-- Delete all entries from HREPL_PublishedTables with pending drops
	BEGIN
		DELETE FROM HREPL_PublishedTables
		WHERE Published_LogDropPending = 1
		OR	  Published_ArticleDropPending = 1;	
	EXCEPTION
		-- Ignore errors
		WHEN OTHERS THEN NULL;	
	END;
		
	  
END CleanupLogsandViews;
-----------------------------------------------------------------------------------
--
--  Name:    SnapshotBegin
--
--  Purpose: Deposit an article SyncInit entry in HREPL_Event.
--
--  Input:
--		 argTableOwner			IN VARCHAR2		Table owner	
--	     argTableName			IN VARCHAR2		Table name
--	  	 argPublicationID		IN NUMBER		Publication ID
--		 argArticleID			IN NUMBER		Article ID
--	     argDirectory			IN VARCHAR2		Location of BCP and Schema files
--		 argScriptSch			IN VARCHAR2		Name of .sch file
--		 argScriptIdx			IN VARCHAR2		Name of index script
--	     argSyncCommand			IN VARCHAR2		Synchronization command
--	     argPreScript			IN VARCHAR2		Name of pre command script
--	     argPostScript			IN VARCHAR2		Name of post command script
--	     argCreationScriptPath	IN VARCHAR2		Name of creation script path
--	     argFtpAddress			IN VARCHAR2		FTP address
--	     argFtpPort				IN VARCHAR2		FTP port
--	     argFtpSubdirectory		IN VARCHAR2		FTP subdirectory
--	     argFtpLogin			IN VARCHAR2		FTP login
--	     argFtpPassword			IN VARCHAR2		FTP password
--	     argAlternateSnapshotFolder	IN VARCHAR2	Alternate snapshot folder
--	     argCompressSnapshot	IN VARCHAR2	Snapshot folder with compressed snapshot
--		
--  Output:
--  
--  Notes:   The SnapshotBegin request is issued by the snapshotAgent just before it begins to
--	     gather snapshot data for an article.  A SyncInit entry is added to the HREPL_Event
--	     table that will be used to identify an interval as a reconciliation interval for the
--	     article.  Entries from HREPL_Event are marked in HREPL_Poll just as entries
--	     from the article log tables are marked, when a PollBegin request is made.  This allows
--	     the SyncInit entry to be associated with a particular polling interval. 
-----------------------------------------------------------------------------------
PROCEDURE SnapshotBegin
(
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argPublicationID	IN NUMBER,
	argArticleID		IN NUMBER,
	argDirectory		IN VARCHAR2,
	argScriptSch		IN VARCHAR2,
	argScriptIdx		IN VARCHAR2,
	argSyncCommand		IN VARCHAR2,
	argPreScript		IN VARCHAR2,
	argPostScript		IN VARCHAR2,
	argCreationScriptPath	IN VARCHAR2,
	argFtpAddress			IN VARCHAR2,
	argFtpPort				IN VARCHAR2,
	argFtpSubdirectory		IN VARCHAR2,
	argFtpLogin				IN VARCHAR2,
	argFtpPassword			IN VARCHAR2,
	argAlternateSnapshotFolder	IN VARCHAR2,
	argCompressSnapshot	IN VARCHAR2
)
AS
	SnapshotSeq NUMBER := 0;
	TableID NUMBER := 0;
	EntryTime NUMBER := round(to_number((SYSDATE - BASETIME) * SecondsInaDay));

BEGIN
	-- Pull sequence value from HREPL_SEQ
	SELECT HREPL_SEQ.nextval INTO SnapshotSeq from dual;
	
	-- Determine the table ID
	SELECT DISTINCT Published_TableID INTO TableID FROM HREPL_PublishedTables
	WHERE Published_Owner = argTableOwner 
	AND   Published_Table = argTableName
	AND   Published_ArticleID = argArticleID;
	
	-- Delete any other synchronizing commands from HREPL_Event for this article
	-- that have not as yet been assigned to an xactset.
	DELETE FROM HREPL_Event e
	WHERE Event_Article_ID = argArticleID
	AND   Event_Table_ID   = TableID
	AND   Event_Operation  IN (SYNCINIT, SYNCDONE, INRECONCILIATION)
	AND   NOT EXISTS (SELECT p.POLL_POLLID FROM HREPL_POLL p
		          WHERE CHARTOROWID(e.ROWID) = p.Poll_ROWID);
	
	-- Make SyncInit entry in HREPL_Event
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, TableID, SYNCINIT, SnapshotSeq, NULL, NULL, EntryTime,
		argDirectory, argScriptSch, argScriptIdx, argSyncCommand, argPreScript, 
		argPostScript, argCreationScriptPath, argFtpAddress, argFtpPort, argFtpSubdirectory, 
		argFtpLogin, argFtpPassword, argAlternateSnapshotFolder, argCompressSnapshot);
		
	-- Make InReconciliation entry in HREPL_Event
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, TableID, INRECONCILIATION, SnapshotSeq, NULL,
		NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
		NULL);

  	-- Commit transaction
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
	
END SnapshotBegin;
-----------------------------------------------------------------------------------
--
--  Name:    SnapshotEnd
--
--  Purpose: The SnapshotEnd request deposits a SYNCDONE entry in HREPL_Event.
--  Input:
--		 argTableOwner	IN VARCHAR2		Table owner	
--	     argTableName	IN VARCHAR2		Table name
--	  	 argPublicationID IN NUMBER		Publication ID
--		 argArticleID	IN NUMBER		Article ID
--  Output:
--
--  Notes:   This request is issued by the Snapshot Agent when it has finished
--	     gathering the snapshot for the article. A SyncDone entry is added to the HREPL_Event
--	     table that will be used to indicate that the reconciliation period has concluded.
--	     Entries from HREPL_Event are marked in HREPL_Poll just as entries from the
--	     article log tables are marked, when a PollBegin request is made.  This allows
--	     the SyncDone entry to be associated with a particular polling interval.     
--
-----------------------------------------------------------------------------------
PROCEDURE SnapshotEnd
(
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argPublicationID	IN NUMBER,
	argArticleID		IN NUMBER
)
AS
	SnapshotSeq NUMBER := 0;
	TableID NUMBER := 0;
	EntryTime NUMBER := round(to_number((SYSDATE - BASETIME) * SecondsInaDay));

BEGIN
	-- Pull sequence value from HREPL_SEQ
	SELECT HREPL_SEQ.nextval INTO SnapshotSeq from dual;
	
	-- Determine the table ID
	SELECT DISTINCT Published_TableID INTO TableID FROM HREPL_PublishedTables
	WHERE Published_Owner = argTableOwner 
	AND   Published_Table = argTableName
	AND   Published_ArticleID = argArticleID;
	
	-- Make SyncDone entry in HREPL_Event
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, TableID,	SYNCDONE, SnapshotSeq,
		NULL, NULL, EntryTime, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
		NULL, NULL, NULL, NULL);

  	-- Commit transaction
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;
	
END SnapshotEnd;
-----------------------------------------------------------------------------------
--
--  Name:    PublishTable
--  Purpose: Publish an Oracle table to SQL Server by:
--
--           (1)  The article view is created for both snapshot and transactional
--                publications.
--           
--           (2)  If this is a transactional publication, the log view for the
--                article is also created.
--
--           (3)  If this is a transactional publication and an ORACLE publisher,
--                and this is the first transactional article associated with a table, or if this
--                is a transactional publication and an ORACLE GATEWAY publisher,
--                the article log table and triggers for the table are also created.
--
--           (4)  If this is a transactinal publication, and entry for the article
--                is added to the HREPL_PublishedTables table.  NOTE:  No entry is
--                made in this table for tables published in snapshot publications.
--
--           (5)  PublishTable is also called to update the views, triggers and log tables
--                of existing published tables.  For ORACLE GATEWAY publishers, views, triggers
--                and log tables are recreated.  For ORACLE publishers, only the article view
--                is recreated.
--   
--  Input:
--		 argTableOwner		IN VARCHAR2	Table owner
--	     argTableName		IN VARCHAR2	Table name
--		 argTableID			IN NUMBER	TableID
--	     argTriggerStyle	IN NUMBER	Trigger style
--										    0 = SUCCESS
--											NON-ZERO = Error code
--       argRecreateTriggers  IN NUMBER   Flag indicating if triggers and article log table are to
--                                      be created for a transactional publication.  
--                                      1 = create triggers and log table, even if they exits
--                                      0 = only create triggers and log table if they don't exist  
--		 argArticleView		IN VARCHAR2 Name of article view
--       argReplFreq        IN NUMBER   Snapshot frequency -- if 0, transactional publication
--       argTimestamp		IN VARCHAR2 Tick count obtained at the distributor prior to the call
--		 argInstance		IN NUMBER	Instance ID
--       argColumnMask      IN RAW      Column Mask for article
--       argTriggerMask     IN RAW      Column Mask used to specify the columns to include in the change tracking trigger
--       argFilterClause    IN LONG     Filter clause for views 
--  Output:
--           
--  Notes:   This procedure is used to publish an Oracle table to SQL Server.
--	     Once the procedure successsfully executes, change capturing will begin
--	     for the table.   
--
-----------------------------------------------------------------------------------
PROCEDURE PublishTable
(
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argTableID			IN NUMBER,
	argTriggerStyle		IN NUMBER,
	argRecreateTriggers	IN NUMBER,
	argArticleView		IN VARCHAR2,
	argReplFreq         IN NUMBER,
	argTimestamp		IN VARCHAR2,
	argInstance			IN NUMBER,
	argColumnMask       IN RAW,
	argTriggerMask		IN RAW,	
	argFilterClause     IN LONG 
)
AS
	TableIsPublished NUMBER(10);
	TableIDInUse	 NUMBER(10);
	ColCount	 NUMBER;
	PKCount		 NUMBER := 0;
	EntryCount	 NUMBER := 0;
	EntryTableID	 NUMBER := NULL;
	ColName string_tab;
	ColType string_tab;
	ColMask number_tab;
	TriggerMask number_tab;
	DotLoc		INTEGER;
	ArticleLog	VARCHAR2(255);
	LogView		VARCHAR2(255);
	AdminUser	VARCHAR2(255) := USER;
	ViewDelimiter 	CHAR := '_';
	PosID		NUMBER := 15;
	PosDelimiter	NUMBER;
	ArticleID	NUMBER;
	LogInstance	NUMBER := 0;
	MaxLogInstance NUMBER := 0;
	FuncName	VARCHAR2(30);
	
BEGIN
	-- Set time of meta data update in HREPL_Publisher
	UPDATE HREPL_Publisher
	SET Publisher_Timestamp = argTimestamp;
	
	-- Create PL/SQL tables containing the column names and column types
	-- for the published table
	HREPL.PopulateColumnTables (argTableOwner, argTableName, ColCount, ColName, ColType);
	
	-- Create PL/SQL table containing the table columns to be included in the article
	HREPL.SetColumnsIncluded (argColumnMask, ColMask);
	
	-- Cleanup article view from any previous attempt to create it
	HREPL.DropView(argArticleView);
	
	-- Always create the article view
	HREPL.CreateView (argTableName, argTableOwner, argArticleView, ColCount, ColName, ColType, ColMask, argFilterClause);

	-- If this is a snapshot publication, only the article view is needed.  COMMIT and return success.
	IF argReplFreq <> 0 THEN
		COMMIT;
		RETURN;
	END IF;
	
	-- Obtain the articleID from the aticle view name
	PosDelimiter := INSTR(argArticleView, ViewDelimiter, PosID);
	ArticleID := TO_NUMBER(SUBSTR(argArticleView, PosID, PosDelimiter - PosID));
	
	-- Get current MAX log table instance for any existing log tables for the table
	BEGIN
		SELECT MAX(DISTINCT Published_LogInstance)
		INTO MaxLogInstance
		FROM HREPL_PublishedTables
		WHERE Published_TableID = argTableID;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	IF MaxLogInstance IS NULL THEN
		MaxLogInstance := 0;
	END IF;	
	
	-- Get current valid log table instance for an existing log table with no drop pending
	BEGIN
		SELECT DISTINCT Published_LogInstance
		INTO LogInstance
		FROM HREPL_PublishedTables
		WHERE Published_TableID = argTableID
		AND Published_Table = argTableName
		AND Published_Owner = argTableOwner
		AND Published_LogDropPending = 0;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	IF LogInstance IS NULL THEN
		LogInstance := 1;
	END IF;	

	-- If there are any entries for the article in HREPL_PublishedTables, mark them as 'Pending drop'.  This
	-- can occur if there was a previously unsuccessful attempt to create the article, or if an existing
	-- article is being modified.
	BEGIN  
		UPDATE HREPL_PublishedTables
		SET Published_ArticleDropPending = 1
		WHERE Published_TableID = argTableID
		AND Published_Table = argTableName
		AND Published_Owner = argTableOwner
		AND Published_ArticleID = ArticleID;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	-- Generate the name of the log view. Always use the instance ID passed in the call when
	-- creating the log view.  This is needed to identify log views of published articles that
	-- have changed.
	SELECT REPLACE(argArticleView, 'ARTICLE', 'LOG') INTO LogView from dual;
	LogView := LogView || '_' || TO_CHAR(argInstance);
	
	-- Construct the base name for the log table.  The instance identifier will be added later
	-- based upon whether a new log table needs to be created are not.
	SELECT REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(argTableID)) INTO ArticleLog from dual;

	-- Cleanup log view from any previous attempt to create it.
	-- NOTE:  This will only delete views created in previous failed attempts, or views that exist
	--        because the distributor or publisher has been restored and the two are not synchronized.
	--        This will not delete published views that are being updated to reflect more recent
	--        changes.
	HREPL.DropView(LogView);
	
	-- If we aren't forcing a recreate for the triggers, and valid triggers and an article log already
	-- exists and the log table does not have an associated pending drop, create the log view, COMMIT, and return success.
	IF argRecreateTriggers = 0 AND (HREPL.HasValidTriggers(argTableName, argTableOwner, argTableID, LogInstance) > 0) THEN

		SELECT REPLACE(ArticleLog, MatchStringY, TO_CHAR(LogInstance)) INTO ArticleLog from dual;
	
		-- Create the log view for the log table 
		HREPL.CreateView (ArticleLog, AdminUser, LogView, ColCount, ColName, ColType, ColMask, argFilterClause);

		-- Create function to return the length of long columns
		HREPL.CreateGetLongLenFunction(ArticleID, argTableID, argInstance, argTableOwner, argTableName, ColCount,
			ColName, ColType, ColMask);

		-- Add entry to HREPL_PublishedTables for this instance of the article
		INSERT INTO HREPL_PublishedTables
		VALUES(argTableOwner, argTableName, argTableID, ArticleID, LogInstance, argInstance, 0, 0);

		COMMIT;
		RETURN;
	
	END IF;
	
	-- Triggers if they exist are being dropped. In HREPL_PublishedTables,
	-- mark the log table as having an associated pending drop.  The actual
	-- drop of the log table will be done from the log reader during PollEnd
	-- processing.
	BEGIN
		UPDATE HREPL_PublishedTables
		SET    Published_LogDropPending = 1,
			   Published_ArticleDropPending = 1
		WHERE Published_TableID = argTableID;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	-- Increment the max log instance to differentiate it from all other log tables for
	-- the published table that may exist
	MaxLogInstance := MaxLogInstance + 1;
	
	-- Generate the new table name for the log
	SELECT REPLACE(ArticleLog, MatchStringY, TO_CHAR(MaxLogInstance)) INTO ArticleLog from dual;
	
	-- Cleanup from any previous attempts to create the triggers
	HREPL.TableCleanup( argTableOwner, argTableName, argTableID);
	
	-- Create PL/SQL table containing the table columns to be included in the trigger
	HREPL.SetColumnsIncluded (argTriggerMask, TriggerMask);

	-- Cleanup log from any previous attempt to create it.
	-- NOTE:  This will only delete logs created in previous failed attempts, or logs that exist
	--        because the distributor or publisher has been restored and the two are not synchronized.
	--        This will not delete published logs that are being updated to reflect more recent
	--        changes.
	HREPL.DropLog(ArticleLog);

	-- Create an article log table for the table
	HREPL.CreateTableLog (ArticleLog, argTriggerStyle, ColCount, ColName, ColType, TriggerMask);

	-- Create an article log table index
	HREPL.CreateTableIndex (ArticleLog);

	-- Create function to return the length of long columns
	HREPL.CreateGetLongLenFunction(ArticleID, argTableID, argInstance, argTableOwner, argTableName, ColCount,
		ColName, ColType, ColMask);

	-- Create the log view for the log table 
	HREPL.CreateView (ArticleLog, AdminUser, LogView, ColCount, ColName, ColType, ColMask, argFilterClause);

	-- Create triggers of the appropriate style for the table
	HREPL.CreateTableTriggers (argTableOwner, argTableName, argTableID, ArticleLog, argTriggerStyle, ColCount, ColName, ColType, TriggerMask);
	
	-- Add entry to HREPL_PublishedTables for this instance of the article
	INSERT INTO HREPL_PublishedTables
	VALUES(argTableOwner, argTableName, argTableID, ArticleID, MaxLogInstance, argInstance, 0, 0);

	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
	
		-- If this is a snapshot publication, only the article view, if created, needs to be dropped.
		IF argReplFreq <> 0 THEN
			ROLLBACK;
			HREPL.DropView(argArticleView);
			RAISE;
			RETURN;
		END IF;

		-- If this is a transactional publication, and the table associated with the article was previously published,
		-- just drop the article and log views if they were created.
		IF argRecreateTriggers = 0 AND (HREPL.HasValidTriggers(argTableName, argTableOwner, argTableID, LogInstance) > 0) THEN
			ROLLBACK;
			HREPL.DropView(argArticleView);
			HREPL.DropView(LogView);
			RAISE;
			RETURN;
		END IF;
		
		-- Generate the function declaration 
		FuncName     :=	REPLACE(ArticleFuncLongLen, MatchString, TO_CHAR(ArticleID));
		FuncName     :=	REPLACE(FuncName, MatchStringY, TO_CHAR(argTableID));
		FuncName     :=	REPLACE(FuncName, MatchStringZ, TO_CHAR(argInstance));
		
		-- If this is a transactional publication, and the table associated with the article was previously published,
		-- just drop the article and log views and function if they were created.
		IF argRecreateTriggers = 0 AND (HREPL.HasValidTriggers(argTableName, argTableOwner, argTableID, LogInstance) > 0) THEN
			ROLLBACK;
			HREPL.DropView(argArticleView);
			HREPL.DropView(LogView);
			HREPL.DropFunction(FuncName);
			RAISE;
			RETURN;
		END IF;

		-- Otherwise, drop everything that may have been created.
		ROLLBACK;
		HREPL.DropView(argArticleView);
		HREPL.DropView(LogView);
		HREPL.DropLog(ArticleLog);
		HREPL.DropFunction(FuncName);
		HREPL.TableCleanup( argTableOwner, argTableName, argTableID);
		RAISE;
		
END PublishTable;
-----------------------------------------------------------------------------------
--
--  Name:    ValidateRowFilter
--  Purpose: Pre-validate a user supplied row filter 
--
--           (1)  Create a temporary article view identifying those columns
--                that will appear in the article, using the passed filter
--				  clause as the where clause.
--
--           (2)  Execute a select from the view that will never return any rows.
--
--			 (3)  Return success if the select succeeds.  Otherwise, raise error.
--                NOTE:  A special PRAGMA is used to force an error to be raised
--				         for error -932, invalid datatypes.  MSDAORA won't throw this error.
--    
--  Input:
--		 argTableOwner		IN VARCHAR2	Table owner
--	     argTableName		IN VARCHAR2	Table name
--       argColumnMask      IN RAW      Column Mask for article
--       argFilterClause    IN LONG     Filter clause to be validated
--  Output:
--           
--  Notes:   This procedure is used to verify a proposed article row filter.
--
-----------------------------------------------------------------------------------
PROCEDURE ValidateRowFilter
(
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argColumnMask		IN RAW,	
	argFilterClause     IN LONG 
)
AS
	ColCount	NUMBER;
	ViewID		NUMBER;
	ColName		string_tab;
	ColType		string_tab;
	ColMask		number_tab;
	ViewName	VARCHAR2(30);
	SQLCommand 	DBMS_SQL.VARCHAR2S;
	EInconsistentDatatype EXCEPTION;
	PRAGMA EXCEPTION_INIT (EInconsistentDatatype, -00932);
	
BEGIN
	-- Get a sequence number to make temporary view name unique
	SELECT HREPL_Stmt.nextval INTO ViewID from dual;
	ViewName := REPLACE(ArticleTempView, MatchString, TO_CHAR(ViewID));
	
	-- Create PL/SQL tables containing the column names and column types
	-- for the table
	HREPL.PopulateColumnTables (argTableOwner, argTableName, ColCount, ColName, ColType);
	
	-- Create PL/SQL table containing the table columns to be included in the article
	HREPL.SetColumnsIncluded (argColumnMask, ColMask);
	
	-- Create the temporary view
	HREPL.CreateView (argTableName, argTableOwner, ViewName, ColCount, ColName, ColType, ColMask, argFilterClause);

	-- Select from the temporary view.  Some errors are not discovered until
	-- a select against the view is executed.  Add the where clause so the
	-- query doesn't return any rows.
	HREPL_ExecuteCommand('SELECT 1 FROM ' || ViewName || ' WHERE 0 = 1');

	-- Drop the temporary view
	HREPL_ExecuteCommand('DROP VIEW ' || ViewName);
	
EXCEPTION
	WHEN EInconsistentDatatype THEN
		RAISE_APPLICATION_ERROR(EInconsistentDatatypes,StrInconsistentDatatypes);
	WHEN OTHERS THEN RAISE;	
		
END ValidateRowFilter;
-----------------------------------------------------------------------------------
--
--  Name:    UnPublishTable
--  Purpose: UnPublish an Oracle table to SQL Server by    
--           (1) Deleting replication triggers for the table
--           (2) Deleting article entries from HREPL_EVENT and HREPL_POLL
--	         (3) Deleting the entries and dropping the article log table for the table
--           (4) Removing the entry from the table identifying published tables  	
--  Input:
--		 argTableOwner	IN VARCHAR2 Table owner	
--	     argTableName	IN VARCHAR2	Table name
--	     argTableID		IN NUMBER	Table ID
--										0 = SUCCESS
--										NON-ZERO = Error code
--       argArticleView	IN VARCHAR2 View name
--       argDropTriggers IN NUMBER  Flag to drop triggers 1=YES, 0=NO
--		 argTimestamp	IN VARCHAR2 Publisher timestamp
--
--  Output:
--  Notes:   This procedure is used to unpublish an Oracle table to SQL Server.
--	     Once the procedure successsfully executes, change capturing for the
--	     table will stop. The routine will mark the article as having an unpublish
--       pending in HREPL_PublishedTables, but will not drop the views or log table.
--       To prevent problems with ongoing change processing, these drops, if required,
--       will be done during log reader pollend processing, when we can guarantee
--       that the tables and views are not being accessed by the log reader.
--
--	     The table id is used to generate the name of the article log table to drop.
--
--       When UnPublishTable is called for an article in a snapshot publication,
--       the only action that has an effect is dropping the article view.   
--
-----------------------------------------------------------------------------------
PROCEDURE UnPublishTable
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER,
	argArticleView	IN VARCHAR2,
	argDropTriggers IN NUMBER,
	argTimestamp	IN VARCHAR2
)
AS
	ViewDelimiter 	CHAR := '_';
	PosID		NUMBER := 15;
	PosDelimiter	NUMBER;
	ArticleID	NUMBER;
	PubArtID	NUMBER := NULL;
	SQLCommand	VARCHAR2(500); 
BEGIN
	-- Set time of meta data update in HREPL_Publisher
	UPDATE HREPL_Publisher
	SET Publisher_Timestamp = argTimestamp;
	
	-- Obtain the articleID from the aticle view name
	PosDelimiter := INSTR(argArticleView, ViewDelimiter, PosID);
	ArticleID := TO_NUMBER(SUBSTR(argArticleView, PosID, PosDelimiter - PosID));
	
	-- Always drop the article view
	BEGIN
		SQLCommand := 'DROP VIEW ' || argArticleView;
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		-- Ignore errors
		WHEN OTHERS THEN NULL;	
	END;
	
	-- If the article is not in HREPL_PublishedTables, the article publication
	-- is a snapshot publication, and only the view needs to be dropped.
	SELECT DISTINCT Published_ArticleID 
	INTO PubArtID
	FROM HREPL_PublishedTables
	WHERE Published_ArticleID = ArticleID;
	
	IF PubArtID IS NULL THEN
		RETURN;
	END IF;
	
	-- In HREPL_PublishedTables, mark the transactional article as having 
	-- an associated pending drop.  The actual drop of the view will be done 
	-- from the log reader during PollEnd processing.
	BEGIN
		UPDATE HREPL_PublishedTables
		SET    Published_ArticleDropPending = 1
		WHERE Published_TableID = argTableID
		AND Published_Table = argTableName
		AND Published_Owner = argTableOwner
		AND Published_ArticleID = ArticleID;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	-- If the triggers are being dropped as well, in HREPL_PublishedTables
	-- mark the log table as having an associated pending drop.  The actual
	-- drop of the log table will be done from the log reader during PollEnd
	-- processing.
	IF argDropTriggers = 1 THEN
		BEGIN
			UPDATE HREPL_PublishedTables
			SET    Published_LogDropPending = 1
			WHERE Published_TableID = argTableID
			AND Published_Table = argTableName
			AND Published_Owner = argTableOwner;
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;
	END IF;			

	-- Delete entries in HREPL_EVENT that are associated with this article
	BEGIN
		DELETE FROM HREPL_Event
		WHERE EVENT_ARTICLE_ID = ArticleID;
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;		

	-- If flag set, drop triggers as well
	BEGIN 
		IF argDropTriggers = 1 THEN
			HREPL.TableCleanup( argTableOwner, argTableName, argTableID);
		END IF;
	EXCEPTION	
		WHEN OTHERS THEN NULL;
	END;	
		
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN COMMIT;

END UnPublishTable;
-----------------------------------------------------------------------------------
--
--  Name:    GetTableIDs
--  Input:
--	     argTableIDs	IN OUT DMS_SQL.VARCHAR2S
--		 argInstanceIDs	IN OUT DMS_SQL.VARCHAR2S
--  Output:
--  Purpose: Populate Table ID Table with table and instance IDs of published tables
--           
--  Input:
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE GetTableIDs
(
	argTableIDs IN OUT number_tab,
	argInstanceIDs IN OUT number_tab
)
AS
	IDCnt NUMBER := 0;
	CURSOR		ID_cur
	IS
		SELECT DISTINCT Published_TableID,
						Published_LogInstance
		FROM HREPL_PublishedTables
		WHERE Published_LogDropPending = 0;
	 
BEGIN
	FOR ID_rec IN ID_cur

	-- Put into an index-by table the table IDs to be polled 
	LOOP
		IDCnt := IDCnt + 1;
		argTableIDs(IDCnt) := ID_rec.Published_TableID;
		argInstanceIDs(IDCnt) := ID_rec.Published_LogInstance;
	END LOOP;

END GetTableIDs;
-----------------------------------------------------------------------------------
--
--  Name:    SetColumnsIncluded
--  Input:
--       argColumnMask      IN     RAW 		 
--	     argColumnsIncluded	IN OUT number_tab
--  Output:
--  Purpose: Populate argColumnsIncluded Table with column information.  The
--           index of the table represents the column ordinal of the table.
--           A value of 1 indicates that the column is included in the associated 
--           article, and a value of 0 indicates that it is not included.  This
--           table is used when generating article views as well as the triggers
--           and article log. If the column mask is null, it is assumed that all
--           bits are set.
--           
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE SetColumnsIncluded
(
	argColumnMask       IN RAW,
	argColumnsIncluded	IN OUT number_tab 
)
AS
	ColumnMask VARCHAR(256);
	ColVal NUMBER(10,0);
	ColLength NUMBER;
	ColIdx NUMBER;
	ColCnt NUMBER := 1;
	ColChar CHAR;
BEGIN
	IF argColumnMask IS NULL THEN
		SELECT RPAD('F',256,'F') INTO ColumnMask from dual;
	ELSE	
		SELECT RAWTOHEX(argColumnMask) INTO ColumnMask from dual;
	END IF;
		
	ColLength := LENGTH(ColumnMask);
	ColIdx := (ColLength-1)*4 + 1;
	
	LOOP
		ColChar := SUBSTR(ColumnMask, ColCnt, 1);
		ColVal := ToNumFromHex(ColChar);

		FOR BitCnt IN 1..4
		LOOP
			argColumnsIncluded(ColIdx) := 1;
			IF ((FLOOR(ColVal/2))*2 = ColVal) THEN
				argColumnsIncluded(ColIdx) := 0;
			END IF;
			ColVal := FLOOR(ColVal / 2);
			ColIdx := ColIdx + 1;				  
		END LOOP;

		ColCnt := ColCnt + 1;
		ColIdx := (ColLength-ColCnt)*4 +1;
		EXIT WHEN ColCnt > ColLength;
	END LOOP;
END SetColumnsIncluded;
-----------------------------------------------------------------------------------
--
--  Name:    PopulatePollTable
--  Purpose: This procedure populates the Poll Table with entries identifying entries
--	     in article log tables and the event table.
--           
--  Input:
--	     
--  Output:
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
PROCEDURE PopulatePollTable
AS
	TableIDs	number_tab;
	InstanceIDs	number_tab;
	IDCount 	BINARY_INTEGER;
	SQLCommand 	DBMS_SQL.VARCHAR2S;
	SelectCmd 	VARCHAR2(1000);
	LogTable	VARCHAR2(255);
	CurrentPollid	NUMBER;

	CURSOR	table_cur

	IS
		SELECT e.Event_Table_ID, e.Event_Article_ID, e.ROWID
		FROM HREPL_Event e
		WHERE e.Event_Operation = TABLEROWCNT
		AND NOT EXISTS (SELECT * FROM HREPL_POLL t WHERE t.POLL_ROWID = CHARTOROWID(e.ROWID));
 
BEGIN

	-- Lock table HREPL_MUTEX to insure that this code is treated as a critical section.
	LOCK TABLE HREPL_MUTEX IN EXCLUSIVE MODE;
		
	-- Pull the next value from the HREPL_POLLID sequence
	SELECT HREPL_POLLID.nextval INTO CurrentPollid FROM dual;
	
	-- Get the Table IDs of the published tables
	HREPL.GetTableIDs(TableIDs, InstanceIDs);
	IDCount := TableIDs.COUNT;
	
	-- Generate command to obtain consistent set of changes from all of the article log
	-- tables as well as the HREPL_EVENT table, excluding any subscription markers in
	-- HREPL_Event that are pending (Event_Operation = 12).
	SelectCmd :=
				'INSERT INTO HREPL_POLL ( SELECT e.ROWID, :Pollid, ' || 
				'e.Event_Operation, 0, 0 ' ||
				'FROM HREPL_Event e ' ||
				'WHERE e.Event_Operation <> 12 and e.Event_Operation <> 10 and ' ||
				'NOT EXISTS (SELECT * FROM HREPL_POLL t WHERE t.POLL_ROWID = CHARTOROWID(e.ROWID)) ';
				
	HREPL.AddToSQLCommand(SelectCmd, SQLCommand);

	FOR id_ind IN 1 .. IDCount
	LOOP
	
		LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
														 MatchStringY, TO_CHAR(InstanceIDs(id_ind)));
	
		SelectCmd :=
				' UNION ALL SELECT a.ROWID, :Pollid, ' || 
				' 0, 0, ' || TO_CHAR(TableIDs(id_ind)) || 
				' FROM ' || LogTable ||
				' a WHERE NOT EXISTS (SELECT * FROM HREPL_POLL t WHERE t.POLL_ROWID = CHARTOROWID(a.ROWID)) ';
	
		HREPL.AddToSQLCommand(SelectCmd, SQLCommand);
		
	END LOOP;
	
	-- Generate row count for row count requests
	FOR	table_rec
	IN	table_cur

	LOOP
		-- Deposit the row count event as well as row count into the poll table.
		-- Use the article view in the request, so that the count is accurate
		-- for articles having row filters as well.
		
		SelectCmd :=
				' UNION ALL SELECT CHARTOROWID(''' || table_rec.ROWID || '''), :Pollid, ' || 
				' 10, COUNT(*), 0 ' ||
				' FROM HREPL_ARTICLE_' ||  TO_CHAR(table_rec.Event_Article_ID) || '_' || TO_CHAR(table_rec.Event_Table_ID);

		HREPL.AddToSQLCommand(SelectCmd, SQLCommand);

	END LOOP;
	HREPL.AddToSQLCommand(')', SQLCommand);
	
	-- Populate HREPL_POLL with a consistent set of change commands from the article logs
	-- and events from the event table.  Note that for row count events, the counts as well 
	-- are determined as part of the consistent set, so that they are accurate based upon 
	-- the current set of committed changes.
	HREPL.ExecuteCommandTableForPollID(SQLCommand, CurrentPollid);
	
	-- If the SyncDone for an InReconciliation entry in this xactset, is not also in
	-- the xactset, duplicate the InReconciliation command in HREPL_Event, so that
	-- it can be picked up in the next xactset as well.
	FOR PollID IN (SELECT CurrentPollid FROM DUAL)
	LOOP
		INSERT INTO HREPL_EVENT 
		(   SELECT 0, e.Event_Publication_ID, e.Event_Article_ID, e.Event_Table_ID, e.Event_Operation, e.Event_Seq,
			   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL,
			   NULL, NULL, NULL, NULL, NULL, NULL, NULL, NULL
			FROM HREPL_EVENT e, HREPL_POLL p
			WHERE e.Event_Operation = 11
			AND p.POLL_ROWID = CHARTOROWID(e.ROWID) 
			AND p.POLL_Pollid = PollID.CurrentPollid
			AND   (e.Event_Publication_ID, e.Event_Article_ID, e.Event_Table_ID) NOT IN
			(     SELECT  v.Event_Publication_ID, v.Event_Article_ID, v.Event_Table_ID 
				  FROM HREPL_EVENT v, HREPL_POLL x
				  WHERE v.Event_Operation = 6
				  AND   x.POLL_Pollid = PollID.CurrentPollid
				  AND   x.POLL_ROWID = CHARTOROWID(v.ROWID)
			)
		);
	END LOOP;           
	
	-- NOTE:  This procedure must be treated as a 'critical section'.  The lock on HREPL_MUTEX is used
	--        to enforce the critical section.  Since the lock is released when either a COMMIT or
	--        ROLLBACK executes, no COMMIT or ROLLBACK should appear within the procedure.
	
	-- Commit transaction and exit critical section
	COMMIT;
	
EXCEPTION
		WHEN OTHERS THEN
			ROLLBACK;
			RAISE;	
	
END PopulatePollTable;
-----------------------------------------------------------------------------------
--
--  Name:    SeqFixupForUniqueColumnUpdates
--
--  Purpose: This procedure modifies the article log entries associated with the
--	     update of unique columns, making the associated HREPL_SEQ value of all
--		 equal to the largest HREPL_SEQ value associated with the update statement.
--           
--  Input:
--	     
--  Output:
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
PROCEDURE SeqFixupForUniqueColumnUpdates
AS
	TableIDs	number_tab;
	InstanceIDs	number_tab;
	IDCount 	BINARY_INTEGER;
	SelectCmd	VARCHAR2(500);
	LogTable	VARCHAR2(255);
BEGIN
	-- Get the Table IDs of the published tables
	HREPL.GetTableIDs(TableIDs, InstanceIDs);
	IDCount := TableIDs.COUNT;
  
	FOR id_ind IN 1 .. IDCount
	LOOP
	
		LogTable := REPLACE( REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(TableIDs(id_ind))),
														 MatchStringY, TO_CHAR(InstanceIDs(id_ind)));
	
		SelectCmd :=
		'UPDATE ' || LogTable ||
		' a SET a.HREPL_SEQ = (SELECT max(b.HREPL_SEQ) ' ||
		' FROM ' || LogTable  ||
		' b WHERE a.HREPL_STMT = b.HREPL_STMT) WHERE a.HREPL_STMT > 0';
   	
		HREPL_ExecuteCommand(SelectCmd);
	END LOOP;
	
END SeqFixupForUniqueColumnUpdates;
-----------------------------------------------------------------------------------
--
--  Name:    CreateTableTriggers
--  Purpose: Creating replication triggers for a table
--	     
--  Input:
--		 argTableOwner	IN VARCHAR2 Table owner
--	     argTableName	IN VARCHAR2	Table name
--		 argTableID		IN NUMBER   Table ID
--	     argLogTable	IN VARCHAR2	Log table name
--	     argTriggerStyle	IN NUMBER	Trigger style
--	     argColumnCount	IN NUMBER	Table column count
--	     argColNames	IN string_tab	Column names
--	     argColTypes	IN string_tab	Column types
--	     argColMask		IN number_tab	Column mask
--  Output:
--  Notes:   This procedure is used to create replication triggers for a table.
--
-----------------------------------------------------------------------------------
PROCEDURE CreateTableTriggers
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER,
	argLogTable		IN VARCHAR2,
	argTriggerStyle	IN NUMBER,
	argColumnCount	IN NUMBER,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argColMask		IN number_tab
)
AS
BEGIN
	-- Create a row trigger of the appropriate style for the table
	HREPL.CreateRowTrigger (argTableOwner, argTableName, argTableID, argLogTable, argColumnCount, argColNames,
		argColTypes, argTriggerStyle, argColMask);
	
	-- Create statement level update trigger
	HREPL.CreateStmtTrigger(argTableOwner, argTableName, argTableID);
	
END CreateTableTriggers;
-----------------------------------------------------------------------------------
--
--  Name:    CreateRowTrigger
--  Purpose: Generate an appropriate row level trigger based upon the passed trigger style.
--
--  Input:
--		 argTableOwner	IN VARCHAR2 Table owner
--	     argTableName	IN VARCHAR2	Table name
--		 argTableID		IN NUMBER	Table ID
--	     argLogTable	IN VARCHAR2	Log table name
--	     argColumnCount	IN NUMBER	Column count of published table
--	     argColNames	IN string_tab	Column names
--	     argColTypes	IN string_tab	Column types 
--	     argTriggerStyle	IN NUMBER	Trigger style
--	     argColMask		IN number_tab	Column mask
--  Output:
--  Notes:   This procedure is used to create the supported table trigger.  The SQL
--	     command to create the trigger is accumulated into an index-by table with type
--	     DBMS_SQL.VARCHAR2S so that it may exceed 32767 bytes.  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateRowTrigger
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER,
	argLogTable		IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argTriggerStyle IN NUMBER,
	argColMask		IN number_tab
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
BEGIN
	-- Generate row trigger command table
	HREPL.GenerateRowTrigger(SQLCommand, argTableOwner, argTableName, argTableID, argLogTable, argColumnCount, argColNames,
		argColTypes, argTriggerStyle, argColMask);
		
	-- Execute command table
	HREPL.ExecuteCommandTable(SQLCommand);

	-- Check for create trigger for compilation errors
	HREPL.CheckCompilationErrors(REPLACE(ArticleTriggerRowTemplate, MatchString, TO_CHAR(argTableID)), 'TRIGGER');

END CreateRowTrigger;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateRowTrigger
--  Purpose: To create row trigger based upon the pased trigger style
--	     
--  Input:
--	     argCommandTable	IN OUT DBMS_SQL.VARCHAR2S Command table
--		 argTableOwner	IN VARCHAR2 Table owner 
--	     argTableName	IN VARCHAR2	Table name
--	     argTableID		IN NUMBER	Table ID
--		 argLogTable	IN VARCHAR2 Log table name
--	     argColumnCount	IN NUMBER	Column count of published table
--	     argColNames	IN string_tab	Column names
--	     argColTypes	IN string_tab	Column types
--	     argTriggerStyle	IN NUMBER	Trigger style
--	     argColMask		IN number_tab	Column mask
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateRowTrigger
(
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S,
	argTableOwner	IN VARCHAR2, 
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER,
	argLogTable		IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argColNames	IN string_tab,
	argColTypes	IN string_tab,
	argTriggerStyle IN NUMBER,
	argColMask		IN number_tab
)
AS
	Trigger		VARCHAR2(4000);
	KeyList		string_tab;
	KeyValue	string_tab;
	GATEWAY BOOLEAN := argTriggerStyle IN (1, 2);
	FirstColumn BOOLEAN := TRUE;
	
BEGIN
	Trigger :=
 
	'CREATE OR REPLACE TRIGGER ' || REPLACE(ArticleTriggerRowTemplate, MatchString, TO_CHAR(argTableID)) ||
	' AFTER DELETE OR INSERT OR UPDATE OF ';
	HREPL.AddToSQLCommand(Trigger, argCommandTable);

    FOR col_ind IN 1 .. argColumnCount
   	LOOP
   	    IF argColMask(col_ind) = 1 THEN
   	        IF NOT FirstColumn = TRUE THEN
   	   	        HREPL.AddToSQLCommand(', "' || argColNames(col_ind) || '"', argCommandTable);
   	   	    ELSE
   	   	        FirstColumn := FALSE;
   	   	        HREPL.AddToSQLCommand('  "' || argColNames(col_ind) || '"', argCommandTable);
   	   	    END IF;
   	   	END IF;        
	END LOOP;

	Trigger := ' ON "' || argTableOwner || '"."' || argTableName || '" ' ||
	'FOR EACH ROW ';
	HREPL.AddToSQLCommand(Trigger, argCommandTable);
	
	Trigger :=
	
	' DECLARE ' ||
	   'Seq NUMBER := 0; ' ||
	   'Stmt NUMBER; ' ||
	   'Op1  NUMBER := 0; ' ||
	   'Op2  NUMBER := 0; ' ||
	   'EntryTime  NUMBER := round(to_number((SYSDATE - HREPL.BaseTimeT) * 86400)); ' ||
	'BEGIN IF HREPL.SQLORIGINATOR = FALSE THEN BEGIN ';

	HREPL.AddToSQLCommand(Trigger, argCommandTable);
	
    Trigger := 
	   'SELECT HREPL_SEQ.nextval INTO Seq from dual; IF UPDATING AND (';
	HREPL.AddToSQLCommand(Trigger, argCommandTable);

	-- Insert primary key list into SQL command
	HREPL.GeneratePKClause (argCommandTable, argTableOwner, argTableName);

	Trigger :=                    ') THEN ' ||

	'SELECT HREPL_STMT.CURRVAL INTO Stmt from dual; ' ||
	'Op1 := ''' || DeleteUnqOp || '''; ' ||
	'OP2 := ''' || InsertUnqOp || '''; ' ||	

   	'ELSE ';
	HREPL.AddToSQLCommand(Trigger, argCommandTable);

	Trigger :=
	   	'IF INSERTING THEN ' ||
	      	    'Op2 := ''' || InsertOp || '''; ' ||
	   	'ELSIF DELETING THEN ' ||
	      	    'Op1 := ''' || DeleteOp || '''; ' ||
	   	'ELSE ' ||
	      	    'Op1 := ''' || UpdateOldOp || '''; ' ||
	      	    'Op2 := ''' || UpdateNewOp || '''; ' ||
	   	'END IF;';
	HREPL.AddToSQLCommand(Trigger, argCommandTable);
	
    HREPL.AddToSQLCommand('END IF; ', argCommandTable);
    
	-- Generate insert command for delete and old values from updates, if needed 
	HREPL.GenerateValueClause (argCommandTable, 'old', argColumnCount, argColNames, argColTypes, argTriggerStyle,
		UpdateAndDeleteCommand, argTableID, argLogTable, argTableOwner, argTableName, argColMask);
		
	-- Generate insert command for inserts and new values from updates 
	HREPL.GenerateValueClause (argCommandTable, 'new', argColumnCount, argColNames, argColTypes, argTriggerStyle,
		NewCommand, argTableID, argLogTable, argTableOwner, argTableName, argColMask);

	-- Terminate the SQL command
	HREPL.AddToSQLCommand(' END; END IF; END;', argCommandTable);

 	
END GenerateRowTrigger;
-----------------------------------------------------------------------------------
--
--  Name:    CreateStmtTrigger
--  Purpose: To create statement trigger for the support of primary key update
--
--  Input:
--		 argTableOwner	IN VARCHAR2 Table owner
--	     argTableName	IN VARCHAR2	Table name
--	     argTableID		IN NUMBER	Table ID
--  Output:
--  Notes:   This procedure is used to create the supported table trigger.  The SQL
--	     command to create the trigger is accumulated into an index-by table with type
--	     DBMS_SQL.VARCHAR2S so that it may exceed 32767 bytes.  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateStmtTrigger
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
BEGIN
	-- Generate stmt trigger command table
	HREPL.GenerateStmtTrigger(SQLCommand, argTableOwner, argTableName, argTableID);
	
	-- Execute command table
	HREPL.ExecuteCommandTable(SQLCommand);

	-- Check for create trigger for compilation errors 
	HREPL.CheckCompilationErrors(REPLACE(ArticleTriggerStmtTemplate, MatchString, TO_CHAR(argTableID)), 'TRIGGER');
 
END CreateStmtTrigger;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateStmtTrigger
--  Purpose: To create statement trigger for the support of primary key update
--	     	     
--  Input:
--  	 argCommandTable	IN OUT 		DBMS_SQL.VARCHAR2S,
--		 argTableOwner	IN VARCHAR2 Table owner 
--	     argTableName	IN VARCHAR2	Table name
--	     argTableID		IN NUMBER	Table ID
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateStmtTrigger
(
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S,
	argTableOwner	IN VARCHAR2, 	 
	argTableName	IN VARCHAR2,
	argTableID	IN NUMBER
)
AS
	SQLCommand	VARCHAR2(500);
			
BEGIN
	SQLCommand :=
 		'CREATE OR REPLACE TRIGGER ' || REPLACE(ArticleTriggerStmtTemplate, MatchString, TO_CHAR(argTableID)) ||
		' BEFORE UPDATE OF ';

	HREPL.AddToSQLCommand(SQLCommand, argCommandTable);

	-- Insert primary key list into SQL command
	HREPL.GeneratePKList (argCommandTable, argTableOwner, argTableName);

	SQLCommand := ' ON "' || argTableOwner || '"."' || argTableName || '" ' ||
		'DECLARE Stmt number; ' ||
		'BEGIN ' ||
		'IF HREPL.SQLORIGINATOR = FALSE THEN ' ||
			'SELECT HREPL_STMT.nextval INTO Stmt FROM dual; ' ||
		'END IF; ' ||
		'END;';

	HREPL.AddToSQLCommand(SQLCommand, argCommandTable);
 
END GenerateStmtTrigger;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateValueClause
--  Purpose: Add value clause to SQL command table
--	     
--  Input:
--	     argCommandTable	IN OUT DBMS_SQL.VARCHAR2S	SQL command table
--	     argType		IN VARCHAR2		'old', 'new'
--	     argColumnCount	IN NUMBER		Column count of table
--	     argColNames	IN string_tab	Columns names table
--	     argColTypes	IN string_tab	Column types table	
--	     argTriggerStyle	IN NUMBER	Trigger style
--	     argCommand		IN INTEGER		Command type
--	     argTableID		IN NUMBER		Table ID
--		 argLogTable	IN VARCHAR2		Log table name
--		 argTableOwner	IN VARCHAR2		Table owmer
--	     argTableName   IN VARCHAR2		Table name
--	     argColMask     IN number_tab	Column mask
--  Output:
--  Notes:   This procedure is used to add the table specific portion of the
--	     value clause to the statement level create trigger command
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateValueClause
(
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S,
	argType			IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argTriggerStyle IN NUMBER,
	argCommand		IN INTEGER,
	argTableID		IN NUMBER,
	argLogTable		IN	VARCHAR2,
	argTableOwner	IN VARCHAR2,
	argTableName    IN VARCHAR2,
	argColMask		IN number_tab
)
AS
	UPKONLY		BOOLEAN := argTriggerStyle = 1;
	DPKONLY		BOOLEAN := argTriggerStyle = 1;
	GATEWAY		BOOLEAN := argTriggerStyle IN (1, 2);
	GATEWAYXCALL BOOLEAN := argTriggerStyle IN (2);
	ORACLE	    BOOLEAN := argTriggerStyle = 0;
	ColumnMatch 	NUMBER;
	ValueClause   	VARCHAR2(20000);
	InsertCommand 	VARCHAR2(20000);
	DotLoc		INTEGER;
		
BEGIN
	-- If Update and Delete command, or Delete command, and primary keys on delete or
	-- Update command and primary keys on update, only retrieve key values	
	IF (((argCommand = UpdateAndDeleteCommand) AND DPKONLY) OR
	    ((argCommand = DeleteCommand)          AND DPKONLY) OR
	    ((argCommand = UpdateCommand)          AND UPKONLY)) THEN
		
		IF (argCommand = UpdateAndDeleteCommand) THEN
			InsertCommand := 'IF ((UPDATING AND (Stmt IS NOT NULL)) OR DELETING) ';
		ELSIF (argCommand = DeleteCommand) THEN
	 		InsertCommand := 'IF DELETING ';
		ELSE
			InsertCommand := 'IF UPDATING ';
		END IF;
		HREPL.AddToSQLCommand(InsertCommand, argCommandTable);

		InsertCommand := ' THEN INSERT INTO ' ||argLogTable ||
			' ( HREPL_SEQ, HREPL_STMT, HREPL_OPERATION, HREPL_POLLID, HREPL_ENTRYTIME ';
		HREPL.AddToSQLCommand(InsertCommand, argCommandTable);

		ValueClause := ' VALUES( Seq, Stmt, Op1, 0, EntryTime ';
			
		InsertCommand := ' ';
		FOR col_ind IN 1 .. argColumnCount
   		LOOP
			-- Determine whether the column is a primary key 
			SELECT COUNT(*) INTO ColumnMatch
			FROM all_cons_columns c, all_constraints s
			WHERE
				c.owner = s.owner AND
				c.constraint_name = s.constraint_name AND
				c.table_name = s.table_name AND
				s.table_name = argTableName AND
				s.owner = argTableOwner AND
				c.column_name = argColNames(col_ind) AND
				s.constraint_type = 'P';
		
			IF ColumnMatch = 1 THEN
				InsertCommand  := InsertCommand  || ', "' || argColNames(col_ind) || '"';		
				ValueClause := ValueClause || ', :' || argType || '."' || argColNames(col_ind) || '"';
			END IF;
		
		END LOOP;
		InsertCommand := InsertCommand || ')' || ValueClause || '); END IF;';
		HREPL.AddToSQLCommand (InsertCommand, argCommandTable);

	-- Otherwise generate insert command using the entire list of columns	
	ELSE
		IF (argCommand = UpdateAndDeleteCommand) THEN
			InsertCommand := 'IF UPDATING OR DELETING ' ;
		ELSIF (argCommand = DeleteCommand) THEN
	 		InsertCommand := 'IF DELETING ';
		ELSIF (argCommand = UpdateCommand) THEN
			InsertCommand := 'IF UPDATING ';
		ELSE
			InsertCommand :=  ' IF UPDATING OR INSERTING ';
		END IF;	
		HREPL.AddToSQLCommand(InsertCommand, argCommandTable);
						
		IF (argCommand = UpdateAndDeleteCommand) THEN
			InsertCommand := ' THEN INSERT INTO ' ||
			argLogTable || ' VALUES ( Seq, Stmt, Op1, 0, EntryTime ';
		ELSIF (argCommand = DeleteCommand) THEN
	 		InsertCommand := ' THEN INSERT INTO ' || argLogTable ||
			' VALUES ( Seq, Stmt, Op1, 0, EntryTime ';
		ELSIF (argCommand = UpdateCommand) THEN
			InsertCommand := ' THEN INSERT INTO ' || argLogTable ||
			' VALUES ( Seq, Stmt, Op1, 0, EntryTime ';
		ELSE
			InsertCommand :=  ' THEN INSERT INTO ' ||
			argLogTable || ' VALUES ( Seq, Stmt, Op2, 0, EntryTime ';
		END IF;	 
		HREPL.AddToSQLCommand(InsertCommand, argCommandTable);

		FOR col_ind IN 1 .. argColumnCount
   		LOOP
			-- If the trigger style is one that supports the non-transactional
			-- treatment of LOB data, then the value stored in the article
			-- log table for the column is the ROWID value rather than the
			-- column value.
			IF (argColMask(col_ind) = 1) THEN
				IF argColTypes(col_ind) IN ('LONG', 'LONG RAW', 'CLOB', 'NCLOB', 'BLOB', 'BFILE') THEN
					InsertCommand := ', :' || argType || '.ROWID';
				ELSE
					InsertCommand := ', :' || argType || '."' || argColNames(col_ind) || '"';
				END IF;
				HREPL.AddToSQLCommand (InsertCommand, argCommandTable);
			END IF;	
		
		END LOOP;
		HREPL.AddToSQLCommand('); END IF;', argCommandTable);

	END IF;
			 
END GenerateValueClause;
-----------------------------------------------------------------------------------
--
--  Name:    GeneratePKClause
--  Purpose: Add list of primary Key columns to SQL command
--	     
--  Input:
--	     argCommandTable	IN OUT DBMS_SQL.VARCHAR2S	SQL command table
--		 argTableOwner	IN	VARCHAR2		Name of table owner
--	     argTableName	IN  VARCHAR2		Name of published table
--  Output:
--  Notes:   This procedure is used to insert a list of primary key columns into
--	     a SQL command
--
-----------------------------------------------------------------------------------
PROCEDURE GeneratePKClause
(
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S,
	argTableOwner	IN VARCHAR2,	
	argTableName	IN VARCHAR2
)
AS
	PKClause	VARCHAR2(100);
	DotLoc		INTEGER;
	FirstTime	BOOLEAN;

	CURSOR		col_cur
		(OwnerName	IN VARCHAR2,
		 TableName	IN VARCHAR2)
	IS
	    SELECT DISTINCT column_name FROM (
		SELECT c.column_name
		FROM all_cons_columns c, all_constraints s
		WHERE
			c.owner = s.owner AND
			c.constraint_name = s.constraint_name AND
			c.table_name = s.table_name AND
			s.table_name = TableName AND
			s.owner = OwnerName AND
			((s.constraint_type = 'P') OR (s.constraint_type = 'U'))
		UNION
		SELECT c.column_name
		FROM all_ind_columns c, all_indexes s
		WHERE
			c.table_owner = s.table_owner AND
			c.table_name = s.table_name AND
			c.index_name = s.index_name AND
			c.index_owner = s.owner AND
			s.table_name = TableName AND
			s.table_owner = OwnerName AND
			s.uniqueness = 'UNIQUE' AND
			s.INDEX_TYPE = 'NORMAL');
	
BEGIN
	FirstTime := TRUE;
	
	-- Extract primary key column information from the data dictionary
	FOR col_rec IN
		col_cur (argTableOwner, argTableName)
	LOOP
		-- Generate list of primary keys
		IF FirstTime THEN
			PKClause := ':old."' || col_rec.column_name || '" != :new."' || col_rec.column_name || '"';
			FirstTime := false;
		ELSE
			PKClause := ' OR :old."' || col_rec.column_name || '" != :new."' || col_rec.column_name || '"';
		END IF;

		HREPL.AddToSQLCommand (PKClause, argCommandTable);	

	END LOOP;
	
END GeneratePKClause;
-----------------------------------------------------------------------------------
--
--  Name:    GeneratePKList
--  Purpose: Add list of primary Key columns to STMT trigger
--	     
--  Input:
--	     argCommandTable	IN OUT DBMS_SQL.VARCHAR2S	SQL command table
--		 argTableOwner	IN	VARCHAR2		Name of table owner
--	     argTableName	IN  VARCHAR2		Name of published table
--  Output:
--  Notes:   This procedure is used to insert a list of primary key columns into
--	     a the STMT trigger
--
-----------------------------------------------------------------------------------
PROCEDURE GeneratePKList
(
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S,
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2
)
AS
	PKList		VARCHAR2(50);
	DotLoc		INTEGER;
	FirstTime	BOOLEAN;

	CURSOR		col_cur
		(OwnerName	IN VARCHAR2,
		 TableName	IN VARCHAR2)
	IS
	    SELECT DISTINCT column_name FROM (
		SELECT c.column_name
		FROM all_cons_columns c, all_constraints s
		WHERE
			c.owner = s.owner AND
			c.constraint_name = s.constraint_name AND
			c.table_name = s.table_name AND
			s.table_name = TableName AND
			s.owner = OwnerName AND
			((s.constraint_type = 'P') OR (s.constraint_type = 'U'))
		UNION
		SELECT c.column_name
		FROM all_ind_columns c, all_indexes s
		WHERE
			c.table_owner = s.table_owner AND
			c.table_name = s.table_name AND
			c.index_name = s.index_name AND
			c.index_owner = s.owner AND
			s.table_name = TableName AND
			s.table_owner = OwnerName AND
			s.uniqueness = 'UNIQUE' AND
			s.INDEX_TYPE = 'NORMAL');
	
BEGIN
	FirstTime := TRUE;
	
	-- Extract primary key column information from the data dictionary
	FOR col_rec IN
		col_cur (argTableOwner, argTableName)
	LOOP
		-- Generate list of primary keys
		IF FirstTime THEN
			PKList := '"' || col_rec.column_name || '"';
			FirstTime := false;
		ELSE
			PKList := ', "' || col_rec.column_name || '"';
		END IF;

		HREPL.AddToSQLCommand (PKList, argCommandTable);	

	END LOOP;
	
END GeneratePKList;
-----------------------------------------------------------------------------------
--
--  Name:    CreateTableLog
--  Purpose: Creating replication article log table for a published table
--	     
--  Input:
--	     argLogTable	IN VARCHAR2		Log table name
--	     argTriggerStyle	IN NUMBER(10)	Trigger style
--	     argColCount	IN NUMBER	Column count
--	     argColNames	IN string_tab	Column names table
--	     argColTypes	IN string_tab	Column types table
--	     argColMask  	IN number_tab	Column mask			
--  Output:
--  Notes:   	  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateTableLog
(
	argLogTable		IN VARCHAR2,
	argTriggerStyle		IN NUMBER,
	argColCount		IN NUMBER,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argColMask	    IN number_tab
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
BEGIN
	-- Generate the command to create the article log table
	HREPL.GenerateCreateCommand (argLogTable, argColCount, SQLCommand, argColNames, argColTypes, argTriggerStyle, argColMask);

	-- Execute the command table
	HREPL.ExecuteCommandTable(SQLCommand);

END CreateTableLog;
-----------------------------------------------------------------------------------
--
--  Name:    CreateTableIndex
--  Purpose: Creating replication article log table index
--	     
--  Input:
--	     argLogTable	IN VARCHAR2		Log table name
--  Output:
--  Notes:   	  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateTableIndex
(
	argLogTable		IN VARCHAR2
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
BEGIN
	-- Generate the command to create the article log table index on HREPL_STMT
	HREPL.GenerateIndexCommand (argLogTable, SQLCommand);

	-- Execute the command table
	HREPL.ExecuteCommandTable(SQLCommand);

END CreateTableIndex;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateCreateCommand
--  Purpose: Creating replication article log table for a published table
--	     
--  Input:
--	     argLogTable	IN VARCHAR2			Log table name
--	     argColumnCount	IN NUMBER			Column count of published table
--	     argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S	SQL command table for creating article log
--	     argColNames	IN string_tab			Column names table
--	     argColTypes	IN string_tab			Column types table
--	     argTriggerStyle	IN NUMBER			Trigger stype
--	     argColMask  	IN number_tab			Column mask		
--  Output:
--  Notes:   This procedure is used to create the article log table.
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateCreateCommand
(
	argLogTable		IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argTriggerStyle	IN NUMBER,
	argColMask	    IN number_tab
)
AS
	GATEWAY BOOLEAN := argTriggerStyle IN (1, 2);
	ORACLE BOOLEAN := argTriggerStyle = 0;
	GATEWAYXCALL BOOLEAN := argTriggerStyle IN (2);
	ColumnDescription	VARCHAR2(250);	
BEGIN
	-- Generate the command to create the article log table 
	ColumnDescription := 

		'CREATE TABLE "' || argLogTable ||
		      '" (HREPL_SEQ NUMBER NULL, '            			||
			'HREPL_STMT NUMBER NULL, '		  					||
			'HREPL_OPERATION NUMBER NOT NULL, '					||
			'HREPL_POLLID NUMBER NULL, '						||
			'HREPL_ENTRYTIME NUMBER NULL';                                          

	HREPL.AddToSQLCommand(ColumnDescription, argSQLCommand);
	
	-- Generate columns specific to published table
	-- Use column name and type information from published table  
	FOR col_ind IN 1 .. argColumnCount
   	LOOP
		-- If the trigger style is one which supports the non-transactional
		-- treatment of LOB data, the column type for the article log Column must
		-- be ROWID rather than the corresponding LOB type from the published
		-- table.
		IF (argColMask(col_ind) = 1) THEN
			IF (argColTypes(col_ind) IN ('LONG', 'LONG RAW', 'CLOB', 'NCLOB', 'BLOB', 'BFILE')) THEN
				ColumnDescription := ', "' || argColNames(col_ind) ||
				'"   ' || 'ROWID'  || ' NULL';
			ELSE
				ColumnDescription := ', "' || argColNames(col_ind) ||
				'"   ' || argColTypes(col_ind)  || ' NULL';
			END IF;
		
			-- Add column description to SQL command
			HREPL.AddToSQLCommand(ColumnDescription, argSQLCommand);
		END IF;	

	END LOOP;

	-- Terminate SQL command
	HREPL.AddToSQLCommand (' ) LOGGING ', argSQLCommand);

END GenerateCreateCommand;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateIndexCommand
--  Purpose: Creating replication article log table index 
--
--  Input:
--	     argLogTable	IN VARCHAR2			Log table name
--	     argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S	SQL command table for creating article log
--  Output:
--  Notes:   This procedure is used to create the article log table index on HREPL_STMT
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateIndexCommand
(
	argLogTable		IN VARCHAR2,
	argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S
)
AS
	IndexDescription	VARCHAR2(250);
BEGIN
	-- Generate the command to create the article log table index on HREPL_STMT 
	IndexDescription := 
		'CREATE INDEX "' || argLogTable || 'X" ON "' || argLogTable || '"(HREPL_STMT) ';  
	HREPL.AddToSQLCommand(IndexDescription, argSQLCommand);

END GenerateIndexCommand;
-----------------------------------------------------------------------------------
--
--  Name:    CreateGetLongLenFunction
--  Purpose: Creating HREPL_GETLONGLEN function for an article
--	     
--  Input:
--	     argArticleID		IN NUMBER		Article ID
--	     argTableID			IN NUMBER		Table ID
--	 	 argArticleInstance IN NUMBER		Article Instance
--		 argTableOwner		IN VARCHAR2		Table owner
--		 argTableName		IN VARCHAR2		Table name
--	     argColCount		IN NUMBER		Column count
--	     argColNames		IN string_tab	Column names table
--	     argColTypes		IN string_tab	Column types table
--	     argColMask  		IN number_tab	Column mask			
--  Output:
--  Notes:   	  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateGetLongLenFunction
(
	argArticleID		IN NUMBER,
	argTableID  		IN NUMBER,
	argArticleInstance	IN NUMBER,
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argColumnCount		IN NUMBER,
	argColNames			IN string_tab,
	argColTypes			IN string_tab,
	argColMask			IN number_tab
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
	FuncName	VARCHAR2(30);
BEGIN
	-- Generate the function declaration 
	FuncName     :=	REPLACE(ArticleFuncLongLen, MatchString, TO_CHAR(argArticleID));
	FuncName     :=	REPLACE(FuncName, MatchStringY, TO_CHAR(argTableID));
	FuncName     :=	REPLACE(FuncName, MatchStringZ, TO_CHAR(argArticleInstance));
	
	-- Locate the LONG or LONG RAW column, if present in the article  
	FOR col_ind IN 1 .. argColumnCount
   	LOOP
		IF (argColMask(col_ind) = 1) AND
		   ((argColTypes(col_ind) = 'LONG') OR (argColTypes(col_ind) = 'LONG RAW'))
		THEN
			-- Drop function, ignore error
			HREPL.DropFunction(FuncName);
	
			-- Generate the command to create the article log table
			HREPL.GenerateFunctionGetLongLen (FuncName, argTableOwner, argTableName,
				col_ind, SQLCommand, argColNames, argColTypes, argColMask);

			-- Execute the command table
			HREPL.ExecuteCommandTable(SQLCommand);
			
		END IF;
	END LOOP;		

END CreateGetLongLenFunction;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateFunctionGetLongLen
--  Purpose: Creating function to query for length of long
--	     
--  Input:
--	 	 argFuncName		IN NUMBER			Function name
--		 argTableOwner		IN VARCHAR2			Table owner
--		 argTableName		IN VARCHAR2			Table name
--	     argColInd			IN NUMBER			Column index of long column
--	     argSQLCommand		IN OUT DBMS_SQL.VARCHAR2S	SQL command table for creating function
--	     argColNames		IN string_tab		Column names table
--	     argColTypes		IN string_tab		Column types table
--	     argColMask  		IN number_tab		Column mask		
--  Output:
--  Notes:   This procedure is used to create the article log table.
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateFunctionGetLongLen
(
	argFuncName			IN VARCHAR2,
	argTableOwner		IN VARCHAR2,
	argTableName		IN VARCHAR2,
	argColInd			IN NUMBER,
	argSQLCommand		IN OUT DBMS_SQL.VARCHAR2S,
	argColNames			IN string_tab,
	argColTypes			IN string_tab,
	argColMask			IN number_tab
)
AS
	FuncBody	VARCHAR2(500);	
BEGIN
	FuncBody := 
		'CREATE FUNCTION ' || argFuncName || '(ROW ROWID)'			||
		'RETURN NUMBER '                                            ||
		'AS '                                                       ||
		'BEGIN ';
	HREPL.AddToSQLCommand(FuncBody, argSQLCommand);
			
	IF (argColTypes(argColInd) = 'LONG') THEN
		FuncBody := 'DECLARE LONG_VAR LONG; ';
	ELSE
		FuncBody := 'DECLARE LONG_VAR LONG RAW; ';
	END IF;
	HREPL.AddToSQLCommand(FuncBody, argSQLCommand);
			
	FuncBody :=	
		'BEGIN '                                                             ||
			'SELECT "' || argColNames(argColInd) || '" INTO LONG_VAR '       ||
			'FROM "' || argTableOwner || '"."' || argTableName || '" '       ||
			'WHERE "' || argTableOwner || '"."' || argTableName ||
			'".ROWID = row; ';
	HREPL.AddToSQLCommand(FuncBody, argSQLCommand);
			
	IF (argColTypes(argColInd) = 'LONG') THEN
		FuncBody := 
			'RETURN LENGTH(LONG_VAR); '                                     ||
			'END; '                                                         ||
			'END; ';
	ELSE
		FuncBody := 
			'RETURN LENGTH(LONG_VAR)/2; '                                   ||
			'END; '                                                         ||
			'END; ';
	END IF;
	HREPL.AddToSQLCommand(FuncBody, argSQLCommand);

END GenerateFunctionGetLongLen;
-----------------------------------------------------------------------------------
--
--  Name:    AlterTableLog
--  Purpose: Modify article log tablespace
--	     
--  Input:
--	     argTableID		IN NUMBER	Table ID
--       argTablespace	IN VARCHAR2	Tablespace name
--
--  Output:
--  Notes:   	  
--
-----------------------------------------------------------------------------------
PROCEDURE AlterTableLog
(
	argTableID		IN NUMBER,
	argTableSpace	IN VARCHAR2
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
	ArticleLog	VARCHAR2(255); 
	LogInstance	NUMBER;
BEGIN
	SELECT DISTINCT Published_LogInstance
	INTO LogInstance
	FROM HREPL_PublishedTables
	WHERE Published_TableID = argTableID
	AND Published_LogDropPending = 0;
	
	ArticleLog := REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(argTableID));
	ArticleLog := REPLACE(ArticleLog, MatchStringY, TO_CHAR(LogInstance));

	-- Generate the command to create the article log table
	HREPL.AddToSQLCommand('ALTER TABLE ' || ArticleLog ||
							' MOVE TABLESPACE ' || argTableSpace, SQLCommand);
	
	-- Execute the command table
	HREPL.ExecuteCommandTable(SQLCommand);

END AlterTableLog;
-----------------------------------------------------------------------------------
--
--  Name:    CreateView
--  Purpose: Creating replication article view for a published table
--	     
--  Input:
--	     argTableName	IN VARCHAR2		Table name
--	     argTableOwner	IN VARCHAR2		Table owner
--	     argViewName	IN VARCHAR2		View name
--	     argColCount	IN NUMBER	Column count
--	     argColNames	IN string_tab	Column names table
--	     argColTypes	IN string_tab	Column types table
--	     argColMask  	IN number_tab	Column mask
--	     argFilterClause IN LONG		Row filter			
--  Output:
--  Notes:   	  
--
-----------------------------------------------------------------------------------
PROCEDURE CreateView
(
	argTableName	IN VARCHAR2,
	argTableOwner	IN VARCHAR2,
	argViewName		IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argColMask	    IN number_tab,
	argFilterClause IN LONG
)
AS
	SQLCommand	DBMS_SQL.VARCHAR2S;
BEGIN
	-- Generate the command to create a view
	HREPL.GenerateView (argTableName, argTableOwner, argViewName, argColumnCount, SQLCommand, argColNames, argColTypes, argColMask, argFilterClause);

	-- Execute the command table
	HREPL.ExecuteCommandTable(SQLCommand);

END CreateView;
-----------------------------------------------------------------------------------
--
--  Name:    GenerateView
--  Purpose: Creating a view for a published table
--	     
--  Input:
--	     argTableName	IN VARCHAR2				Table name
--	     argTableOwner	IN VARCHAR2				Table owner
--	     argViewName	IN VARCHAR2				View name
--	     argColumnCount	IN NUMBER			Column count of published table
--	     argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S	SQL command table for creating article log
--	     argColNames	IN string_tab			Column names table
--	     argColTypes	IN string_tab			Column types table
--	     argColMask  	IN number_tab			Column mask	
--	     argFilterClause IN LONG				Row filter		
--  Output:
--  Notes:   This procedure is used to create a view for the table.
--
-----------------------------------------------------------------------------------
PROCEDURE GenerateView
(
	argTableName	IN VARCHAR2,
	argTableOwner	IN VARCHAR2,
	argViewName		IN VARCHAR2,
	argColumnCount	IN NUMBER,
	argSQLCommand	IN OUT DBMS_SQL.VARCHAR2S,
	argColNames		IN string_tab,
	argColTypes		IN string_tab,
	argColMask	    IN number_tab,
	argFilterClause IN LONG
)
AS
	LOGVIEW BOOLEAN := argViewName LIKE 'HREPL_LOG%';
	FirstColumn BOOLEAN := TRUE;
	ColumnDescription	VARCHAR2(250);
	
BEGIN
	-- Generate the command to create the article view
	ColumnDescription := 'CREATE VIEW ' || argViewName || ' AS SELECT ';
	HREPL.AddToSQLCommand(ColumnDescription, argSQLCommand);
	
	-- Generate columns specific to published table
	-- Use column name and type information from published table  
	FOR col_ind IN 1 .. argColumnCount
   	LOOP
		IF (argColMask(col_ind) = 1) THEN
			IF NOT FirstColumn = TRUE THEN
				ColumnDescription := ', l."' || argColNames(col_ind) || '"';
			ELSE
				FirstColumn := FALSE;
				-- If generating the log view, include additional row entries for the log in the view
				IF LOGVIEW = TRUE THEN
					ColumnDescription :=  ' l."HREPL_SEQ", l."HREPL_STMT", l."HREPL_OPERATION", l."HREPL_POLLID", l."HREPL_ENTRYTIME", "' || argColNames(col_ind) || '"'; 
				ELSE
					ColumnDescription := 'l."' || argColNames(col_ind) || '"';
				END IF;	
			END IF;	
			-- Add column description to SQL command
			HREPL.AddToSQLCommand(ColumnDescription, argSQLCommand);
		END IF;	

	END LOOP;
	
	-- Add from clause to SQL command
	HREPL.AddToSQLCommand(' FROM "' || argTableOwner || '"."' || argTableName || '" l ', argSQLCommand);

	-- Incorporate the filter clause into the views.
	IF argFilterClause IS NOT NULL THEN
		HREPL.AddToSQLCommand(' WHERE ' || argFilterClause, argSQLCommand);
	END IF;	

END GenerateView;
-----------------------------------------------------------------------------------
--
--  Name:    AddToSQLCommand
--  Purpose: Transfer string to SQLCommand table
--	     
--  Input:
--	     argString		IN VARCHAR2			String to add to command table
--	     argCommandTable	IN OUT DBMS_SQL.VARCHAR2S	command table
--  Output:
--  Notes:   This procedure is used to assemble a SQL command into a command table
--
-----------------------------------------------------------------------------------
PROCEDURE AddToSQLCommand
(
	argString	IN VARCHAR2,
	argCommandTable	IN OUT DBMS_SQL.VARCHAR2S
)
AS
	StringLength	BINARY_INTEGER;
	StartPosition	BINARY_INTEGER := 1;	
BEGIN
	IF argString IS NOT NULL THEN
		StringLength := LENGTH(argString);
	
		LOOP
			argCommandTable(NVL(argCommandTable.LAST, 0) + 1) :=
				NextRow(argString, StartPosition, StringLength);
			EXIT WHEN StartPosition > StringLength;
		END LOOP;

	END IF;

END AddToSQLCommand;
-----------------------------------------------------------------------------------
--
--  Name:    NextRow
--  Purpose: Extract up to the next MAXLEN characters
--	     
--  Input:
--	     argStringIn 	IN VARCHAR2,
--	     argStartInOut 	IN OUT BINARY_INTEGER,
--	     argLenIn		IN BINARY_INTEGER
--  Output:
--
-----------------------------------------------------------------------------------
FUNCTION NextRow
(
	argStringIn 	IN VARCHAR2,
	argStartInOut	IN OUT BINARY_INTEGER,
	argLenIn	IN BINARY_INTEGER
) 
RETURN VARCHAR2 

AS
	StartPosition	BINARY_INTEGER := argStartInOut;	
BEGIN
	argStartInOut := LEAST(argLenIn + 1, argStartInOut + MAXLEN);
	RETURN SUBSTR(argStringIn, StartPosition, MAXLEN);
	
END NextRow;
-----------------------------------------------------------------------------------
--
--  Name:    PopulateColumnTables
--  Purpose: Populate the column tables containing the column names and
--	     types of the published table. 
--	     
--  Input:
--		 argTableOwner	IN VARCHAR2		Table owner
--	     argTableName	IN VARCHAR2		Table name
--	     argColCount	OUT NUMBER		Column count of published table
--	     argColNames	IN OUT string_tab	Column names
--	     argColTypes	IN OUT string_tab	column type strings		
--  Output:
--  Notes:   This procedure is used to create the article log table.
--
-----------------------------------------------------------------------------------
PROCEDURE PopulateColumnTables
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argColCount	OUT NUMBER,
	argColNames	IN OUT string_tab,
	argColTypes	IN OUT string_tab
)
AS
	TypeNM		VARCHAR2(128);
	DotLoc		INTEGER;
	ColCount	NUMBER;

	CURSOR		col_cur
		(OwnerName	IN VARCHAR2,
		 TableName	IN VARCHAR2)
	IS
		SELECT	Column_name,
				data_type,
				DECODE(CHAR_COL_DECL_LENGTH,NULL,DATA_LENGTH,CHAR_COL_DECL_LENGTH) as data_length,
				data_precision,
				data_scale
		FROM	all_tab_columns
		WHERE	owner = OwnerName
		  AND	table_name = TableName
		ORDER BY column_id;
	
BEGIN
	-- Extract column information from the data dictionary
	CoLCount := 0;

	FOR col_rec IN
		col_cur (argTableOwner, argTableName)

	LOOP
		-- Save column name and type for table create
		ColCount := ColCount + 1;
		argColNames (ColCOunt) := col_rec.column_name;

		-- Get basic type
		TypeNM := (col_rec.data_type);

		-- Add qualifiers to basic type as needed		
		IF is_string (TypeNM) OR is_raw(TypeNM) THEN
			TypeNM := TypeNM || '(' || TO_CHAR(col_rec.data_length) || ')';
	
		ELSIF is_number (TypeNM) THEN
			BEGIN
				IF (col_rec.data_scale <> 0) AND (col_rec.data_scale IS NOT NULL) THEN
					TypeNM := TypeNM || '(' || TO_CHAR(col_rec.data_precision) ||
					', ' || TO_CHAR(col_rec.data_scale) || ')';
				ELSIF (col_rec.data_precision <> 0) AND (col_rec.data_precision IS NOT NULL) THEN
					TypeNM := TypeNM || '(' || TO_CHAR(col_rec.data_precision) || ')';
				END IF;
			END;
		END IF;
		
		-- Save qualified string type for create table
		argColTypes (ColCount) := TypeNM;

	END LOOP;
  
	argColCount := ColCount;

END PopulateColumnTables;
-----------------------------------------------------------------------------------
--
--  Name:    TableCleanup
--  Purpose: Delete any triggers associated with a previous attempt to publish the table.
--	     
--  Input:
--		 argTableOwner	IN VARCHAR2		Table owner
--	     argTableName	IN VARCHAR2		Table name
--	     argTableID		IN NUMBER		Table ID
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE TableCleanup
(
	argTableOwner	IN VARCHAR2,
	argTableName	IN VARCHAR2,
	argTableID		IN NUMBER
)
AS
	SQLCommand	VARCHAR2(500);
	
BEGIN
	BEGIN
		-- Drop the row level trigger for the table if it exists, ignore error
		SQLCommand := 'DROP TRIGGER '|| REPLACE(ArticleTriggerRowTemplate, MatchString, TO_CHAR(argTableID)); 
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;

	BEGIN
		-- Drop the statement level trigger if it exists, ignore error
		SQLCommand := 'DROP TRIGGER ' || REPLACE(ArticleTriggerStmtTemplate, MatchString, TO_CHAR(argTableID)); 
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
			
END TableCleanup;
-----------------------------------------------------------------------------------
--
--  Name:    DropView
--  Purpose: Drop view
--	     
--  Input:
--		 argArticleView IN VARCHAR2		Article view name
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE DropView
(
	argArticleView IN VARCHAR2
)
AS
	SQLCommand	VARCHAR2(500);
	
BEGIN
	-- Delete view, ignore error
	BEGIN
		-- Drop the view if it exists, ignore error
		SQLCommand := 'DROP VIEW ' || argArticleView;
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
		
END DropView;
-----------------------------------------------------------------------------------
--
--  Name:    DropLog
--  Purpose: Drop log
--	     
--  Input:
--		 argArticleLog IN VARCHAR2		Article log name
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE DropLog
(
	argArticleLog IN VARCHAR2
)
AS
	SQLCommand	VARCHAR2(500);
	
BEGIN
	-- Delete log, ignore error
	BEGIN
		-- Drop the log if it exists, ignore error
		SQLCommand := 'DROP TABLE ' || argArticleLog;
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
		
END DropLog;
-----------------------------------------------------------------------------------
--
--  Name:    DropFunction
--  Purpose: Drop function 
--	     
--  Input:
--		 argFunctionName IN VARCHAR2		Function name
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE DropFunction
(
	argFunctionName IN VARCHAR2
)
AS
	SQLCommand	VARCHAR2(500);
	
BEGIN
	-- Delete function, ignore error
	BEGIN
		-- Drop the function if it exists, ignore error
		SQLCommand := 'DROP FUNCTION ' || argFunctionName;
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
		
END DropFunction;
-----------------------------------------------------------------------------------
--
--  Name:    InitPublisher
--  Purpose: Clean any article tracking tables or triggers.  Used to make sure
--           startup is clean.
--
--  Input:
--	     	
--  Output:
--			argRetVal		OUT NUMBER	    Return value
--           
--  Notes:  Best effort to make sure startup is clean.  No errors are returned.   
--
-----------------------------------------------------------------------------------
PROCEDURE InitPublisher
AS
	SQLCommand VARCHAR2(500);

	CURSOR	trigger_cur
	IS
		SELECT trigger_name
		FROM all_triggers
		WHERE
		owner = USER AND
		UPPER(trigger_name) LIKE '%HREPL_ARTICLE%';

	CURSOR	table_cur
	IS
		SELECT table_name, owner
		FROM all_all_tables
		WHERE
		owner = USER AND
		UPPER(table_name) LIKE '%HREPL_ARTICLE%' AND
		nested = 'NO';
		
	CURSOR	view_cur
	IS
		SELECT view_name
		FROM all_views
		WHERE
		owner = USER AND
		(UPPER(view_name) LIKE 'HREPL_ARTICLE%' OR 
		UPPER(view_name) LIKE 'HREPL_LOG%');
		
	CURSOR	func_cur
	IS
		SELECT object_name
		FROM all_objects
		WHERE
		owner = USER AND
		(UPPER(object_name) LIKE 'HREPL_LEN%' AND 
		object_type = 'FUNCTION');	

BEGIN
	-- Drop triggers
	FOR	trigger_rec
	IN	trigger_cur

	LOOP
		-- Delete any triggers owned by the replication administrator
		-- that are prefixed with 'HREPL_Article'
		BEGIN
			-- Drop the trigger if it exists, ignore error
			SQLCommand := 'DROP TRIGGER ' || trigger_rec.trigger_name;
		HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;

	END LOOP;

	-- Drop tracking tables
	FOR	table_rec
	IN	table_cur

	LOOP
		-- Delete any tables owned by the replication administrator
		-- that are prefixed with 'HREPL_Article'
		BEGIN
			-- Drop the tracking table if it exists, ignore error
			SQLCommand := 'DROP TABLE ' 
				|| table_rec.owner || '.' || table_rec.table_name; 
		HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;

	END LOOP;
	
	-- Drop views
	FOR	view_rec
	IN	view_cur

	LOOP
		-- Delete any views owned by the replication administrator
		-- that are prefixed with 'HREPL_Article' or 'HREPL_Log'
		BEGIN
			-- Drop the view if it exists, ignore error
			SQLCommand := 'DROP VIEW ' || view_rec.view_name;
		HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;

	END LOOP;
	
	-- Drop functions
	FOR	func_rec
	IN	func_cur

	LOOP
		-- Delete functions owned by the replication administrator
		-- that are prefixed with 'HREPL_LEN' 
		BEGIN
			-- Drop the function if it exists, ignore error
			SQLCommand := 'DROP FUNCTION ' || func_rec.object_name;
		HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;

	END LOOP;
	
	-- Drop public synonym for SetSQLOriginator, ignore error
	BEGIN
		SQLCommand := 'DROP PUBLIC SYNONYM MSSQLSERVERSETSQLORIGINATOR';
		HREPL_ExecuteCommand(SQLCommand);
	EXCEPTION
		WHEN OTHERS THEN NULL;
	END;
	
	COMMIT;

EXCEPTION
	WHEN OTHERS THEN NULL;

END InitPublisher;
-----------------------------------------------------------------------------------
--
--  Name:    RowCnt
--
--  Purpose: Deposit a row count entry in HREPL_Event.
--
--  Input:
--		 argTableOwner	IN VARCHAR2 Table owner	
--	     argTableName	IN VARCHAR2	Table name
--	  	 argPublicationID IN NUMBER Publication ID
--		 argArticleID	IN NUMBER	Article ID
--		 argCommandType	IN NUMBER	Command type:  35 or 69 (SQLSERVER_ARTICLE_CMD OR SQL_SUBSCRIPTION_VALIDATION)
--		 argCommand		IN VARCHAR2	Template for row count command to be executed at subscriber
--		 argSubscriptionLevel IN NUMBER	0 if request applies to all subscriptions, 1 if request applies to those
--                                      subscriptions identified with subscription markers.		
--  Output:
--
--  Notes:   The RowCnt request is generated when sp_ORAarticle_validation is called.  A RowCnt
--	     entry is added to the HREPL_Event table that will be later inserted into the
--	     MSrepl_commands table. Entries from HREPL_Event are marked in HREPL_Poll just as entries
--	     from the article log tables are marked, when a PollBegin request is made.  This allows
--	     the RowCnt entry to be associated with a particular polling interval. Note that the actual
--	     row count value is not determined at this time.  To be completed correctly for article
--	     views with row filtering, the count must be obtained when the poll interval that the
--	     event is associated with completes, as part of the same READ ONLY transaction. If there
--	     are any pending subscription markers in the event table, for the publication and article
--	     represented in the validation request, these are either activated or deleted depending
--	     upon whether the subscription level is 1 or 0 respectively. 
-----------------------------------------------------------------------------------
PROCEDURE RowCnt
(
	argTableOwner IN VARCHAR2,
	argTableName IN VARCHAR2,
	argPublicationID IN NUMBER,
	argArticleID IN NUMBER,
	argCommandType IN NUMBER,
	argCommand IN VARCHAR2,
	argSubscriptionLevel IN NUMBER
)
AS
	RowCntSeq NUMBER := 0;	
	TableID NUMBER := 0;
	EntryTime NUMBER := round(to_number((SYSDATE - BASETIME) * SecondsInaDay));

BEGIN
	-- Pull sequence value from HREPL_SEQ
	SELECT HREPL_SEQ.nextval INTO RowCntSeq FROM dual;
	
	-- Determine the table ID
	SELECT DISTINCT Published_TableID INTO TableID FROM HREPL_PublishedTables
	WHERE Published_Owner = argTableOwner 
	AND   Published_Table = argTableName;
	
	-- Make Row Count entry in HREPL_Event
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, TableID, TABLEROWCNT, RowCntSeq,
	0, argCommandType, EntryTIme, NULL, NULL, NULL, argCommand, NULL, NULL, NULL, NULL,
	NULL, NULL, NULL, NULL, NULL, NULL);

	IF argSubscriptionLevel = 0
	THEN
		-- If subscription level is 0, delete any associated pending subscription markers.
		--  markers. The validation command will be applied to all subscription.
		BEGIN
			DELETE HREPL_Event
			WHERE Event_Publication_ID = argPublicationID
			AND   Event_Operation = PENDINGSUBSCRIPTIONMARKER;
		EXCEPTION
			WHEN NO_DATA_FOUND THEN NULL;	
			WHEN OTHERS THEN RAISE;
		END;
	ELSE
		-- If subscription level is 1, mark any associated pending subscription markers
		-- as active, so that they are eligible for inclusion in the next poll interval.
		BEGIN
			UPDATE HREPL_Event
			SET   Event_Operation = ACTIVESUBSCRIPTIONMARKER
			WHERE Event_Publication_ID = argPublicationID
			AND   Event_Operation = PENDINGSUBSCRIPTIONMARKER;
	
		EXCEPTION
			WHEN NO_DATA_FOUND THEN NULL;	
			WHEN OTHERS THEN RAISE;
		END;
	END IF;	
	
	-- Commit transaction
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;

END RowCnt;
-----------------------------------------------------------------------------------
--
--  Name:    MarkSubscription
--
--  Purpose: Deposit a subscription marker entry in HREPL_Event.
--
--  Input:
--	  	 argPublicationID IN NUMBER Publication ID
--		 argArticleID	IN NUMBER	Article ID
--		 argCommandType	IN NUMBER	Command type:  35 or 69 (SQLSERVER_ARTICLE_CMD OR SQL_SUBSCRIPTION_VALIDATION)
--		 argCommand		IN VARCHAR2	Template for row count command to be executed at subscriber
--		
--  Notes:   The MarkSubscription request is generated when sp_ORAmarksubscriptionvalidation is called.
--		 The request causes a subscription marker entry to be added to the HREPL_Event table.  When the marker 
--       is added, it is given an operation code that identifier this marker as pending.  The marker is not given 
--       active status until a validation request is encountered for the same article and publication.  Pending
--       markers are ignored when HREPL_POLL is populated during the creation of a new poll interval.  This allows markers
--       to effectively be held back from inclusion in a poll interval until their corresponding validation
--       request is encountered. This assures that they appear within the same transaction when deposited into the
--       MSrepl_commands table by the log reader, which is a requirement for proper distribution agent processing.
-----------------------------------------------------------------------------------
PROCEDURE MarkSubscription
(
	argPublicationID IN NUMBER,
	argArticleID IN NUMBER,
	argCommandType IN NUMBER,
	argCommand IN VARCHAR2
)
AS
	MarkerSeq NUMBER := 0;	
	EntryTime NUMBER := round(to_number((SYSDATE - BASETIME) * SecondsInaDay));

BEGIN
	-- Pull sequence value from HREPL_SEQ
	SELECT HREPL_SEQ.nextval INTO MarkerSeq FROM dual;

	-- Make pending subscription marker entry in HREPL_Event
	BEGIN
		DELETE HREPL_Event
		WHERE Event_Publication_ID = argPublicationID
		AND   Event_Operation = PENDINGSUBSCRIPTIONMARKER
		AND   Event_Command = argCommand
		AND   Event_CmdType = argCommandType;
	
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN RAISE;
	END;
	
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, NULL, PENDINGSUBSCRIPTIONMARKER, MarkerSeq,
	NULL, argCommandType, EntryTime, NULL, NULL, NULL, argCommand, NULL, NULL, NULL, NULL, NULL,
	NULL, NULL, NULL, NULL, NULL);
	
	-- Commit transaction
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;

END MarkSubscription;
-----------------------------------------------------------------------------------
--
--  Name:    HasValidTriggers
--
--  Purpose: Check to see if triggers exist for the table
--
--  Input:
--	     argTableName	IN VARCHAR2	Table name
--       argTableOwner	IN VARCHAR2	Table owner
--       argTableID 	IN NUMBER	Table ID
--		 argLogInstance	IN NUMBER	Log instance ID
--		
--  Output:
--
--  Notes:   Returns 1 if valid triggers and article log exist, 0 if not
-----------------------------------------------------------------------------------
FUNCTION HasValidTriggers
(
	argTableName	IN VARCHAR2,
	argTableOwner	IN VARCHAR2,
	argTableID		IN NUMBER,
	argLogInstance	IN NUMBER
)
RETURN NUMBER
AS
	ArticleLog VARCHAR2(255) := REPLACE(REPLACE(ArticleLogTemplate, MatchString, TO_CHAR(argTableID)), MatchStringY, argLogInstance);
	RowTrigger VARCHAR2(255) := REPLACE(ArticleTriggerRowTemplate, MatchString, TO_CHAR(argTableID));
	StmtTrigger VARCHAR2(255) := REPLACE(ArticleTriggerStmtTemplate, MatchString, TO_CHAR(argTableID));
	NoEntry NUMBER;
	table_name VARCHAR2(255);
		
	CURSOR	tables_cur
	IS
		SELECT table_name FROM ALL_ALL_TABLES
		WHERE table_name = ArticleLog
		AND	owner = USER
		AND nested = 'NO';

	CURSOR	triggers_cur
	IS
		SELECT table_name FROM ALL_TRIGGERS
		WHERE trigger_name = RowTrigger
		AND table_name = argTableName
		AND table_owner = argTableOwner
		AND	owner = USER;

	CURSOR	triggersstmt_cur
	IS
		SELECT table_name FROM ALL_TRIGGERS
		WHERE trigger_name = StmtTrigger
		AND table_name = argTableName
		AND table_owner = argTableOwner
		AND	owner = USER;
		
	CURSOR	pubtables_cur
	IS
		SELECT Published_Table FROM HREPL_PublishedTables
		WHERE Published_TableID = argTableID
		AND	Published_Table = argTableName
		AND Published_Owner = argTableOwner
		AND Published_LogDropPending = 0
		AND Published_ArticleDropPending = 0;

BEGIN
	-- Determine whether the article log table exists
	OPEN tables_cur;
	FETCH tables_cur INTO table_name;

	IF tables_cur%NOTFOUND
	THEN
		NoEntry := 1;
	ELSE
		NoEntry := 0;
	END IF;
	
	CLOSE tables_cur;
	
	IF NoEntry = 1 THEN
		RETURN 0;
	END IF;	

	-- Determine whether the row trigger exists
	OPEN triggers_cur;
	FETCH triggers_cur INTO table_name;

	IF triggers_cur%NOTFOUND
	THEN
		NoEntry := 1;
	ELSE
		NoEntry := 0;
	END IF;
	
	CLOSE triggers_cur;
	
	IF NoEntry = 1 THEN
		RETURN 0;
	END IF;	

	-- Determine whether the stmt trigger exists
	OPEN triggersstmt_cur;
	FETCH triggersstmt_cur INTO table_name;

	IF triggersstmt_cur%NOTFOUND
	THEN
		NoEntry := 1;
	ELSE
		NoEntry := 0;
	END IF;
	
	CLOSE triggersstmt_cur;
	
	IF NoEntry = 1 THEN
		RETURN 0;
	END IF;	

	-- Determine whether an entry for the table exists in HREPL_PublishedTables
	OPEN pubtables_cur;
	FETCH pubtables_cur INTO table_name;

	IF pubtables_cur%NOTFOUND
	THEN
		NoEntry := 1;
	ELSE
		NoEntry := 0;
	END IF;
	
	CLOSE pubtables_cur;
	
	IF NoEntry = 1 THEN
		RETURN 0;
	END IF;	
	
	RETURN 1;
	
END HasValidTriggers;
-----------------------------------------------------------------------------------
--
--  Name:    ToNumFromHex
--
--  Purpose: Convert a HEX character to a number to work around deficiency
--           in Oracle 8.0.
--
--  Input:
--	     argChar	IN Char		Hex character
--		
--  Output:
--
--  Notes:   Returns HEX character converted to a number
-----------------------------------------------------------------------------------
FUNCTION ToNumFromHex
(
	argChar	IN CHAR
)
RETURN NUMBER
AS
BEGIN
	IF argChar = '0' THEN RETURN 0; END IF;
	IF argChar = '1' THEN RETURN 1; END IF;
	IF argChar = '2' THEN RETURN 2; END IF;
	IF argChar = '3' THEN RETURN 3; END IF;
	IF argChar = '4' THEN RETURN 4; END IF;
	IF argChar = '5' THEN RETURN 5; END IF;
	IF argChar = '6' THEN RETURN 6; END IF;
	IF argChar = '7' THEN RETURN 7; END IF;
	IF argChar = '8' THEN RETURN 8; END IF;
	IF argChar = '9' THEN RETURN 9; END IF;
	IF argChar = 'A' THEN RETURN 10; END IF;
	IF argChar = 'B' THEN RETURN 11; END IF;
	IF argChar = 'C' THEN RETURN 12; END IF;
	IF argChar = 'D' THEN RETURN 13; END IF;
	IF argChar = 'E' THEN RETURN 14; END IF;
	IF argChar = 'F' THEN RETURN 15; END IF;
	RETURN 0;

END ToNumFromHex;
-----------------------------------------------------------------------------------
--
--  Name:    ExecuteCommand
--  Purpose: This procedure takes a passed command, deposits the command in a
--			 DBMS_SQL.VARCHAR2S table, and calls ExecuteCommandTable to execute
--			 the command.
--	     	     
--  Input:
--  	 argSQLCommand	IN VARCHAR2
--
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE ExecuteCommand
(
	argSQLCommand	IN VARCHAR2
)
AS
	SQLCommandTable	DBMS_SQL.VARCHAR2S;	
			
BEGIN
	HREPL.AddToSQLCommand(argSQLCommand, SQLCommandTable);
	
	-- Execute the command
	HREPL.ExecuteCommandTable(SQLCommandTable);

END ExecuteCommand;
-----------------------------------------------------------------------------------
--
--  Name:    ExecuteCommandTable
--
--  Purpose: This procedure executes a command passed as a DBMS_SLQ.VARCHAR2S
--			 command table.
--           
--  Input:
--		argSQLCommandTable 	IN	DBMS_SQL.VARCHAR2S	Command table with command to execute
--	     
--  Output:
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
PROCEDURE ExecuteCommandTable
(
	argSQLCommandTable 	IN	DBMS_SQL.VARCHAR2S
)	
AS
	CursorHandle 	INTEGER;
	ReturnValue 	INTEGER;
BEGIN
	-- Open a cursor 
	CursorHandle := DBMS_SQL.OPEN_CURSOR;
 
	-- Parse the command
	BEGIN 
		DBMS_SQL.PARSE(CursorHandle, argSQLCommandTable, argSQLCommandTable.FIRST,
			argSQLCommandTable.LAST, FALSE, Language);

	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;

	-- Execute the command
	BEGIN 
		ReturnValue := DBMS_SQL.EXECUTE(CursorHandle);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;
	
	-- Close the cursor
	DBMS_SQL.CLOSE_CURSOR(CursorHandle);
	
END ExecuteCommandTable;
-----------------------------------------------------------------------------------
--
--  Name:    ExecuteCommandForPollID
--  Purpose: This procedure takes a passed command and executes it as dynamic SQL
--           using the passed Pollid as a bound variable
--	     	     
--  Input:
--  	 argSQLCommand	IN VARCHAR2
--       argPollidValue	IN NUMBER
--
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE ExecuteCommandForPollID
(
	argSQLCommand	IN VARCHAR2,
	argPollidValue	IN NUMBER
)
AS
	CursorHandle 	INTEGER;
	ReturnValue 	INTEGER;
BEGIN
	-- Open a cursor 
	CursorHandle := DBMS_SQL.OPEN_CURSOR;
 
	BEGIN
		-- Parse the command
		DBMS_SQL.PARSE(CursorHandle, argSQLCommand, DBMS_SQL.native);

		-- Bind the variable
		DBMS_SQL.BIND_VARIABLE(CursorHandle, 'Pollid', argPollidValue);
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;

	-- Execute the command
	BEGIN 
		ReturnValue := DBMS_SQL.EXECUTE(CursorHandle);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;
	
	-- Close the cursor
	DBMS_SQL.CLOSE_CURSOR(CursorHandle);

END ExecuteCommandForPollID;
-----------------------------------------------------------------------------------
--
--  Name:    ExecuteCommandForPollIDTableID
--  Purpose: This procedure takes a passed command and executes it as dynamic SQL
--           using the passed Pollid and TableID as bound variables.
--	     	     
--  Input:
--  	 argSQLCommand	IN VARCHAR2
--       argPollidValue	IN NUMBER
--       argTableidValue IN NUMBER
--
--  Output:
--  Notes:   
--
-----------------------------------------------------------------------------------
PROCEDURE ExecuteCommandForPollIDTableID
(
	argSQLCommand	IN VARCHAR2,
	argPollidValue	IN NUMBER,
	argTableidValue	IN NUMBER
)
AS
	CursorHandle 	INTEGER;
	ReturnValue 	INTEGER;
BEGIN
	-- Open a cursor 
	CursorHandle := DBMS_SQL.OPEN_CURSOR;
 
	BEGIN 
		-- Parse the command
		DBMS_SQL.PARSE(CursorHandle, argSQLCommand, DBMS_SQL.native);

		-- Bind the variables
		DBMS_SQL.BIND_VARIABLE(CursorHandle, 'Pollid', argPollidValue);
		DBMS_SQL.BIND_VARIABLE(CursorHandle, 'Tableid', argTableidValue);

	EXCEPTION
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;

	-- Execute the command
	BEGIN 
		ReturnValue := DBMS_SQL.EXECUTE(CursorHandle);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;
	
	-- Close the cursor
	DBMS_SQL.CLOSE_CURSOR(CursorHandle);

END ExecuteCommandForPollIDTableID;

-----------------------------------------------------------------------------------
--
--  Name:    ExecuteCommandTableForPollID
--
--  Purpose: This procedure executes a command passed as a DBMS_SLQ.VARCHAR2S
--			 command table binding the passed pollid parameter
--           
--  Input:
--		argSQLCommandTable 	IN	DBMS_SQL.VARCHAR2S	Command table with command to execute
--      argPollidValue		IN NUMBER
--	     
--  Output:
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
PROCEDURE ExecuteCommandTableForPollID
(
	argSQLCommandTable 	IN	DBMS_SQL.VARCHAR2S,
	argPollidValue		IN	NUMBER
)	
AS
	CursorHandle 	INTEGER;
	ReturnValue 	INTEGER;
BEGIN
	-- Open a cursor 
	CursorHandle := DBMS_SQL.OPEN_CURSOR;
 
	BEGIN 
		-- Parse the command
		DBMS_SQL.PARSE(CursorHandle, argSQLCommandTable, argSQLCommandTable.FIRST,
		argSQLCommandTable.LAST, FALSE, Language);
		
		-- Bind variable
		DBMS_SQL.BIND_VARIABLE(CursorHandle, 'Pollid', argPollidValue);
	EXCEPTION
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;
 
	-- Execute the command
	BEGIN 
		ReturnValue := DBMS_SQL.EXECUTE(CursorHandle);
	EXCEPTION
		WHEN NO_DATA_FOUND THEN NULL;	
		WHEN OTHERS THEN
			DBMS_SQL.CLOSE_CURSOR(CursorHandle);
			RAISE;
	END;
	
	-- Close the cursor
	DBMS_SQL.CLOSE_CURSOR(CursorHandle);
	
END ExecuteCommandTableForPollID;
-----------------------------------------------------------------------------------
--
--  Name:    CheckCompilationErrors
--
--  Purpose: This procedure checks USER_ERRORS for compilation errors associated with a
--           specified object and object type.  If errors are found, a message
--           is constructed with the error information and ECreateError is raised.
--  Input:
--		argObjectName 	IN	VARCHAR2	Object name
--		argObjectType 	IN	VARCHAR2	Object type
--	     
--  Output:
--
--  Notes:    	        
--
-----------------------------------------------------------------------------------
PROCEDURE CheckCompilationErrors
(
	argObjectName 	IN	VARCHAR2,
	argObjectType 	IN	VARCHAR2
)	
AS
	ErrCount	INTEGER := 0;
	ErrorMessage	VARCHAR2(4000);
	CURSOR err_cur IS
		SELECT e.line, e.text, e.type, e.name, e.position
                FROM   user_errors e
		WHERE  e.type = argObjectType
		AND    e.name = NLS_UPPER(argObjectName)
		ORDER BY sequence;
BEGIN
	SELECT COUNT(*) INTO ErrCount
	FROM   user_errors e 
	WHERE  e.type = argObjectType
	AND    e.name = NLS_UPPER(argObjectName);

	-- If the count is greater than zero, return an error
	-- with the associated information from user_errors
	IF ErrCount > 0 THEN 
		FOR err IN err_cur LOOP

			ErrorMessage := ErrorMessage ||
 		
				RTRIM(INITCAP(err.type))||' '||err.name||': Line '||TO_CHAR(err.line)||
       				' : Column '||TO_CHAR(err.position)||' : error '|| err.text;
		END LOOP;
		RAISE_APPLICATION_ERROR(ECompilationError,ErrorMessage);
	END IF;
	
END CheckCompilationErrors;
-----------------------------------------------------------------------------------
--
--  Name:    SetSqlOriginator
--
--  Purpose: Set Sql Server as the source of changes associated with this session
--
--  Input:
--	     none
--		
--  Output:
--
--  Notes:   
-----------------------------------------------------------------------------------
PROCEDURE SetSqlOriginator
AS
BEGIN
	SQLORIGINATOR := TRUE;
	
	-- Commit transaction
	COMMIT;
	
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;	

END SetSqlOriginator;
-----------------------------------------------------------------------------------
--
--  Name:    Trace
--
--  Purpose: Deposit replication trace token in HREPL_Event.
--
--  Input:
--	  	 argPublicationID   IN NUMBER    Publication ID
--		 argArticleID		IN NUMBER	 Article ID
--		 argTracerCmdType	IN NUMBER	 Tracer command type
--		 argTracerStr		IN VARCHAR2	 Tracer string
--  Output:
--
--  Notes:   The Trace request is generated when sp_ORAposttracertoken is called.  A trace
--	     entry is added to the HREPL_Event table that will later be inserted into the
--	     MSrepl_commands table. Entries from HREPL_Event are marked in HREPL_Poll just as 
--	     entries from the article log tables are marked, when a PollBegin request is made.  
--	     This allows the Trace entry to be associated with a particular polling interval.
-----------------------------------------------------------------------------------
PROCEDURE Trace
(
	argPublicationID	IN NUMBER,
	argArticleID		IN NUMBER,
	argTracerCmdType	IN NUMBER,
	argTracerStr		IN VARCHAR2
)
AS
	TraceSeq NUMBER := 0;
	EntryTime NUMBER := round(to_number((SYSDATE - BASETIME) * SecondsInaDay));

BEGIN
	-- Pull sequence value from HREPL_SEQ
	SELECT HREPL_SEQ.nextval INTO TraceSeq FROM dual;
	
	-- Make Trace entry in HREPL_Event
	INSERT INTO HREPL_Event
	VALUES(0, argPublicationID, argArticleID, NULL, TRACERTOKEN, TraceSeq,
	0, argTracerCmdType, EntryTime, NULL, NULL, NULL, argTracerStr, NULL, NULL, NULL,
	NULL, NULL, NULL, NULL, NULL, NULL, NULL);
	
	-- Commit transaction
	COMMIT;
	
EXCEPTION
	WHEN OTHERS THEN
		ROLLBACK;
		RAISE;

END Trace;
-----------------------------------------------------------------------------------
--
--  Name:    CompileTriggers
--
--  Purpose: Compile all defined HREPL triggers.
--           Used on upgrade when only package code is refreshed.
--
--  Input:
--	     none
--		
--  Output:
--
--  Notes:   
-----------------------------------------------------------------------------------
PROCEDURE CompileTriggers
AS
	SQLCommand VARCHAR2(500);

	CURSOR	trigger_cur
	IS
		SELECT trigger_name
		FROM all_triggers
		WHERE
		owner = USER AND
		UPPER(trigger_name) LIKE '%HREPL_ARTICLE%';

BEGIN
	-- Compile triggers
	FOR	trigger_rec
	IN	trigger_cur

	LOOP
		-- Compile any triggers owned by the replication administrator
		-- that are prefixed with 'HREPL_Article'
		BEGIN
			-- Compile the trigger if it exists, ignore error
			SQLCommand := 'ALTER TRIGGER ' || trigger_rec.trigger_name || ' COMPILE';
		HREPL_ExecuteCommand(SQLCommand);
		EXCEPTION
			WHEN OTHERS THEN NULL;
		END;

	END LOOP;

	COMMIT;

EXCEPTION
	WHEN OTHERS THEN NULL;

END CompileTriggers;
END HREPL;
/
SHOW ERRORS PACKAGE BODY HREPL
EXIT