Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_ORAgetcolumndata

  No additional text.


Syntax


-- Name:
--          sp_ORAgetcolumndata

-- Description:
--          Queries Oracle publisher for column info for specific table

-- Inputs:
--		@owner			== owner of table
--		@tablename		== name of temp table
--		@publisher		== publisher
--		@fordiagnosis	== Defaults to 0. Set to 1 if sp_ORAgetcolumndata is being called from sp_ORAvalidatecache.
--                         When set, filters that normally constrain the set of eligible tables are not applied to
--                         the returned result set.

-- Security:
--          Internal

-- Returns:
--          Success/failure + temp table

-- Owner:
--          

CREATE PROCEDURE sys.sp_ORAgetcolumndata
(
	@owner		sysname,
	@tablename	sysname,	
	@publisher	sysname,
	@fordiagnosis bit = 0
 )
AS	
BEGIN
	SET NOCOUNT ON

	DECLARE @retcode			int
	DECLARE @InsColumnList		nvarchar(2000)
	DECLARE @SelectColumnList	nvarchar(2000)
	DECLARE @version			sysname

	-- Get Oracle version
	EXEC @retcode = sys.sp_IHgetversion	@publisher	= @publisher,
										@version	= @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)
	)

	
	-- populate a temp table with a list of table columns from the Oracle publisher
	-- this will be used by sp_IHsyncmetadata to populate IHpublishercolumns
	
	SELECT @InsColumnList    = 'name,column_ordinal,type,length,prec,scale,isnullable,iscaptured'
	SELECT @SelectColumnList = 'COLUMN_NAME,COLUMN_ID,DATA_TYPE,DATA_LENGTH,DATA_PRECISION,DATA_SCALE,NULLABLE,0'

	INSERT INTO #hquery(cmd) VALUES ('SELECT COLUMN_NAME,COLUMN_ID,')

    -- DATA_TYPE - special case for TIMESTAMP in Oracle 9+
    IF @version LIKE '8.%'
    BEGIN
        INSERT INTO #hquery(cmd) VALUES ('DATA_TYPE,')
    END
    ELSE
    BEGIN
        INSERT INTO #hquery(cmd) VALUES ('CASE WHEN DATA_TYPE LIKE ''TIMESTAMP% WITH TIME ZONE'' THEN ''TIMESTAMP WITH TIME ZONE'' WHEN DATA_TYPE LIKE ''TIMESTAMP% WITH LOCAL TIME ZONE'' THEN ''TIMESTAMP WITH LOCAL TIME ZONE'' WHEN DATA_TYPE LIKE ''TIMEST
AMP%'' THEN ''TIMESTAMP'' ELSE DATA_TYPE END AS DATA_TYPE,')
    END
	INSERT INTO #hquery(cmd) VALUES ('DECODE(CHAR_COL_DECL_LENGTH,NULL,DATA_LENGTH,CHAR_COL_DECL_LENGTH) AS DATA_LENGTH,')
	INSERT INTO #hquery(cmd) VALUES ('DATA_PRECISION,DATA_SCALE,')
	INSERT INTO #hquery(cmd) VALUES ('DECODE(NULLABLE,''Y'',1,0) AS NULLABLE ')
	INSERT INTO #hquery(cmd) VALUES ('FROM ALL_TAB_COLUMNS ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE table_name=' + QUOTENAME(@tablename, ''''))
	INSERT INTO #hquery(cmd) VALUES (' and owner=' + QUOTENAME(@owner, ''''))

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

	IF @@ERROR <> 0 or @retcode <> 0
		RETURN (1)

	
	-- populate a temp table with a list of table constraints from the Oracle publisher
	-- this will be used by sp_IHaddarticle to populate IHpublisherconstraints
	-- If not being called for validation, filter out constraints that are not 'VALID',
	-- 'ENABLED', and 'VALIDATED'.
	
	SELECT @InsColumnList    = 'constraint_name,constraint_type,table_owner,table_name,index_owner,index_name,column_name,column_position,validated,cons_status,idx_status'
	SELECT @SelectColumnList = 'CONSTRAINT_NAME,CONSTRAINT_TYPE,OWNER,TABLE_NAME,INDEX_OWNER,INDEX_NAME,COLUMN_NAME,POSITION,VALIDATED,CONSSTATUS,IDXSTATUS'
	
	TRUNCATE TABLE #hquery
	
	IF @version LIKE '8.%'
	BEGIN

		-- If an index for the table exists, having the same name as the constraint name,
		-- identify it as the index associated with the primary key constraint.

		INSERT INTO #hquery(cmd) VALUES ('SELECT CONS.CONSTRAINT_NAME,CONS.CONSTRAINT_TYPE,COLS.OWNER,COLS.TABLE_NAME,IDX.OWNER AS INDEX_OWNER,')
		INSERT INTO #hquery(cmd) VALUES ('IDX.INDEX_NAME,COLS.COLUMN_NAME,COLS.POSITION,CONS.VALIDATED,CONS.STATUS AS CONSSTATUS,IDX.STATUS AS IDXSTATUS')
		INSERT INTO #hquery(cmd) VALUES ('FROM ALL_CONS_COLUMNS COLS,ALL_CONSTRAINTS CONS, ALL_INDEXES IDX ')
		INSERT INTO #hquery(cmd) VALUES ('WHERE CONS.CONSTRAINT_NAME=COLS.CONSTRAINT_NAME ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.OWNER=COLS.OWNER ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.TABLE_NAME=CONS.TABLE_NAME ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.OWNER=IDX.TABLE_OWNER (+) ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.TABLE_NAME=IDX.TABLE_NAME (+) ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.CONSTRAINT_NAME=IDX.INDEX_NAME (+) ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.TABLE_NAME=' + QUOTENAME(@tablename, ''''))
		INSERT INTO #hquery(cmd) VALUES (' AND CONS.OWNER=' + QUOTENAME(@owner, ''''))
		INSERT INTO #hquery(cmd) VALUES (' AND CONS.CONSTRAINT_TYPE=''P''')
		INSERT INTO #hquery(cmd) VALUES (' AND COLS.OWNER=' + QUOTENAME(@owner, ''''))
		INSERT INTO #hquery(cmd) VALUES (' AND COLS.TABLE_NAME=' + QUOTENAME(@tablename, ''''))
		
		IF @fordiagnosis = 0
		BEGIN
			INSERT INTO #hquery(cmd) VALUES (' AND CONS.STATUS=''ENABLED'' ')
			INSERT INTO #hquery(cmd) VALUES (' AND CONS.VALIDATED=''VALIDATED'' ')
		END
	END
	ELSE
	BEGIN
		INSERT INTO #hquery(cmd) VALUES ('SELECT CONS.CONSTRAINT_NAME,CONS.CONSTRAINT_TYPE,COLS.OWNER,COLS.TABLE_NAME,DECODE(CONS.INDEX_OWNER,NULL,COLS.OWNER,CONS.INDEX_OWNER) AS INDEX_OWNER,')
		INSERT INTO #hquery(cmd) VALUES ('CONS.INDEX_NAME,COLS.COLUMN_NAME,COLS.POSITION,CONS.VALIDATED,CONS.STATUS AS CONSSTATUS,IDX.STATUS AS IDXSTATUS ')
		INSERT INTO #hquery(cmd) VALUES ('FROM ALL_CONS_COLUMNS COLS,ALL_CONSTRAINTS CONS, ALL_INDEXES IDX ')
		INSERT INTO #hquery(cmd) VALUES ('WHERE COLS.CONSTRAINT_NAME=CONS.CONSTRAINT_NAME ')
		INSERT INTO #hquery(cmd) VALUES ('AND CASE WHEN CONS.INDEX_OWNER IS NOT NULL THEN CONS.INDEX_OWNER ELSE CONS.OWNER END = IDX.OWNER ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.INDEX_NAME=IDX.INDEX_NAME ')
		INSERT INTO #hquery(cmd) VALUES ('AND CONS.TABLE_NAME=' + QUOTENAME(@tablename, ''''))
		INSERT INTO #hquery(cmd) VALUES (' AND CONS.OWNER=' + QUOTENAME(@owner, ''''))
		INSERT INTO #hquery(cmd) VALUES (' AND CONS.CONSTRAINT_TYPE=''P''')
        INSERT INTO #hquery(cmd) VALUES (' AND COLS.OWNER=' + QUOTENAME(@owner, ''''))
        INSERT INTO #hquery(cmd) VALUES (' AND COLS.TABLE_NAME=' + QUOTENAME(@tablename, ''''))
		
		IF @fordiagnosis = 0
		BEGIN
			INSERT INTO #hquery(cmd) VALUES (' AND CONS.STATUS=''ENABLED'' ')
			INSERT INTO #hquery(cmd) VALUES (' AND IDX.STATUS=''VALID'' ')
			INSERT INTO #hquery(cmd) VALUES (' AND CONS.VALIDATED=''VALIDATED'' ')
		END		
	END

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

	IF @@ERROR <> 0 or @retcode <> 0
		RETURN (1)
	
	
	-- populate a temp table with a list of table indexes from the Oracle publisher
	-- this will be used by sp_IHaddarticle to populate IHpublisherindexes
	--		
	SELECT @InsColumnList    = 'index_name,index_owner,uniqueness,column_name,column_position,index_status'
	SELECT @SelectColumnList = 'INDEX_NAME,OWNER,UNIQUENESS,COLUMN_NAME,COLUMN_POSITION,IDXSTATUS'
	
	TRUNCATE TABLE #hquery
	INSERT INTO #hquery(cmd) VALUES ('SELECT IDX.INDEX_NAME,IDX.OWNER,IDX.UNIQUENESS,IDXCOL.COLUMN_NAME,IDXCOL.COLUMN_POSITION,IDX.STATUS AS IDXSTATUS')
	INSERT INTO #hquery(cmd) VALUES ('FROM ALL_INDEXES IDX,ALL_IND_COLUMNS IDXCOL ')
	INSERT INTO #hquery(cmd) VALUES ('WHERE IDX.INDEX_NAME=IDXCOL.INDEX_NAME AND IDX.OWNER=IDXCOL.INDEX_OWNER ')
	INSERT INTO #hquery(cmd) VALUES ('AND ((IDX.INDEX_TYPE=''NORMAL'') OR (IDX.INDEX_TYPE=''IOT - TOP'')) ')
	INSERT INTO #hquery(cmd) VALUES ('AND IDX.TABLE_NAME=' + QUOTENAME(@tablename, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES ('AND IDX.TABLE_OWNER=' + QUOTENAME(@owner, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES ('AND IDXCOL.TABLE_NAME=' + QUOTENAME(@tablename, '''') + ' ')
	INSERT INTO #hquery(cmd) VALUES ('AND IDXCOL.TABLE_OWNER=' + QUOTENAME(@owner, '''') + ' ')
	
	IF @fordiagnosis = 0
	BEGIN
		INSERT INTO #hquery(cmd) VALUES ('AND IDX.STATUS=''VALID'' ')
	END		

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

	IF @@ERROR <> 0 or @retcode <> 0
		RETURN (1)

	-- Filter PK constraints from list of indexes
	DELETE	#indexes
	FROM	#indexes idx INNER JOIN #constraints con
	  ON	idx.index_owner 	= con.index_owner
	  AND	idx.index_name		= con.index_name
	WHERE	con.constraint_type	= 'P'

	-- Filter indexes and constraints that don't reference published columns
	DELETE	#indexes
	WHERE	index_name IN
			(
				SELECT DISTINCT idx.index_name
				FROM #indexes idx
				WHERE idx.column_name NOT IN
				(
					SELECT	pc.name
					FROM	#publishercolumns pc
				)
			)

	DELETE	#constraints
	WHERE	constraint_name IN
			(
				SELECT DISTINCT con.constraint_name
				FROM #constraints con
				WHERE con.column_name NOT IN
				(
					SELECT	pc.name
					FROM	#publishercolumns pc
				)
			)

	RETURN(0)
END	

 
Last revision 2008RTM
See also

  sp_IHaddarticle (Procedure)
sp_IHsyncmetadata (Procedure)
sp_MSdrop_repltran (Procedure)
sp_MSrepl_get_Oracle_publisher_metadata (Procedure)
sp_ORAvalidatecache (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