-- 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