-- Name:
--		sp_ORAloadscript

-- Description:
--		Executes script on Oracle server

-- Inputs:
--		@publisher		== publisher name (TNS name)
--		@script			== script file
--		@security_mode	== 0 - Oracle security; 1 - NT integrated security
--		@login			== Oracle login to server as schema owner
--		@password		== Oracle password

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

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

-- Notes:
--		Generates SQL*Plus command line to execute script using auth info

create procedure sys.sp_ORAloadscript
(
    @publisher		sysname,
    @script			sysname,
    @security_mode	bit = 1,
    @login			sysname = NULL,
    @password		sysname = NULL
)
AS
BEGIN
    SET NOCOUNT ON
	
	DECLARE @retcode			int
    DECLARE @install_path		nvarchar(255)
    DECLARE @mssql_data_path		nvarchar(255)
    DECLARE @script_cmd			nvarchar(4000)

    -- Security Check: require sysadmin
    IF (ISNULL(IS_SRVROLEMEMBER('sysadmin'),0) = 0)
    BEGIN
        RAISERROR(21089,16,-1)
        RETURN (1)
    END

	-- Check to ensure a login is provided if security mode is SQL Server authentication.
	SELECT @login = RTRIM(LTRIM(ISNULL(@login, '')))
	IF @security_mode = 0 and @login = ''
	BEGIN
		-- @login cannot be null or empty when @security_mode is set to 0 (SQL Server authentication).'
		RAISERROR(21694, 16, -1, '@login', '@security_mode')
		RETURN (1)
	END

    IF @password = N''
    BEGIN
		SELECT @password = NULL
	END

    -- Get path to version specific INSTALL directory
    exec @retcode = sys.sp_MSget_setup_paths @sql_path = @install_path OUTPUT, @data_path = @mssql_data_path OUTPUT
    IF @retcode != 0 OR @install_path IS NULL OR @install_path = '' OR @mssql_data_path IS NULL OR @mssql_data_path = ''
    BEGIN
        RETURN (1)
    END

	-- Use SQL*Plus for loading scripts
	SELECT @script_cmd = '" "sqlplus.exe" "\"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@login) COLLATE DATABASE_DEFAULT + '\"/\"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@password) COLLATE DATABASE_DEFAULT + '\"@'
						+ sys.fn_escapecmdshellsymbols(@publisher) COLLATE DATABASE_DEFAULT + '" @"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@install_path) COLLATE DATABASE_DEFAULT + '\install\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT +'.sql" > "'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@mssql_data_path) COLLATE DATABASE_DEFAULT + '\log\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT + '.out"'

	EXEC @retcode = master.dbo.xp_cmdshell @script_cmd, NO_OUTPUT
	
	IF @retcode != 0 OR @@ERROR != 0
	BEGIN

		SELECT @script_cmd = '" "sqlplus.exe [ORALOGIN]/[ORAPASSWORD]@"'
						+ sys.fn_escapecmdshellsymbols(@publisher) COLLATE DATABASE_DEFAULT + '" @"'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@install_path) COLLATE DATABASE_DEFAULT + '\install\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT +'.sql" > "'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@mssql_data_path) COLLATE DATABASE_DEFAULT + '\log\'
						+ sys.fn_escapecmdshellsymbolsremovequotes(@script) COLLATE DATABASE_DEFAULT + '.out"'

		RAISERROR (25017, 16, -1, @script_cmd)
		RETURN (1)
	END

    RETURN(0)
END