Release Notes - SQLines SQL Converter

Version 3.3.131

In progress. Please contact us at support@sqlines.com for updates, improvements and new features.

  • Escaping double quotes in JSON assessment report
  • Removing CREATE PACKAGE specification header except TYPE declarations (Oracle PL/SQL to Java)
  • Converting TYPE name IS RECORD to class with members, getter and setter methods (Oracle PL/SQL to Java)
  • Added Conversion Issues section to the assessment report
  • Added subqueries used in SELECT and INSERT statements to the assessment report
  • Converting TEXT to VARCHAR2(4000) by default (PostgreSQL to Oracle)
  • Converting ALTER TABLE ADD COLUMN (PostgreSQL to Oracle)
  • Parsing ROWNUM conditions with expressions, not just integer constants (from Oracle)
  • Converting JSON to CLOB for version Oracle 19 and earlier by default (to Oracle)
  • Extended assessment with Line metrics - need conversion, automated conversion and manual conversion at script as well as at each statement level
  • Converting ALTER TABLE ALTER COLUMN SET NOT NULL statement (PostgreSQL to Oracle)
  • Converting NUMBER without parameters to DECIMAL without parameters, not to inexact DOUBLE PRECISION (Oracle to PostgreSQL)
  • Added details section for CREATE VIEW statements in the assessment report
  • Converting NOCACHE to CACHE 1 in CREATE SEQUENCE statements (Oracle to PostgreSQL)
  • Added aliases when converting SELECT subqueries (Oracle to MySQL, MariaDB)
  • Converting DELETE table to DELETE FROM table (Oracle to MySQL, MariaDB)
  • Converting ALTER TABLE ALTER COLUMN DROP NOT NULL statement (PostgreSQL to Oracle)
  • Converting LISTAGG function to GROUP_CONCAT (Oracle to MySQL, MariaDB)

Version 3.3.130

Released on June 23, 2024.

  • Converting UPDATE alias SET … FROM tab alias, tab2, … WHERE … (update from another table) to UPDATE tab alias SET … FROM tab2, … WHERE … (SQL Server to PostgreSQL)
  • Converting assignment statements var := expr to SET var = expr (Oracle to MariaDB)
  • Converting statement delimiter @ to ; (from IBM DB2)
  • Commenting CALL SYSPROC.SET_ROUTINE_OPTS('REOPT ALWAYS') system call (from IBM DB2)
  • Converting SET (v1, v2, …) = (SELECT c1, c2, …) to SELECT c1, c2, … INTO v1, v2, … (IBM DB2 to PostgreSQL)
  • Converting LOCATE(substring, string) function to POSITION(substring IN string) (IBM DB2 to PostgreSQL)
  • Converting CALL sp_name to PERFORM sp_name only if the stored procedure is converted to function (IBM DB2 to PostgreSQL)
  • Converting "ISNULL"() function (name in double quotes) to ISNULL() (Sybase ASA to SQL Server)
  • Removing double quotes from built-in functions (CHAR, COUNT, LEFT, MIN, TRUNCATE and others) (Sybase ASA to SQL Server)
  • Converting COMMENT ON VIEW to EXECUTE sp_addextendedproperty (to SQL Server)
  • Converting COMMENT ON PROCEDURE to EXECUTE sp_addextendedproperty (to SQL Server)
  • Fixing derived %TYPE data type mapping for CHAR, VARCHAR, VARCHAR2, TEXT and STRING to String (Oracle PL/SQL to Java)
  • Parsing DROP PUBLIC SYNONYM statement (from Oracle)
  • Commenting DROP PUBLIC SYNONYM, CREATE PUBLIC SYNONYM and GRANT EXECUTE statements (Oracle PL/SQL to Java)
  • Commenting COMMENT TO PRESERVE FORMAT ON PROCEDURE/TRIGGER statements (from Sybase ASA)
  • Converting procedure calls using Java naming convention for schema names (Oracle PL/SQL to Java)
  • Converting FETCH INTO statement (Oracle PL/SQL to Java)
  • Converting EXIT WHEN cur%NOTFOUND to if(!cur_found) break (Oracle PL/SQL to Java)
  • Handling WHEN NO_DATA_FOUND THEN NULL exception (Oracle PL/SQL to Java)
  • Creating JSON report file sqlines_report.json (defined by -jrpt option) for assessment to help build custom reports

Version 3.3.117

Released on March 29, 2024.

  • Converting "GETDATE"(*) function to GETDATE() (Sybase ASA to SQL Server)
  • Converting "DATE"(GETDATE(*)) expression to CONVERT(DATE, GETDATE()) (Sybase ASA to SQL Server)
  • Converting "REPEAT"(string, num) function to REPLICATE(string, num) (Sybase ASA to SQL Server)
  • Converting DECIMAL(65,s) to DECIMAL(38,s) due to data type range limitations (MySQL to SQL Server)
  • Improvement for converting (+) outer joins with mixed outer, non-outer and constant conditions in WHERE clause including T.COL(+)=T.COL2(+) conditions referencing the same table (from Oracle)
  • Converting EOMONTH function to LAST_DAY (SQL Server to Oracle, Redshift)
  • Removing constraint name for DEFAULT values (SQL Server to MySQL, MariaDB)
  • Converting PRINT text statement outside procedural block to SELECT text AS '' (if there are expressions) or \! echo 'text' (SQL Server to MySQL, MariaDB)
  • Parsing nested multi-line comments /* comment /* nested comment */ text */ (SQL Server, PostgreSQL etc.)
  • Fixing the parser for UPDATE statement with SET col = (expr) expression in parentheses (from SQL Server, Sybase)
  • Parsing UPDATE STATISTICS table_name statement (from SQL Server)
  • Escaping '\' with '\\' in string literals (SQL Server to MySQL)
  • Removing (+) outer join condition if only one table is defined in the FROM clause (from Oracle)
  • Converting (+) outer join condition with a bind variable i.e. T1.C1(+) = :1 (from Oracle)
  • Fixing declaration conversion if DECLARE SQLCODE INTEGER DEFAULT 0 is specified first (IBM DB2 to PostgreSQL)
  • Removing DECLARE keyword from cursor declaration (IBM DB2 to PostgreSQL)
  • Converting OFFSET n ROWS FETCH NEXT k ROWS ONLY to OFFSET n LIMIT k (SQL Server to PostgreSQL)
  • Converting SELECT col + ',' FROM tab FOR XML PATH() to SELECT STRING_AGG(col + ',', ) FROM tab (SQL Server to PostgreSQL)
  • Moving DECLARE CURSOR statements inside procedure to the declaration block (SQL Server to PostgreSQL)
  • Parsing DELETE alias FROM table AS alias INNER JOIN … syntax (from SQL Server)
  • Supporting table re-oredring in FROM clause when converting legacy (+) outer join syntax (from Oracle)
  • Parsing boolean expressions in THEN clause of CASE expression (from MySQL, PostgreSQL)
  • Parsing string sequence 'a' 'b' 'c' meaning single string literal 'abc' (from MySQL)
  • Parsing REGEXP operator (from MySQL)
  • Parsing CONVERT(exp USING encoding) function (from MySQL)
  • Parsing legacy operator && for AND condition (from MySQL)
  • Converting TO_CHAR(SYSDATE, 'D') to CONVERT(VARCHAR, DATEPART(DW, GETDATE())) (Oracle to SQL Server)
  • Converting ending / in anonymous PL/SQL block to GO statement (Oracle to SQL Server)
  • Disclosing %ROWTYPE variables in anonymous PL/SQL block (Oracle to SQL Server, MySQL, MariaDB)
  • Parsing OBJECT_ID function with the second object type parameter (from SQL Server)
  • Converting pattern IF EXISTS (SELECT * FROM sys.procedures WHERE type='P' AND name LIKE 'sproc') DROP PROCEDURE sproc to DROP PROCEDURE IF EXISTS sproc (SQL Server to PostgreSQL, MySQL, MariaDB)

Version 3.3.113

Released on December 29, 2023.

  • Converting table functions (RETURNS TABLE) to pipelined functions (SQL Server to Oracle)
  • Removing IN in parameters in CREATE FUNCTION statement (Oracle to Snowflake)
  • Converting RETURN to RETURNS, and IS to LANGUAGE SQL AS $$ in CREATE FUNCTION statement (Oracle to Snowflake)
  • Converting cursor declarations CURSOR cur(params) IS SELECT … to cur CURSOR FOR SELECT … with parameter placeholders ? (Oracle to Snowflake)
  • Converting OPEN cur(param1, …) to OPEN cur USING (param1, …) statement (Oracle to Snowflake)
  • Converting IF condition THEN … ELSIF … END IF to IF (condition) THEN … ELSEIF … END IF (Oracle to Snowflake)
  • Removing DETERMINISTIC option in CREATE PROCEDURE (from IBM DB2)
  • Extending ALTER DATABASE parser and converting COLLATE option (SQL Server to MySQL, MariaDB)
  • Commenting EXEC sp_fulltext_database (from SQL Server)
  • Converting CREATE USER statement (SQL Server to MySQL, MariaDB)
  • Parsing and commenting EXEC sp_addrolemember statement (from SQL Server)
  • Parsing and commenting CREATE ASSEMBLY statement (from SQL Server)
  • Converting COLLATE Latin1_General_CI_AS in CREATE TABLE statement (SQL Server to MySQL, MariaDB)
  • Parsing and commenting INCLUDE columns clause in CREATE INDEX statement (SQL Server to MySQL, MariaDB)
  • Converting CREATE STATISTICS statement to ANALYZE TABLE statement (SQL Server to MySQL, MariaDB)
  • Parsing bitwise operators | (OR) and ^ (XOR) (from SQL Server, MySQL)
  • Parsing and commenting CREATE AGGREGATE statement (from SQL Server)
  • Parsing and adding conversion warning for WITH XMLNAMESPACES clause in SELECT (from SQL Server)
  • Parsing and adding conversion warning for SET TRANSACTION ISOLATION LEVEL SNAPSHOT (from SQL Server)
  • Parsing and commenting EXECUTE sp_addextendedproperty 'URL' (from SQL Server)
  • Converting PERSISTED keyword to STORED for computed columns (SQL Server to MySQL, MariaDB)
  • Removing PERSISTED keyword for computed columns (SQL Server to Oracle)
  • Moving inner DECLARE statements to the beginning of BEGIN-END block (SQL Server to MySQL, MariaDB)
  • Converting ALTER TABLE tab ADD CONSTRAINT cns DEFAULT exp FOR col (adding default for a column) to ALTER TABLE tab MODIFY (col DEFAULT exp) (SQL Server to Oracle)
  • Fixing moving expressions when converting DATEADD to INTERVAL expression (SQL Server to Oracle)
  • Removing WITH CHECK clause in ALTER TABLE ADD CONSTRAINT statement (from SQL Server)
  • Converting ALTER TABLE tab CHECK CONSTRAINT cns to ALTER TABLE tab ENABLE CONSTRAINT cns (SQL Server to Oracle)
  • Parsing a list of options in SET statement i.e. SET ANSI_PADDING, ANSI_WARNINGS, …, ANSI_NULLS ON (from SQL Server)
  • Removing IF @@ERROR <> 0 SET NOEXEC ON error handling pattern (from SQL Server)
  • Converting PRINT text statement to PROMPT text in SQL scripts (not procedural block) (SQL Server to Oracle)
  • Fixing adding SYS_REFCURSOR for stored procedures returning result sets when existing parameters are not enclosed with () (SQL Server to Oracle)
  • Fixing parser for RETURN SELECT from a table-valued function (from SQL Server)
  • Converting function “NOW”() with name enclosed with double quotes (Sybase SQL Anywhere to SQL Server)
  • Using OUT for REFCURSOR when converting procedures returning result sets (IBM DB2 to PostgreSQL)
  • Fixing parser for single row subselect without FROM like (SELECT 1) (from SQL Server)
  • Parsing DBINFO function with 2 parameters (from Informix)
  • Converting function CONVERT(MONEY, exp) (from SQL Server)
  • Parsing // single line comment (Snowflake)
  • Converting NVARCHAR(MAX) to NVARCHAR2 in parameters in functions and procedures (SQL Server to Oracle)
  • Adding statement delimiter / for CREATE FUNCTION (SQL Server to Oracle)
  • Moving CREATE GLOBAL TEMPORARY TABLE definitions outside CREATE PROCEDURE when converting DECLARE @name TABLE local tables (SQL Server to Oracle)
  • Converting FROM table_func(params) to FROM TABLE(table_func(params)) for calling table valued functions (SQL Server to Oracle)
  • Converting DECLARE @var type = (SELECT …) to a standalone declaration and SELECT INTO statement (SQL Server to Oracle)
  • Converting UNIQUEIDENTIFIER to CHAR in function and procedure parameters (SQL Server to Oracle)
  • Handling reserved words in identifiers (to Oracle)
  • Parsing COUNT(UNIQUE col1, …) function (from Informix)
  • Converting SELECT … FROM … LIMIT n to SELECT TOP n … clause (MySQL to SQL Server)

Version 3.3.111

Released on December 04, 2023.

  • Converting TO_CHAR(exp, 'YYYY/MM/DD HH24:MI:SS') to REPLACE(CONVERT(VARCHAR(19), exp, 20), '-', '/') (Oracle to SQL Server)
  • Converting IIF function to CASE expression (SQL Server to Synapse)
  • Converting DATEDIFF(YEAR, dt1, dt2) to EXTRACT(YEAR FROM AGE(dt2, dt1)) (SQL Server to PostgreSQL)
  • Commenting ALTER TABLE table CHECK CONSTRAINT constraint (SQL Server to MariaDB)
  • Commenting CONNECT TO statement (from IBM DB2)
  • Converting CREATE SEQUENCE statement (IBM DB2 to Snowflake)
  • Converting string1 CONCAT string2 operator to || operator (IBM DB2 to PostgreSQL, Redshift)
  • Converting VALUES c1, c2, … INTO v1, v2, … to individual v1 := c1; v2 := c2; … assignment statements (from IBM DB2)
  • Commenting FULLTEXT INDEX in CREATE TABLE (from MySQL, MariaDB)
  • Converting ENUM data type to VARCHAR with CHECK constraint (from MySQL, MariaDB)
  • Converting SET data type to VARCHAR (from MySQL, MariaDB)
  • Commenting ON UPDATE CASCADE clause in FOREIGN KEY constraint (MySQL, MariaDB to Oracle)
  • Adding CHAR length semantics specifier explicitly when converting CHAR(n) and VARCHAR(n) data types (MySQL, MariaDB to Oracle)
  • Converting VARCHAR(n) BINARY to RAW(n) (MySQL to Oracle)
  • Converting stored procedures with CURSOR WITH RETURN (result sets) (IBM DB2 to PostgreSQL)
  • Removing ON ROLLBACK DELETE ROWS in temporary tables (IBM DB2 to PostgreSQL)
  • Converting VARCHAR(exp, length) function to CAST(exp AS VARCHAR(length)) (from IBM DB2)
  • Converting TIMESTAMP function (from IBM DB2)
  • Bug fix - ALTER TABLE tab ALTER COLUMN col RESTART WITH num conversion for PostgreSQL 10 and later (IBM DB2 to PostgreSQL)
  • Bug fix - string+LEFT/RIGHT() expression conversion (SQL Server to PostgreSQL)
  • Bug fix - DAY function conversion with string literal, adding DATE before the literal (SQL Server to PostgreSQL)
  • Bug fix - When converting DATEDIFF enclose expression with () before casting to TIMESTAMP like f() + … to (f() || … )::TIMESTAMP (SQL Server to PostgreSQL)
  • Bug fix - Parsing ON UPDATE CURRENT_TIMESTAMP() in CREATE TABLE (from MySQL, MariaDB)
  • Bug fix - Converting CURRENT_TIMESTAMP() to CURRENT_TIMESTAMP (from MySQL, MariaDB to Oracle)
  • Bug fix - Parsing storage options for CREATE TABLE (ENGINE, AUTO_INCREMENT, CHARSET etc.) (from MariaDB)
  • Bug fix - BIT data type conversion (MariaDB to Oracle)
  • Bug fix - CREATE PROCEDURE parser when there are no parameters (from IBM DB2)
  • Bug fix - Fixing the parser when UNION keyword is used for an alias (from IBM DB2)
  • Bug fix - Removing WITH HOLD clause from cursor declarations (from IBM DB2)

Version 3.3.110

Released on September 10, 2023.

  • Converting LISTAGG function to STRING_AGG function supporting ORDER BY conversion (Oracle to PostgreSQL)
  • Converting FROM_TZ function to AT TIME ZONE expression (Oracle to SQL Server)
  • AT TIME ZONE timezone name conversion support i.e. 'EST' to 'Eastern Standard Time' (Oracle, PostgreSQL to SQL Server)
  • Removing 'NLS_DATE_LANGUAGE = language' when converting TO_DATE and TO_CHAR functions (from Oracle)
  • Converting TRUNC(exp, 'MI') to CONVERT(DATETIME, CONVERT(VARCHAR(16), exp, 120) + ':00') (from Oracle to SQL Server)
  • Converting TRUNC(exp, 'MONTH') and TRUNC(exp, 'MON') to CONVERT(DATETIME, CONVERT(VARCHAR(7), exp, 120) + '-01') (from Oracle to SQL Server)
  • Converting EXTRACT(HOUR FROM datetime) function to DATEPART(HOUR, datetime) (Oracle to SQL Server)
  • Converting EXTRACT(MINUTE FROM datetime) function to DATEPART(MINUTE, datetime) (Oracle to SQL Server)
  • Converting TO_CHAR(exp, 'MON-YYYY') to REPLACE(RIGHT(CONVERT(VARCHAR(11), exp, 106), 8), ' ', '-') (Oracle to SQL Server)
  • Converting TO_CHAR(exp, 'MM/DD/YYYY') to CONVERT(VARCHAR(10), exp, 101) (Oracle to SQL Server)
  • Converting INSERT table statement to INSERT INTO table statement (SQL Server, MySQL to Oracle, IBM DB2)
  • Converting positional GROUP BY 1, 2, 3 to GROUP BY with SELECT expressions (to SQL Server)
  • Converting specific OUTER join to ANSI SQL LEFT OUTER JOIN (from Informix)
  • Converting PRIMARY KEY (c1, c2, …) CONSTRAINT schema.name to CONSTRAINT name PRIMARY KEY (c1, c2, …) (from Informix)
  • Bitwise BITAND function to & (AND) operator conversion (Oracle to SQL Server)
  • Removing NO ORDER clause from GENERATED AS IDENTITY (from IBM DB2)
  • Removing ORGANIZE BY ROW clause in CREATE TABLE (from IBM DB2)
  • Removing INCLUDE NULL KEYS from CREATE INDEX options (from IBM DB2)
  • Removing the schema name when converting CREATE INDEX statement (to PostgreSQL)
  • Bug fix - Parsing 'NLS_DATE_LANGUAGE = language' in TO_CHAR function (from Oracle)
  • Bug fix - Converting TO_TIMESTAMP(string, 'YYYY-MM-DD HH24:MI:SS.FF3') to CONVERT(DATETIME, string) (Oracle to SQL Server)
  • Bug fix - Converting TO_DATE(string, 'MM/DD/YYYY') to CONVERT(DATETIME, string) (Oracle to SQL Server)
  • Bug fix - CROSS JOIN clause parser
  • Bug fix - Convering AFTER UPDATE, INSERT, DELETE events to AFTER UPDATE OR INSERT OR DELETE in triggers (SQL Server to PostgreSQL)
  • Bug fix - Moving ON table AFTER INSERT after the trigger event AFTER INSERT ON table in CREATE TREIGGER conversion (SQL Server to PostgreSQL)
  • Bug fix - Adding FOR EACH ROW when converting triggers (SQL Server to PostgreSQL)

Version 3.3.107

Released on July 25, 2023.

  • Converting JSON_VALUE function to JSONB_PATH_QUERY function (Oracle to PostgreSQL)
  • Using TRIM available since SQL Server 2017 by default and convert to RTRIM(LTRIM(string) is version is lower than 2017 (to SQL Server)
  • Converting TRUNC(datetime) to DATE_TRUNC('day', datetime) (Oracle to PostgreSQL)
  • Parsing CONNECT_BY_ROOT and CONNECT_BY_ISLEAF hierarchical query functions (from Oracle)
  • Conversion errors section conversion_errors in the assessment report template sqlines_report.tpl
  • Conversion warnings section conversion_warnings in the assessment report template sqlines_report.tpl
  • Converting STRING data type to TEXT (Hive to PostgreSQL)
  • Parsing STORED AS type clause (from Hive)
  • Converting FROM_UNIXTIME(epoch) function to TO_TIMESTAMP(epoch) (Hive, Spark to Snowflake)
  • Converting LIMIT offset, rows to OFFSET offset ROWS FETCH NEXT rows ROWS ONLY (MySQL to Oracle)
  • Parsing UPDATE t INNER JOIN t2 ON … SET … WHERE statement (from MySQL)
  • Converting UPDATE t INNER JOIN … statement to UPDATE FROM (MySQL to PostgreSQL)
  • Converting MERGE to MERGE INTO statement (SQL Server to PostgreSQL)
  • Converting DATEPART function to DATE_PART (SQL Server to PostgreSQL)
  • Bug fix - Not adding an alias for FROM (t1 JOIN t2 …) clauses (Oracle to SQL Server, PostgreSQL)
  • Bug fix - Redundant ( could be added when converting MONTHS_BETWEEN to DATEDIFF(MONTH, …) when -udf_months_between=no is set (Oracle to SQL Server)
  • Bug fix - Converting SYSDATE to CURRENT_TIMESTAMP(0), was CURRENT_TIMESTAMP (Oracle to PostgreSQL)
  • Bug fix - Converting CONNECT BY PRIOR with preceeding UNION ALL clause (from Oracle)
  • Bug fix - Converting DECODE with NULL condition to searched CASE expression (from Oracle)
  • Bug fix - SELECT statements in SELECT expression list of other SELECT statements were reported as standalone SELECT statements (Assessment Report)
  • Bug fix - Converting session variables @var to local variables v_var when there are no any declaration in source procedure (from MySQL)
  • Bug fix - Parsing qualified identifiers having spaces around dot (.)
  • Bug fix - Determing object type (procedure or function) when converting DENY EXECUTE to REVOKE EXECUTE ON PROCEDURE | FUNCTION (SQL Server to PostgreSQL)
  • Bug fix - DATEADD to INTERVAL expression conversion if an expression is added (not a single number) (SQL Server to PostgreSQL)
  • Bug fix - DATEADD to INTERVAL expression conversion for ss and mi units (SQL Server to PostgreSQL)

Version 3.3.103

Released on July 09, 2023.

  • Converting JSON_TABLE function (Oracle to PostgreSQL)
  • Converting PIVOT clause to CASE expressions with aggregations (SQL Server to PostgreSQL)
  • Converting CONNECT BY with multiple tables to recursive CTE (from Oracle)
  • Converting EXCEPTION WHEN NO_DATA_FOUND to IF @@FETCH_STATUS <> 0 BEGIN … END block (Oracle to SQL Server)
  • Parsing CREATE FUNCTION with optional BEGIN-END keywords (from Sybase ASE)
  • Converting AUTO_INCREMENT to GENERATED ALWAYS AS IDENTITY by default (MySQL to PostgreSQL)
  • Convering CALL sp_name from another stored procedure to PERFORM sp_name (MySQL to PostgreSQL)
  • Converting session variables @var to local variables if they are initialized within the stored procedure (MySQL to PostgreSQL)
  • Removing alias when converting simple form of UPDATE tab alias SET alias.col… WHERE alias.col… (Oracle to SQL Server)
  • Removing NO ALLOW REVERSE SCANS from CREATE INDEX (from IBM DB2)
  • Converting SET CURRENT SCHEMA = name to SET SCHEMA 'name' (IBM DB2 to PostgreSQL)
  • Converting SET CURRENT PATH to SET search_path TO (IBM DB2 to PostgreSQL)
  • Converting SIGNAL SQLSTATE 'sqlstate' ('message') to RAISE EXCEPTION SQLSTATE 'sqlstate' USING MESSAGE = 'message' (IBM DB2 to PostgreSQL)
  • Removing NO CASCADE option in CREATE TRIGGER (from IBM DB2)
  • Converting PRINT statement to RAISE NOTICE (SQL Server to PostgreSQL)
  • Converting EXEC sp_executesql to EXECUTE USING (SQL Server to PostgreSQL)
  • Converting BREAK statement to exit from a loop to EXIT (SQL Server to Oracle, PostgreSQL)
  • Using smaller number of REFCURSOR when converting result sets in IF ELSE branches (SQL Server to PostgreSQL)
  • -column_name_quotes option to quote column identifiers
  • -object_name_quotes option to quote object name identifiers (schemas, tables, views, aliases etc.)
  • -object_name_case option to convert object names (tables i.e.) to upper or lower case in DDL scripts
  • -column_name_case option to convert column names to upper or lower case in DDL scripts
  • -pg_use_function_single_result_set option to convert result sets returned from stored procedure to RETURN QUERY (SQL Server, Sybase, MySQL to PostgreSQL)
  • Parsing PRINT statement with format and parameters (from Sybase)
  • Parsing READ_ONLY option in DECLARE CURSOR (from SQL Server)
  • Converting GETUTCDATE() to CURRENT_TIMESTAMP(3) AT TIME ZONE 'UTC' (SQL Server to PostgreSQL)
  • Converting DENY EXECUTE ON name TO role to REVOKE EXECUTE ON PROCEDURE name FROM role (SQL Server to PostgreSQL)
  • Parsing PIVOT clause (from SQL Server)
  • Bug fix - SELECT … INTO var conversion to SELECT @var = … (wrong location of variable) (Oracle to SQL Server)
  • Bug fix - Adding (cur REFCURSOR) when a stored procedure has empty parameter list () (MySQL to PostgreSQL)
  • Bug fix - Adding END at the end of trigger block to single statement triggers (to PostgreSQL)
  • Bug fix - Adding () around parameters when converting EXECUTE sp_name @parm = @value with single parameter (SQL Server to PostgreSQL)
  • Bug fix - Parsing SELECT @var = (…) for non-SELECT assignment expressions starting with open parenthesis (from SQL Server, Sybase)
  • Bug fix - Outer join (+) conversion for 3 and more table when condition goes right after WHERE clause (from Oracle)

Version 3.3.101

Released on June 26, 2023.

  • Converting CASE statement to IF statement (from IBM DB2, Oracle to SQL Server)
  • Converting HEX function to CONVERT expressions (from IBM DB2 to SQL Server)
  • Converting TRANSLATE function changing the order of parameters and dealing with short replacement string (IBM DB2 to SQL Server)
  • Converting LISTAGG function STRING_AGG (Oracle to SQL Server)
  • Converting CHAR(string, num) function to CAST(string AS CHAR(num)) (IBM DB2 to SQL Server)
  • Converting ROUND(num) with single parameter to ROUND(num, 0) (to SQL Server)
  • Converting EXTRACT(SECOND FROM datetime) to DATEPART(SECOND, datetime) (Oracle to SQL Server)
  • Converting TO_CHAR(expr) without format to expr::text (Oracle to PostgreSQL)
  • Converting STR function to TO_CHAR with the corresponding format (SQL Server to Oracle, PostgreSQL)
  • Converting NOCACHE, NOORDER, NOCYCLE and MAXVALUE options of CREATE SEQUENCE statement (Oracle to SQL Server)
  • Removing () when converting INSERT … (SELECT …) (from Oracle to SQL Server)
  • Parsing DENY EXECUTE ON statement (from SQL Server)
  • Bug fix - Converting parameters to @param if they were defined as “param” but later referenced as param (to SQL Server)
  • Bug fix - Parsing SELECT @ret = exp FROM … assignment (from Sybase SQL Anywhere)
  • Bug fix - Parsing legacy outer join operators *= and =* in WHERE clause (from Sybase SQL Anywhere)
  • Bug fix - Parsing ORDER BY clause in XMLAGG function (from IBM DB2, Oracle)
  • Bug fix - Parsing INHERIT ISOLATION LEVEL WITH | WITHOUT LOCK REQUEST clause in CREATE FUNCTION (from IBM DB2)
  • Bug fix - Parsing CONVERT(FLOAT, expr) function (from SQL Server)
  • Bug fix - Parsing CONVERT(DECIMAL(p,s, expr) function (from SQL Server)
  • Bug fix - Converting CONVERT(DATETIME, expr) to CAST(expr AS TIMESTAMP(3)) (SQL Server to PostgreSQL)
  • Bug fix - Converting CONVERT(BIT, expr) to CAST(expr AS BOOLEAN) (SQL Server to PostgreSQL)
  • Bug fix - Redandant END IF was added for IF … ELSE IF … conversion (SQL Server to PostgreSQL)

Version 3.3.100

Released on May 29, 2023.

  • Converting STUFF function with SELECT … FOR XML PATH to GROUP_CONCAT (SQL Server to MariaDB, MySQL) and LISTAGG (SQL Server to Oracle)
  • Converting BEGIN CATCH to DECLARE CONTINUE HANDLER FOR SQLEXCEPTION (SQL Server to MariaDB, MySQL)
  • Remove options NOKEEP, NOSCALE, GLOBAL from CREATE SEQUENCE (Oracle to PostgreSQL)
  • Converting sequence options for GENERATED AS IDENTITY in CREATE TABLE statement (Oracle to PostgreSQL)
  • Converting conditional predicate UPDATING to TG_OP = 'UPDATE' in CREATE TRIGGER (Oracle to PostgreSQL)
  • Converting REFERENCING NEW AS … OLD AS … to REFERENCING NEW TABLE AS … OLD TABLE AS … (Oracle to PostgreSQL)
  • Not adding p_ parameter prefix when converting parameters starting from @p_ (from SQL Server, Sybase)
  • Parsing standalone hint specified without WITH keword i.e. SELECT * FROM orders (NOLOCK) (from SQL Server)
  • Converting date and time literals TIMESTAMP '2023-05-23 09:30:00' to { ts '2023-05-23 09:30:00' } (Oracle to SQL Server)
  • Removing OR REPLACE and terminating / when converting CREATE OR REPLACE TYPE statement (Oracle to PostgreSQL)
  • -udf_months_between option to define whether to use UDF to implement Oracle MONTHS_BETWEEN function in SQL Server or simplified conversion to DATEDIFF(MONTH, …) that produces different result (Oracle to SQL Server)
  • Removing WITH READ ONLY clause from CREATE VIEW (Oracle to MariaDB Oracle compatibility mode)
  • Bug fix - Removing constraint name for inline named NOT NULL constraints in CREATE TABLE (Oracle to MariaDB Oracle compatibility mode)
  • Bug fix - Oracle (+) outer join conversion when the order of tables/columns in FROM and WHERE clauses is different and LEFT/RIGHT join type was not correct (from Oracle)
  • Bug fix - Changing column reference in trigger for redefined NEW i.e. from :NEWROW.c1 to NEWROW.c1 (Oracle to PostgreSQL)
  • Bug fix - Scanning sub-directories for searching files specified by -in option
  • Bug fix - Removing MAX when converting CONVERT(NVARCHAR(MAX), string) to CONVERT(string, CHAR) (from SQL Server to MariaDB, MySQL)
  • Bug fix - Converting CURRENT_DATE that includes time part (Oracle to SQL Server)
  • Bug fix - Convertng TRUNC(CURRENT_DATE) expression (Oracle to SQL Server)
  • Bug fix - Converting TRUNC(number) to FLOOR (Oracle to SQL Server)
  • Bug fix - Fixing IF BEGIN … END; ELSE IF … parser when END followed by optional semicolon (;) (from SQL Server)
  • Bug fix - Replacing PL/SQL delimiter / with // and resetting DELIMITER ; in CREATE PROCEDURE (Oracle to MariaDB Compatibility mode)
  • Bug fix - Fixing error “Error writing output” when converting a single file and -out specifies the existing directory

Version 3.3.77

Released on March 22, 2023.

  • Generating function that RETURNS TRIGGER when converting CREATE TRIGGER (Oracle to PostgreSQL)
  • Converting RAISE_APPLICATION_ERROR to RAISE EXCEPTION (Oracle to PostgreSQL)
  • Converting GETDATE() + 1.1 (decimal expressions) to INTERVAL expressions (SQL Server to MySQL, MariaDB, SingleStore)
  • Converting SELECT statements returning result sets in stored procedures to DECLARE CURSOR WITH RETURN and OPEN cursor statements (SQL Server, Sybase, MySQL to DB2)
  • Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISPROCEDURE') = 1) DROP PROCEDURE (from SQL Server)
  • Recognizing pattern IF EXISTS (SELECT * FROM sysobjects WHERE … OBJECTPROPERTY(id, N'ISVIEW') = 1) DROP VIEW (from SQL Server)
  • Adding explicit casting when converting '0' < 1 string-number comparison (Oracle to PostgreSQL)
  • Adding Azure Synapse as target database
  • Adding code snippets for non-ASCII identifiers in the assessment report
  • Converting RPAD function (Oracle, MySQL, PostgreSQL, Netezza to SQL Server, Synapse)
  • Converting PRIMARY KEY to PRIMARY KEY NONCLUSTERED NOT ENFORCED (to Synapse)
  • Converting UNIQUE constraint to UNIQUE NOT ENFORCED (to Synapse)
  • Removing schema name in DROP TRIGGER and CREATE TRIGGER statements (to PostgreSQL)
  • Adding table name to DROP TRIGGER (to PostgreSQL)
  • Converting SQLCODE to SQLSTATE (Oracle to PostgreSQL)
  • Converting conditional predicate INSERTING to TG_OP = 'INSERT' in CREATE TRIGGER (Oracle to PostgreSQL)
  • Converting DATETIME2(p) to TIMESTAMP(p) (SQL Server to Snowflake)
  • Parsing and commenting ORGANIZATION EXTERNAL clause in CREATE TABLE (from Oracle)
  • Commenting GLOBAL TEMPORARY in CREATE TABLE (to SQL Server)
  • Moving NOT NULL constraint from ALTER TABLE … MODIFY (column NOT NULL ENABLE) to CREATE TABLE statement (Oracle to SQL Server)
  • Renaming primary constraint name if it is the same as an index name defined on the table (to SQL Server)
  • Changing the data type of the foreign key column to match the data type of the primary key column (to SQL Server)
  • Adding NOT NULL constraints explicitly for primary key columns (Oracle to SQL Server)
  • Bug fix - Parsing declaration block in CREATE TRIGGER (Oracle to PostgreSQL)
  • Bug fix - Not specifying INTERVAL DAY literal precision when used in PL/SQL code (to Oracle)
  • Bug fix - Converting DATEADD(HH, datetime) function (SQL Server, Sybase to Oracle, PostgreSQL)
  • Bug fix - Removing function name after END function if CREATE FUNCTION has the qualified name schema.function (Oracle to PostgreSQL)
  • Bug fix - Handling UTF-8 0xC2A0 sequence (non-breaking space) in the source scripts as it broke the SQL parser
  • Bug fix - Converting :NEW.column and :OLD.column references to NEW.column and OLD.column in CREATE TRIGGER (Oracle to PostgreSQL)
  • Bug fix - Adding PERFORM for procedure call within a trigger (Oracle to PostgreSQL)
  • Bug fix - Converting enclosed [schema]] name (SQL Server to Snowflake)
  • Bug fix - Removing schema name from index when the schema name mapping is applied (to SQL Server, MariaDB, MySQL)
  • Bug fix - Parsing USING INDEX for primary key columns (from Oracle)

Version 3.3.73

Released on February 15, 2023.

  • Dynamic SQL conversion referenced in EXEC statements (from SQL Server, Sybase)
  • Moving in-place declarations to DECLARE section of BEGIN-END block (to Oracle)
  • Converting BEGIN CATCH to EXCEPTION WHEN OTHERS block (SQL Server to SingleStore)
  • Converting RAISERROR function to RAISE USER_EXCEPTION (SQL Server to SingleStore)
  • Convert FORMAT function to DATE_FORMAT (SQL Server to MySQL, MariaDB, SingleStore)
  • Commenting SET XACT_ABORT ON statement in stored procedures (SQL Server to Singlestore)
  • Commenting SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED | COMMITTED in stored procedures (SQL Server to Singlestore)
  • Converting EXEC sp_name params procedure call into sp_name(params) (SQL Server, Sybase to Oracle)
  • Parsing FORWARD_ONLY and STATIC cursor options in DECLARE CURSOR statement (from SQL Server)
  • Converting pattern IF EXISTS (SELECT * FROM sys.objects … ) DROP TRIGGER (from SQL Server)
  • Converting patterns IF EXISTS (SELECT * FROM sysobjects … ) DROP … (from SQL Server)
  • Converting SQL*Plus EXECUTE to CALL statement with named parameters (Oracle to PostgreSQL)
  • Parsing ALTER TABLE ADD column for adding multiple columns in single statement (from Oracle)
  • Parsing FOREIGN KEY name (columns) syntax (from DB2 z/OS)
  • Allowing to define empty prefix for -tsql_parameters_prefix option by specifying ”” or '' value
  • Commenting COLLATE name clause for expressions (from SQL Server)
  • Bug fix - Not adding FROM dual when converting SELECT @var=expr to v_var := expr (SQL Server, Sybase to Oracle)
  • Bug fix - Removing constraint name for inline named NOT NULL constraints in CREATE TABLE (Oracle to MySQL)
  • Bug fix - Converting DECLARE @tab AS TABLE to CREATE GLOBAL TEMPORARY TABLE (SQL Server to Oracle)
  • Bug fix - Replace comma (,) with semicolon (;) when converting SELECT @var = exp, @var2 = exp2, … to var := exp; @var2 = exp2 (SQL Server, Sybase to Oracle, PostgreSQL)
  • Bug fix - Parsing CROSS APPLY join when AS keyword is not specified before the alias (from any database supporting CROSS APPLY)
  • Bug fix - DATEADD(dd, num, exp) conversion - replace DD with DAY in interval expression (SQL Server, Sybase to Oracle)
  • Bug fix - Parsing ELSE clause when converting IF @@ERROR <> 0 to EXCEPTION block (SQL Server to Oracle)
  • Bug fix - Removing DEALLOCATE cursor statement if semicolon (;) follows CLOSE cursor (from SQL Server)
  • Bug fix - Parsing WITH (NOLOCK) for multi-table FROM without table aliases (from SQL Server)
  • Bug fix - Converting #tmp.column identifiers referencing temporary tables (from SQL Server, Sybase)
  • Bug fix - Converting enclosed table alias [alias] (from SQL Server, Sybase)
  • Bug fix - Adding semicolon (;) when converting END CATCH to END; (SQL Server to SingleStore)
  • Bug fix - Converting enclosed identifiers for temporary tables [#tmp] (from SQL Server)
  • Bug fix - Parsing SELECT * INTO table FROM table2 for non-temporary tables (from SQL Server, Sybase)

Version 3.3.71

Released on February 07, 2023.

  • -ident option to transform the identifiers using the specified template
  • Converting INSTR function with 4 parameters to CHARINDEX if the 4th parameter has value of 1 (i.e. to find the first occurrence) (Oracle to SQL Server)
  • Outer join syntax (+) conversion for more than 2 tables (from Oracle)
  • Data type mapping (sqlines_dtmap.txt) for various combinations of length and scale for numeric data types
  • Reporting syntax errors with code snippets in sqlines_report.html file when -a assessment option is specified
  • Adding OR REPLACE when converting CREATE PROCEDURE statement (to SingleStore)
  • Removing IN, commenting OUT parameter specifiers for stored procedures (MySQL to SingleStore)
  • Removing LANGUAGE SQL, NOT DETERMINISTIC, READS SQL DATA and COMMENT clauses when converting stored procedures and user-defined functions (from MySQL)
  • Moving declarations before BEGIN-END block (MySQL to SingleStore)
  • Converting USE db_name to SET SCHEMA name (SQL Server to DB2)
  • Removing GO statement from CREATE PROCEDURE (SQL Server to DB2)
  • Converting IDENTITY property to GENERATED ALWAYS AS IDENTITY (SQL Server, Sybase to DB2)
  • Removing ASC, DESC column sorting from PRIMARY KEY constraints (SQL Server to DB2)
  • Parsing SELECT … FROM … WHERE … INTO variables clause (INTO at the end of statement) (from MySQL)
  • Converting SELECT statements returning result sets to ECHO SELECT (SQL Server, MySQL to SingleStore)

Version 3.3.70

Released on January 31, 2023.

  • -remd option to remove database name in 3-part identifiers (from SQL Server, Sybase)
  • Commenting foreign key constraints in CREATE TABLE (to SingleStore)
  • Converting _utf8mb4'string' string literals to _utf8'string' (MySQL to SingleStore)
  • COMMENT ON TABLE conversion (to SQL Server)
  • Bug fix: INSTR to dbo.INSTR4 UDF conversion (Oracle to SQL Server)
  • Bug fix: FOR i IN k..m range loop (from Oracle)
  • Bug fix: -smap schema mapping option
  • Bug fix: ALTER TABLE name ADD col type - removing GO (from SQL Server)
  • Bitwise & (AND) operator to BITAND function (SQL Server to Oracle)
  • Bug fix: DROP FUNCTION statement parser
  • IF EXISTS (SELECT * FROM sys.objects WHERE type = 'FN' …) DROP FUNCTION pattern recognition (from SQL Server)
  • Bug fix: NVARCHAR(MAX) to NCLOB conversion (SQL Server to Oracle)
  • Bug fix: Removing size for NVARCHAR(n) in parameters for procedures and functions (to Oracle)
  • Bug fix: Parser CREATE FUNCTION … RETURNS @tab TABLE (cols) column definitions (from SQL Server)
  • Removing WITH ENCRYPTION, SCHEMABINDING options in CREATE FUNCTION (from SQL Server)
  • Bug fix: Parser rules fix - OPEN keyword cannot be an column alias (from SQL Server)
  • Removing OPTION (MAXRECURSION num) query hint (from SQL Server)
  • Parsing ENUM data type (from MySQL)
  • Parsing SET data type (from MySQL)
  • Parsing FULLTEXT index in CREATE TABLE (from MySQL)