-- Name:
--          sp_ORAremotequery

-- Description:
--          Execute remote Oracle query via OPENQUERY.  Can use an optional temp table.

-- Inputs:
--		@Server				== Name of linked server
--		@SelectColumnList	== Comma seperated list of columns to select from
--		@InsTable			== An optional table can be inserted into
--		@InsColumnList		== Comma separated list of columns to insert into (required if @InsTable is not null)

-- Security:
--          Internal

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

-- Owner:
--          

CREATE PROCEDURE sys.sp_ORAremotequery
(
	@Server				varchar(128),
	@SelectColumnList	nvarchar(2000) = NULL,
	@InsTable			sysname = NULL,
	@InsColumnList		nvarchar(2000) = NULL
)
AS
BEGIN
	DECLARE @retcode	int
	DECLARE @dbname		sysname

	SET NOCOUNT ON
	
	SELECT @retcode = 0
	SELECT @dbname = db_name()

	-- Verify publisher linked server login exists
	EXEC @retcode = sys.sp_IHCheckPublisherLogin @publisher = @Server OUTPUT
	IF @retcode != 0
	BEGIN
		RETURN (@retcode)
	END

	-- Validate #hquery table exists and no invalid entry
	IF object_id('tempdb..#hquery', 'U') IS NULL
	OR EXISTS
	(
		SELECT	*
		FROM	#hquery
		WHERE	seq in (0, 1)
	)
	BEGIN
        RAISERROR(8624, 16, -1)
		RETURN (1)
	END
	
	IF (@SelectColumnList is NOT NULL and @InsColumnList is NULL) or (@InsColumnList is NOT NULL and @SelectColumnList is NULL)
	BEGIN
		RAISERROR(21619, 16, -1)
		RETURN (1)
	END

	-- Prepare quotes for OPENQUERY
	UPDATE #hquery
	SET cmd = REPLACE(cmd, '''', '''''')

	-- Enable identity insert to inject wrapper queries
	SET IDENTITY_INSERT #hquery ON

	IF @InsTable is NULL
	BEGIN
		INSERT INTO #hquery (seq, cmd)
		VALUES (1, N'SELECT * FROM OPENQUERY(' + QUOTENAME(@Server) + ',''')
	END
	ELSE
	BEGIN
		IF @InsColumnList is NULL
		BEGIN
			INSERT INTO #hquery (seq, cmd)
			VALUES (1, N'INSERT INTO ' + QUOTENAME(@InsTable)
						+ ' SELECT * FROM OPENQUERY (' + QUOTENAME(@Server) + ',''')
		END
		ELSE
		BEGIN
			INSERT INTO #hquery (seq, cmd)
			VALUES (1, N'INSERT INTO '+ QUOTENAME(@InsTable)
							+ ' (' + @InsColumnList + ') SELECT ' + @SelectColumnList + ' FROM OPENQUERY('
							+ QUOTENAME(@Server) + ',''')
		END
	END

	-- Close open query
	SET IDENTITY_INSERT #hquery OFF
	INSERT INTO #hquery (cmd) VALUES (N''')')

	-- Execute query
	EXEC @retcode = sys.sp_IHexecresultset N'SELECT cmd FROM #hquery ORDER BY seq', @dbname

	RETURN @retcode
END