Tags: code conversion, database conversion errors
Oracle allows you to specify virtual columns in the table definition. Oracle doesn’t store the data in virtual columns on the disk. On the contrary, the database derives the values in a virtual column on demand by computing a set of expressions or functions. The computed columns in SQL Server are considered as a direct analog of Oracle’s virtual columns. The problem is that the SQL Server Migration Assistant (SSMA) for Oracle converts the virtual columns as a DEFAULT expression. Thus the logic that stands behind the virtual column doesn’t converts to the target database. This will result in an error when you will try to apply the converted code to a SQL Server target database.
Source Code Example
Consider the following Oracle code example that includes a couple of virtual columns.
1 2 3 4 5 6 7 8 9 10 | CREATE TABLE TEST_ORG_ORACLE_SQL.T_VIRTUAL_COLS ( id NUMBER, first_name VARCHAR2(10), last_name VARCHAR2(10), salary NUMBER(9,2), comm1 NUMBER(3), comm2 NUMBER(3), virt_salary1 AS (ROUND(salary*(1+comm1/100),2)), virt_salary2 NUMBER GENERATED ALWAYS AS (ROUND(salary*(1+comm2/100),2)) VIRTUAL, CONSTRAINT pk_t_virtual_cols PRIMARY KEY (id)); |
Conversion Result
Let’s see what happens when you try to convert this code in SSMA. The tool will not generate an error because it converts the virtual columns as a DEFAULT expression. So, the conversion result will look as follows:
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 | CREATE TABLE [dbo].[T_VIRTUAL_COLS] ( [ID] FLOAT(53) NOT NULL, [FIRST_NAME] VARCHAR(10) NULL, [LAST_NAME] VARCHAR(10) NULL, [SALARY] NUMERIC(9, 2) NULL, [COMM1] NUMERIC(3, 0) NULL, [COMM2] NUMERIC(3, 0) NULL, [VIRT_SALARY1] FLOAT(53) NULL, [VIRT_SALARY2] FLOAT(53) NULL) WITH (DATA_COMPRESSION = NONE) GO ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD CONSTRAINT [PK_T_VIRTUAL_COLS] PRIMARY KEY CLUSTERED ([ID] ASC) GO ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD DEFAULT round(SALARY * (1 + COMM1 / 100), 2) FOR [VIRT_SALARY1] GO ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD DEFAULT round(SALARY * (1 + COMM2 / 100), 2) FOR [VIRT_SALARY2] GO |
In SQL Server a DEFAULT definition can only be a constant value, such as a character string, a scalar function (either a system, user-defined, or CLR function), or NULL value. Other columns of the table cannot participate in a DEFAULT definition. So, when you try to synchronize the converted code with SQL Server database, SSMA will generate the following synchronization error message: «Column names are not permitted».
Possible Remedies
In order to resolve this issue, you should substitute the DEFAULT definition in converted expression with the computed column definition. You can do it in the following way:
1 2 3 4 5 6 7 8 9 | CREATE TABLE [dbo].[T_VIRTUAL_COLS] ( [ID] FLOAT(53) NOT NULL, [FIRST_NAME] VARCHAR(10) NULL, [LAST_NAME] VARCHAR(10) NULL, [SALARY] NUMERIC(9, 2) NULL, [COMM1] NUMERIC(3, 0) NULL, [COMM2] NUMERIC(3, 0) NULL, [VIRT_SALARY1] AS round(SALARY * (1 + COMM1 / 100), 2), [VIRT_SALARY2] AS round(SALARY * (1 + COMM2 / 100), 2) |
Then you have to deploy this code the the SQL Server manually.
Data Migration Issues
However, a computed column cannot be the target of an INSERT or UPDATE statement. Thus, when you try to migrate the data to the table with computed columns you will get the following error message: «The column cannot be modified because it is either a computed column or is a result of a UNION operator».
In order to resolve this issue, you should remove the computed column definition and create the table without computed columns. Then you should migrate the data into this table. After that you should add the computed columns to the table using the alter table statement. So, the SQL Server code of the converted table will look as follows:
1 2 3 4 5 6 7 8 | CREATE TABLE [dbo].[T_VIRTUAL_COLS] ( [ID] FLOAT(53) NOT NULL, [FIRST_NAME] VARCHAR(10) NULL, [LAST_NAME] VARCHAR(10) NULL, [SALARY] NUMERIC(9, 2) NULL, [COMM1] NUMERIC(3, 0) NULL, [COMM2] NUMERIC(3, 0) NULL) GO |
After migrating the data use the following code to create the computed columns:
1 2 3 4 5 6 | ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD [VIRT_SALARY1] AS round([SALARY] * (1 + COMM1 / 100), 2) GO ALTER TABLE [dbo].[T_VIRTUAL_COLS] ADD [VIRT_SALARY2] AS round([SALARY] * (1 + COMM2 / 100), 2) GO |
Do you have other questions or concerns about SSMA or Oracle to SQL Server migration issues? Make sure to check out our new Jumpstart for SSMA offer!
Related posts
- How to convert foreign keys with different types of columns and referenced columns
- How to convert cursor or cursor variable as a function or procedure call parameter
- How to convert unsupported table expressions
- How to convert Unparsed SQL — Pivot Operator
- How to convert materialized view with float type
- How to convert interval expressions
- How to convert interval literals
- How to convert database links
- How to convert aggregate functions referring to remote table
- How to convert unsupported SQL clause
Be sure to check the regularly updated table of contents for our series of blog posts on typical SSMA conversion errors.
Source of technical information: Oksana Eremenko, Technical Lead at DB Best.
