I am migrating sp from Oracle to SQL Server through SSMA but I get below error and unable to create SP.
Error-Msg 2715, Level 16, State 3, Procedure UPDATER, Line 16 [Batch Start Line 0] Column, parameter, or variable #5: Cannot find data type ssma_oracle.CollectionIndexInt. Parameter or variable '@L_TAB' has an invalid data type.
CREATE PROCEDURE UPDATER
@PV_CUSTOMER_ID varchar(max),
@PV_LIST_RULEIDS varchar(max),
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@PN_ACTIVE float(53),
@PV_USERID varchar(max)
AS
BEGIN
DECLARE
@L_TAB ssma_oracle.CollectionIndexInt,
/*
* SSMA warning messages:
* O2SS0356: Conversion from NUMBER datatype can cause data loss.
*/
@LN_ACTIVE float(53),
@ER_LINE varchar(max)
/*
* SSMA error messages:
* O2SS0050: Conversion of identifier 'regexp_substr(VARCHAR2, CHAR, BINARY_INTEGER, NUMBER)' is not supported.
DECLARE
L_CUR CURSOR LOCAL FOR
WITH
h$cte AS
(
SELECT 1 AS LEVEL, CAST(row_number() OVER(
ORDER BY @@spid) AS varchar(max)) AS path
UNION ALL
/*
* SSMA error messages:
* O2SS0050: Conversion of identifier 'REGEXP_SUBSTR(VARCHAR2, CHAR, BINARY_INTEGER, NUMBER)' is not supported.
SELECT h$cte.LEVEL 1 AS LEVEL, path ',' CAST(row_number() OVER(
ORDER BY @@spid) AS varchar(max)) AS path
FROM h$cte
WHERE REGEXP_SUBSTR(upper(@PV_LIST_RULEIDS), '([^,] )', 1, (LEVEL 1)) IS NOT NULL
*/
)
SELECT REGEXP_SUBSTR(@PV_LIST_RULEIDS, '([^,] )', 1, h$cte.LEVEL) AS RULE_ID
FROM h$cte
ORDER BY h$cte.path
*/
OPEN L_CUR
WHILE 1 = 1
BEGIN
DECLARE
@CollectionIndexInt$TYPE varchar(max) = ' TABLE OF ( RECORD ( RULE_ID STRING ) )',
@collection_cur_key int
SET @collection_cur_key = 0
DECLARE
@collection_QTY_rows int
SET @collection_QTY_rows = 1000
IF (@L_TAB IS NULL)
SET @L_TAB = ssma_oracle.CollectionIndexInt ::[Null].SetType(@CollectionIndexInt$TYPE)
SET @L_TAB = @L_TAB.RemoveAll()
WHILE @collection_QTY_rows > 0
BEGIN
SET @collection_cur_key = @collection_cur_key 1
DECLARE
@rule_id varchar(max)
FETCH L_CUR
INTO @rule_id
IF @@FETCH_STATUS = -1
BREAK
SET @L_TAB = @L_TAB.ExtendAndSetRecord(@collection_cur_key, @L_TAB.GetOrCreateRecord(@collection_cur_key).SetString(N'RULE_ID', @rule_id))
SET @collection_QTY_rows = @collection_QTY_rows - 1
END
IF @L_TAB.Count <= 0
BREAK
DECLARE
@I int
SET @I = @L_TAB.First()
DECLARE
@loop$bound int
SET @loop$bound = @L_TAB.Last()
WHILE @I <= @loop$bound
BEGIN
BEGIN
BEGIN TRY
SELECT @LN_ACTIVE = AD_CUST_RULE_MASTER.ACTIVE
FROM CS_ANTIFRAUD.AD_CUST_RULE_MASTER
WHERE AD_CUST_RULE_MASTER.CUSTOMER_ID = @PV_CUSTOMER_ID AND AD_CUST_RULE_MASTER.RULE_ID = @L_TAB.GetRecord(@I).GetString(N'RULE_ID')
IF @LN_ACTIVE <> @PN_ACTIVE
BEGIN
INSERT CS_ANTIFRAUD.AD_CUST_RULE_MASTER_AU(
CUSTOMER_ID,
RULE_ID,
ACTIVE,
LAST_UPDATED_DATE,
LAST_UPDATED_BY,
LOG_USER_ID,
LOG_DATE,
REMARKS)
SELECT
AD_CUST_RULE_MASTER.CUSTOMER_ID,
AD_CUST_RULE_MASTER.RULE_ID,
AD_CUST_RULE_MASTER.ACTIVE,
AD_CUST_RULE_MASTER.LAST_UPDATED_DATE,
AD_CUST_RULE_MASTER.LAST_UPDATED_BY,
@PV_USERID,
sysdatetime(),
'Updated ' ISNULL(CAST(AD_CUST_RULE_MASTER.ACTIVE AS nvarchar(max)), '') ' to ' ISNULL(CAST(@PN_ACTIVE AS nvarchar(max)), '') '.'
FROM CS_ANTIFRAUD.AD_CUST_RULE_MASTER
WHERE AD_CUST_RULE_MASTER.CUSTOMER_ID = @PV_CUSTOMER_ID AND AD_CUST_RULE_MASTER.RULE_ID = @L_TAB.GetRecord(@I).GetString(N'RULE_ID')
UPDATE CS_ANTIFRAUD.AD_CUST_RULE_MASTER
SET
ACTIVE = @PN_ACTIVE,
LAST_UPDATED_BY = @PV_USERID,
LAST_UPDATED_DATE = sysdatetime()
WHERE AD_CUST_RULE_MASTER.CUSTOMER_ID = @PV_CUSTOMER_ID AND AD_CUST_RULE_MASTER.RULE_ID = @L_TAB.GetRecord(@I).GetString(N'RULE_ID')
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END
END TRY
BEGIN CATCH
DECLARE
@errornumber int
SET @errornumber = ERROR_NUMBER()
DECLARE
@errormessage nvarchar(4000)
SET @errormessage = ERROR_MESSAGE()
DECLARE
@exceptionidentifier nvarchar(4000)
SELECT @exceptionidentifier = ssma_oracle.db_error_get_oracle_exception_id(@errormessage, @errornumber)
IF (@exceptionidentifier LIKE N'ORA-00100%')
BEGIN
INSERT CS_ANTIFRAUD.AD_CUST_RULE_MASTER(CUSTOMER_ID, RULE_ID, ACTIVE)
VALUES (@PV_CUSTOMER_ID, @L_TAB.GetRecord(@I).GetString(N'RULE_ID'), @PN_ACTIVE)
IF @@TRANCOUNT > 0
COMMIT TRANSACTION
END
ELSE
BEGIN
/*
* SSMA error messages:
* O2SS0560: Identifier DBMS_UTILITY.FORMAT_ERROR_BACKTRACE cannot be converted because it was not resolved.
* This may happen because system package that defines the identifier was excluded from loading in Project Settings.
SET @ER_LINE = DBMS_UTILITY.FORMAT_ERROR_BACKTRACE
*/
DECLARE
@temp varchar(8000)
SET @temp = 'ERROR IN PROC :' ISNULL(ssma_oracle.db_error_sqlerrm_0(@exceptionidentifier, @errornumber), '') ISNULL(@ER_LINE, '')
EXECUTE CS_ANTIFRAUD.PK_UTILITIES$PR_LOG_FILE_MANAGE @P_PKG_NAME = 'PR_AD_CUST_DECISION_UPDATER', @P_PR_NAME = 'PR_AD_CUST_DECISION_UPDATER', @P_STATUS = 'ERROR', @P_COMMENTS = @temp
END
END CATCH
END
SET @I = @I 1
END
END
END
GO
CodePudding user response：
You will most likely be missing SSMA4OracleSQLServerCollections.NET.dll on the server,
see Installing SSMA for Oracle (OracleToSQL)
Or more specifically for the server installation: Installing SSMA components on SQL Server (OracleToSQL)
- You need to run the MSI from the server, this element cannot be completed from a client workstation:
SSMAforOracleExtensionPack_n.msi(where n is the build number)
After installing the server component on the server, make sure you have enabled the CLR runtimes:
sp_configure 'clr enabled', 1
GO
RECONFIGURE
GO
After you install the extension pack, an ssma_oracle.bcp_migration_packages table appears in the sysdb database.
Every time you migrate data to SQL Server, SSMA creates a SQL Server Agent job. These jobs are named ssma_oracle data migration package {GUID}, and are visible in the SQL Server Agent node of SQL Server Management Studio in the Jobs folder.
Also following extended stored procedures will be added to the master database:
xp_ora2ms_exec2
xp_ora2ms_exec2_ex
xp_ora2ms_versioninfo2
CodePudding user response：
Thanks for your help I have copied .dll file in SQL server and after that
Enabling advanced options resolved my problem:
EXEC sp_configure 'show advanced options', 1; RECONFIGURE;
EXEC sp_configure 'clr strict security', 0; RECONFIGURE;
after that I have create assemblies and create SP issue resolved thanks