-- Name:
--		sp_MSrepl_validalateache

-- Description:
--		Validate the distributor side Oracle meta data (Oracle specific)

-- Inputs:
--		@publisher		== name of Oracle publisher
--		@publication	== publication name
--		@article		== article name

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


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


CREATE PROCEDURE sys.sp_MSrepl_validatecache
(
	@publisher		sysname,
	@publication	sysname,
	@article		sysname
)
AS
BEGIN
	set nocount on

	declare @publication_id	int
	declare @pubname		sysname
	declare @artname		sysname
	declare @tableowner		sysname
	declare @tablename		sysname
	declare @retcode		int
	declare @fetch_status	int
	
	-- Verify @publication is valid, if specified
	if @publication <> '%'
	begin
		if not exists
			(select	p.name
			from	syspublications p,
					MSpublications b,
					master.dbo.sysservers s
			where	p.pubid = b.publication_id
			and	s.srvid = b.publisher_id
			and	UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
			and	p.name = @publication)
		begin
			raiserror(21073, 16, -1)
			return 1
		end
	end	
	
	-- Verify @article is valid, if specified
	if @article <> '%'
	begin
		if not exists
			(select	a.name
			from	sysarticles a,
					MSarticles m,
					syspublications p,
					MSpublications b,
					master.dbo.sysservers s
			where	a.pubid = p.pubid
			and	a.artid = m.article_id
			and	a.pubid = m.publication_id
			and	s.srvid = m.publisher_id
			and	p.pubid = b.publication_id
			and	s.srvid = b.publisher_id
			and	UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
			and	a.name = @article
			and	((@publication = N'%') or (p.name = @publication)))
		begin
			raiserror(20046, 16, -1)
			return 1
		end
	end

    CREATE TABLE #RESULTSET (publication sysname, article sysname, tableowner sysname, tablename sysname, columnname nvarchar(30), index_or_constraint nvarchar(30), columnordinal int, type nvarchar(30), length int, prec int, scale int, isnullable bit, val
idate nvarchar(13), consstatus nvarchar(8), idxstatus nvarchar(8), description NVARCHAR(100))

	declare hC CURSOR LOCAL FAST_FORWARD for
	select	p.name, a.name, m.source_owner, m.source_object
	from	sysarticles a,
			MSarticles m,
			syspublications p,
			MSpublications b,
			master.dbo.sysservers s
	where	a.pubid = p.pubid
	  and	a.artid = m.article_id
	  and	a.pubid = m.publication_id
	  and	s.srvid = m.publisher_id
	  and	p.pubid = b.publication_id
	  and	s.srvid = b.publisher_id
	  and	UPPER(s.srvname collate database_default) = UPPER(@publisher) collate database_default
	  and	((@article = N'%') or (a.name = @article))
	  and	((@publication = N'%') or (p.name = @publication))
	order by p.name, a.name
	
	open hC
	fetch hC into @pubname, @artname, @tableowner, @tablename
	
    while @@fetch_status <> -1
	begin
	
		-- sp_ORAvalidatecache will populate RESULTSET
		exec @retcode = sys.sp_ORAvalidatecache	@publisher,
												@pubname,
												@artname,
												@tableowner,
												@tablename

    	if @retcode <> 0 or @@error <> 0
    	begin
			close hC
			deallocate hC
			return 1
    	end
    	
   		fetch hC into @pubname, @artname, @tableowner, @tablename

	end

	close hC
	deallocate hC

	-- Return result set
	select * from #RESULTSET order by publication, article, tableowner, tablename, columnname, description

	return 0
end