Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORACheckSourceTable

  No additional text.


Syntax


-- Name:
--		sp_ORACheckSourceTable

-- Description:
--		Validate source table for publishing (Oracle specific)

-- Inputs:
--		@publisher_type		== publisher type
--		@publisher			== publisher name
--		@source_table		== source table
--		@source_owner		== source table owner
--		@publication_type	== publication type (tran == 0; snapshot == 1)

-- Returns:
--		Return code (0 for success, 1 for failure)

-- Security:
--		Internal
-- Requires Certificate signature for catalog access

-- Notes:
--		Used by sp_IHCheckSourceTable for Oracle publishers


CREATE PROCEDURE sys.sp_ORACheckSourceTable
(
	@publisher			sysname,
	@source_table		sysname,
	@source_owner		sysname,
	@publisher_type		sysname,
	@publication_type	int
)
AS
BEGIN
	DECLARE @retcode			int
    DECLARE @publisher_dbms		sysname
    DECLARE @publisher_version	sysname
	DECLARE	@InsColumnList		nvarchar(2000)
	DECLARE	@SelectColumnList	nvarchar(2000)
	DECLARE @pubid				int

	SET NOCOUNT ON
	
	-- Get publisher ID
	SELECT	@pubid = srvid
	FROM	master.dbo.sysservers
	WHERE	UPPER(srvname collate database_default) = UPPER(@publisher)
	
	IF @pubid IS NULL
	BEGIN
		RAISERROR(21600, 16, -1, @publisher)
		RETURN (1)
	END

	-- Set publisher DBMS and version
	SELECT	@publisher_dbms	= 'ORACLE'

	EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
										@version	= @publisher_version OUTPUT

	IF @retcode != 0 OR @@ERROR != 0
	BEGIN
		RETURN (1)
	END

	-- Define sp_ORAremotequery support table
	create table #hquery
	(
		seq	int identity(2,1),
		cmd	nvarchar(4000)
	)

	CREATE TABLE #oraprops
	(
		property		char(10),
		value			int
	)
	
	-- Master table list
	SELECT @InsColumnList    = 'property, value'
	SELECT @SelectColumnList = '*'

	INSERT INTO #hquery(cmd) VALUES('SELECT ''TABLE'', DECODE(TEMPORARY,''N'',1,0) ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_ALL_TABLES T ')
	INSERT INTO #hquery(cmd) VALUES('WHERE T.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND T.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND T.NESTED = ''NO'' ')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
		INSERT INTO #hquery(cmd) VALUES('AND T.DROPPED = ''NO'' ')

	IF @publisher_version LIKE N'8.%'
	BEGIN
		-- Primary key index is no longer being checked for Oracle 8.
		-- The Oracle 8 version of all_constraints does not explicitly identify
		-- the name of the primary key index, and since the constraint_name and
		-- index name need not be the same, this check has been dropped.
		-- This is fairly safe, since the index cannot be dropped while it is
		-- being used to enforce a primary key constraint.
		INSERT INTO #hquery(cmd) VALUES('UNION ')
		INSERT INTO #hquery(cmd) VALUES('SELECT ''PKIDX'', COUNT(*) ')
		INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC ')
		INSERT INTO #hquery(cmd) VALUES('WHERE AC.CONSTRAINT_TYPE = ''P'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')
		INSERT INTO #hquery(cmd) VALUES('UNION ')

	END
	ELSE
	BEGIN
		-- Primary key with valid index
		INSERT INTO #hquery(cmd) VALUES('UNION ')
		INSERT INTO #hquery(cmd) VALUES('SELECT ''PKIDX'', COUNT(*) ')
		INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC, ALL_INDEXES IDX')

		IF @publisher_version NOT LIKE N'9.%'
		    INSERT INTO #hquery(cmd) VALUES(', ALL_ALL_TABLES T ')

		INSERT INTO #hquery(cmd) VALUES('WHERE CASE WHEN AC.INDEX_OWNER IS NOT NULL THEN AC.INDEX_OWNER ELSE AC.OWNER END = IDX.OWNER ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.INDEX_NAME = IDX.INDEX_NAME ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_TYPE = ''P'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
		INSERT INTO #hquery(cmd) VALUES('AND IDX.STATUS = ''VALID'' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
		INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')
		
		IF @publisher_version NOT LIKE N'9.%'
		BEGIN
		    INSERT INTO #hquery(cmd) VALUES(' AND CASE WHEN AC.INDEX_OWNER IS NOT NULL THEN AC.INDEX_OWNER ELSE AC.OWNER END = T.OWNER ')
		    INSERT INTO #hquery(cmd) VALUES(' AND AC.TABLE_NAME = T.TABLE_NAME ')
		    INSERT INTO #hquery(cmd) VALUES(' AND T.DROPPED = ''NO'' ')
		END

		INSERT INTO #hquery(cmd) VALUES('UNION ')
	END
	-- Primary key columns
	INSERT INTO #hquery(cmd) VALUES('SELECT ''PKCOL'', COUNT(*) ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC, ALL_TAB_COLUMNS TC, ALL_CONS_COLUMNS CC')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
		INSERT INTO #hquery(cmd) VALUES(', ALL_ALL_TABLES T ')

	INSERT INTO #hquery(cmd) VALUES(' WHERE AC.OWNER = TC.OWNER ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = TC.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = CC.OWNER ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = CC.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND CC.COLUMN_NAME = TC.COLUMN_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_TYPE = ''P'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
	BEGIN
		INSERT INTO #hquery(cmd) VALUES(' AND AC.OWNER = T.OWNER ')
		INSERT INTO #hquery(cmd) VALUES(' AND AC.TABLE_NAME = T.TABLE_NAME ')
		INSERT INTO #hquery(cmd) VALUES(' AND T.DROPPED = ''NO'' ')
	END

	INSERT INTO #hquery(cmd) VALUES('UNION ')
	-- Primary key length
	INSERT INTO #hquery(cmd) VALUES('SELECT ''PKLEN'', SUM(TC.DATA_LENGTH) ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_CONSTRAINTS AC, ALL_TAB_COLUMNS TC, ALL_CONS_COLUMNS CC')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
		INSERT INTO #hquery(cmd) VALUES(', ALL_ALL_TABLES T ')

	INSERT INTO #hquery(cmd) VALUES(' WHERE AC.OWNER = TC.OWNER ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = TC.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = CC.OWNER ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = CC.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND CC.COLUMN_NAME	= TC.COLUMN_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_NAME = CC.CONSTRAINT_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.CONSTRAINT_TYPE = ''P'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.VALIDATED = ''VALIDATED'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.STATUS = ''ENABLED'' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND AC.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
	BEGIN
		INSERT INTO #hquery(cmd) VALUES(' AND AC.OWNER = T.OWNER ')
		INSERT INTO #hquery(cmd) VALUES(' AND AC.TABLE_NAME = T.TABLE_NAME ')
		INSERT INTO #hquery(cmd) VALUES(' AND T.DROPPED = ''NO'' ')
	END

	INSERT INTO #hquery(cmd) VALUES('UNION ')
	-- Select privileges
	INSERT INTO #hquery(cmd) VALUES('SELECT ''PRIVSEL'', 1 ')
	INSERT INTO #hquery(cmd) VALUES('FROM ALL_ALL_TABLES T ')
	INSERT INTO #hquery(cmd) VALUES('WHERE (USER IN (SELECT USERNAME FROM USER_SYS_PRIVS WHERE PRIVILEGE = ''SELECT ANY TABLE'') ')
	INSERT INTO #hquery(cmd) VALUES('OR (T.TABLE_NAME IN (')
	INSERT INTO #hquery(cmd) VALUES('SELECT ATP.TABLE_NAME FROM ALL_TAB_PRIVS ATP ')
	INSERT INTO #hquery(cmd) VALUES('WHERE ATP.TABLE_SCHEMA = T.OWNER ')
	INSERT INTO #hquery(cmd) VALUES('AND ATP.TABLE_NAME = T.TABLE_NAME ')
	INSERT INTO #hquery(cmd) VALUES('AND ATP.PRIVILEGE = ''SELECT'' ')
	INSERT INTO #hquery(cmd) VALUES('AND (ATP.GRANTEE = USER AND GRANTOR IN (SELECT USERNAME FROM ALL_USERS) ')
	INSERT INTO #hquery(cmd) VALUES('OR GRANTEE = ''PUBLIC'')))) ')
	INSERT INTO #hquery(cmd) VALUES('AND T.TABLE_NAME = ' + QUOTENAME(@source_table, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND T.OWNER = ' + QUOTENAME(@source_owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES('AND T.NESTED = ''NO'' ')

	IF @publisher_version NOT LIKE N'8.%' AND @publisher_version NOT LIKE N'9.%'
		INSERT INTO #hquery(cmd) VALUES(' AND T.DROPPED = ''NO'' ')

	EXEC @retcode = sp_ORAremotequery
					@Server				= @publisher,
					@SelectColumnList	= @SelectColumnList,
					@InsTable			= '#oraprops',
					@InsColumnList		= @InsColumnList

	IF @@ERROR <> 0 OR @retcode <> 0
	BEGIN
		DROP TABLE #oraprops
		DROP TABLE #hquery
		
		RAISERROR (21781, 16, -1)
		RETURN (@retcode)
	END

	-- Verify source table is at publisher
	IF NOT EXISTS
	(
		SELECT	*
		FROM	#oraprops
		WHERE	property = 'TABLE'
	)
	BEGIN
		RAISERROR (21662, 16, -1, @source_owner, @source_table)
		
		DROP TABLE #oraprops
		DROP TABLE #hquery
				
		RETURN (1)
	END
	
	-- Verify source table is not a temp table
	IF EXISTS
	(
		SELECT	*
		FROM	#oraprops
		WHERE	property = 'TABLE'
		  AND	value	 = 0
	)
	BEGIN
		RAISERROR (21735, 16, -1, @source_owner, @source_table)
		
		DROP TABLE #oraprops
		DROP TABLE #hquery
				
		RETURN (1)
	END

	-- Verify tran specific requirements
	IF @publication_type = 0
	BEGIN
		DECLARE @pkCount 	int
		DECLARE @pkLen   	int
		DECLARE @pkIndex	bit
		DECLARE	@privSel	bit
		
		-- Get PK info
		SELECT	@pkCount = value
		FROM	#oraprops
		WHERE	property = 'PKCOL'
		
		SELECT	@pkLen = value
		FROM	#oraprops
		WHERE	property = 'PKLEN'
		
		SELECT	@pkIndex = CONVERT(bit, value)
		FROM	#oraprops
		WHERE	property = 'PKIDX'
		
		SELECT	@privSel = CONVERT(bit, value)
		FROM	#oraprops
		WHERE	property = 'PRIVSEL'
		
		-- Verify if PK is valid

        -- Primary key columns
        IF (@pkCount > 16)
        BEGIN
            RAISERROR (21834, 16, -1, @source_owner, @source_table, @pkCount, 16)
            SET @retcode = 1
        END

	-- Primary key length
        IF (@pkLen > 900)
        BEGIN
            RAISERROR (21835, 16, -1, @source_owner, @source_table, @pkLen, 900)
            SET @retcode = 1
        END

	-- Primary key existance test
        IF ((@pkCount < 0) OR (@pkIndex = 0))
		BEGIN
			RAISERROR (21663, 16, -1, @source_owner, @source_table)
			SET @retcode = 1
		END

		-- Verify select privileges
		IF @privSel = 0
		BEGIN
			RAISERROR (21682, 16, -1, @source_owner, @source_table)
			SET @retcode = 1
		END
	END

	DROP TABLE #oraprops
	DROP TABLE #hquery
	
	RETURN (@retcode)
END

 
Last revision 2008RTM
See also

  sp_IHCheckSourceTable (Procedure)
sp_MSdrop_repltran (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash