Oracle 11g introduced pivot operation that allows writing cross tabulation (also called transposed, crosstab and matrix) queries that rotate rows into columns and aggregate results. Pivot rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output and performs aggregations where they are required on any remaining column values that you want to get in the final output. SQL Server Migration Assistant (SSMA) for Oracle cannot correctly parse the pivot […]
Learn more about
Database conversion errors
Database conversion is an important step in database migration projects. However, various conversion automation tools do not convert 100% of the database code. Learn how you can deal with the most common database conversion errors from DB Best’s blog.
Oracle allows you to create foreign key for table using columns with different data types. But SQL Server Migration Assistant (SSMA) for Oracle cannot convert them to SQL Server correctly because SQL Server doesn’t support the foreign keys that use columns with different data types. So, when you try to convert the original code that includes the foreign key with the columns of different data types, SSMA will generate the following error message: «Error O2SS0231: Foreign keys with different types […]
Oracle 9i introduced the predefined SYS_REFCURSOR type meaning we no longer have to define our own REF CURSOR types. SQL Server doesn’t support SYS_REFCURSOR type and doesn’t have a direct equivalent for it. Thus, SQL Server Migration Assistant (SSMA) for Oracle converts SYS_REFCURSOR variables into varchar (8000) and generates the appropriate warning message: «Warning O2SS0259: Cursor type was converted to varchar». Oracle’s SYS_REFCURSOR type allows returning recordsets from stored procedures and functions. For example, a procedure can open a cursor […]
Oracle’s table functions allow you to define a set of PL/SQL statements that will, when queried, behave just as a regular query to the table. You may use the table function to manipulate the individual elements of a collection (user-defined object types) in your SQL code. The problem is that SQL Server Migration Assistant (SSMA) for Oracle can not convert table function and generates a set of errors: O2SS0474: Conversion of user defined type variable is not supported and is […]
Welcome to our blog post series covering the typical conversion errors of SQL Server Migration Assistant (SSMA). DB Best has a vast experience in database migration, so, we constantly encounter these errors and found the solutions for most of them. We will devote one blog post per common error message that may occur during database migration with SSMA. Each blog post contains a problem statement and our recommendation on how to fix it correctly. Related posts Below you can find the table of contents […]
Oracle’s NumToDSInterval function converts the provided number value of specified time units or expression to an interval day to second literal. Usually, we use the interval values when dealing with dates. PostgreSQL provides some support for intervals but does not support storing values of such kind in variables. Hence, there are no functions in PostgreSQL able to return interval values.We commonly use the functions working with date intervals in reporting and other type of queries. So, when you try to […]
Welcome to our blog post series covering the typical conversion issues of AWS Schema Conversion Tool (SCT). DBBest team has a vast experience in database migration area. So, we decided to start the blog post series covering the most common conversion issues of AWS SCT. Each blog post will be devoted to a certain action item and will provide specific solution or a workaround for a problem. Below you can find the table of contents for blog posts, related to the […]
Oracle’s NumToYmInterval function converts the provided number value of specified time units or expression to an interval year to month literal. Usually, we use the interval values when dealing with dates. MySQL provides a certain level of support for intervals but does not support storing values of this type in variables. Hence, MySQL does not have any functions that are able to return interval values. The functions working with date intervals are commonly used by reporting and other type of […]
Oracle’s subtype declaration allows creating an “alias” for some datatype declaration, which can be re-used later as many times as needed. PostgreSQL domains do not match Oracle’s subtypes completely. Thus, whenever you try to convert PL/SQL code containing variables that are declared with defined subtype, SCT will generate the following message: “Issue 5028 — Unable to convert definition of object with unsupported datatype”. Continue reading to learn more about the possible remedies of this issue.
Oracle 9i introduced the predefined SYS_REFCURSOR type meaning we no longer have to define our own REF CURSOR types. We normally use Oracle’s SYS_REFCURSOR datatype to declare the CURSOR variable of a weak type, which means that we don’t know exactly the number of columns and their type. Then this SYS_REFCURSOR variable has to be used together with a dynamic SQL code to open the cursor variable. MySQL does not support cursors of a weak type. So, when you try to […]