SQLines SQL Converter - Command Line Reference

SQLines SQL Converter tool allows you to convert database schema (DDL), queries and DML statements, views, stored procedures, packages, functions and triggers between databases.

SQLines converts SQL statements located in one or more files, not from a database. You can run SQLines from a command line. Executables files are sqlines.exe and ./sqlines on Windows and Linux, respectively.

Note. Parameters are case-insensitive, and you can specify them in any order.

Command Line Parameters

The following general parameters are available:

Option Description Default
-s Source database Option must be specified
-t Target database Option must be specified
-tv Target database version The latest supported version
-in List of files (wildcards *.* are allowed) Option must be specified unless -stdin is set
-stdin Read input from STDIN and send the result to STDOUT Read from -in file
-out Output file or directory
-log Log file path sqlines.log in the current directory
-remd Remove database name in target scripts No
-rems Remove schema name in target scripts No
-smap Schema name mapping No mapping
-ident Identifier transformation template None
-omapf Database object name mapping file No mapping
-meta Column meta data file No meta data
-dtmapf Data type mapping file sqlines_dtmap.txt
-a Create assessment report
-rpt Report file path and name (-a should be set) sqlines_report.html
-snp Code snippets file path and name (-a should be set) sqlines_report_snippets.html
-encoding Input file encoding (i.e. Shift-JIS for Japanese)
-name License name Taken from license.txt
-key License key Taken from license.txt

-s and -t - Source and Target Database Types

-s and -t option allow you to specify the source and target database types (SQL dialects), respectively.

Use the following values:

Option Value Database
1 oracle Oracle
2 sql Microsoft SQL Server
3 db2 IBM DB2 for z/OS and LUW
4 sybase Sybase Adaptive Server Enterprise
5 asa Sybase Adaptive Server Anywhere, SQL Anywhere
6 mysql MySQL
7 postgresql PostgreSQL
8 informix Informix Dynamic Server, Extended Parallel Server
9 greenplum Greenplum
10 teradata Teradata
11 netezza Netezza
12 mariadb MariaDB
13 mariadb_ora MariaDB Oracle Compatibility mode
14 hive Apache Hive
15 redshift Amazon Redshift
16 singlestore SingleStore
17 snowflake Snowflake
18 spark Spark SQL
19 synapse Azure Synapse
20 trino Trino
21 presto Presto
22 impala Impala
23 databricks Databricks
24 bigquery Google BigQuery

For example, specify the following command line to convert script.sql file from MySQL to Oracle:

sqlines -s=mysql -t=oracle -in=script.sql

The result will be script_out.sql file in the current directory.

-stdin - Read input from Standard Input

When -stdin option is set, the converter reads the input from STDIN and sends the result to STDOUT.

  • Send source SQL from another program

You can run the tool as follows:

echo "SELECT NVL(a,b) FROM dual" | ./sqlines -stdin -s=oracle -t=sql

The tool reads input from STDIN sent by another program, performs the conversion and outputs the result to STDOUT:

SELECT ISNULL(a,b)

Note. If you use echo command on Windows, do not specify double-quotes.

  • Typing source SQL

Run the tool as follows:

./sqlines -stdin -s=oracle -t=sql

and start typing SQL code for conversion. When it's done, press Enter + Ctrl-D on Linux, or Enter + Ctrl-Z on Windows to start the conversion. You can see the converted result in STDOUT.

-remd - Remove Database Name in Target Scripts

Some databases (SQL Server, Sybase i.e.) support 3-part identifiers: database.schema.name

  -- Table in schema HR in database ORG
  CREATE TABLE org.hr.dept (name VARCHAR(70));

When -remd option is set in the command line, the database name is removed:

sqlines -s=sybase -t=mariadb -in=script.sql -remd

Target script:

  -- Table is without ORG reference now
  CREATE TABLE hr.dept (name VARCHAR(70));

By default, the option is not set, so the database name is not removed. This option applies to 3-part identifiers only.

-rems - Remove Schema Name in Target Scripts

Often a SQL statement contains a schema reference:

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));

When -rems option is set in the command line, the schema name is removed:

sqlines -s=postgresql -t=oracle -in=script.sql -rems

Target script:

  -- Table in the default schema 
  CREATE TABLE dept (name VARCHAR(70));

By default, the option is not set. Note that -smap and -omapf options override -rems option.

-smap - Schema Name Mapping

You can rename or remove the specified schemas using -smap option.

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
 
  -- Table in schema SALES 
  CREATE TABLE sales.product (name VARCHAR(70));

Let's rename schema HR and remove SALES in the target scripts:

sqlines -s=postgresql -t=oracle -in=script.sql -smap=hr:hr2,sales

Target script:

  -- Schema renamed to HR2 
  CREATE TABLE hr2.dept (name VARCHAR(70));
 
  -- Schema name SALES removed 
  CREATE TABLE product (name VARCHAR(70));

Note that -omapf option overrides -smap option.

-ident - Identifier Transformation

You can transform the identifier using -ident option, by specifying words database, schema and object and put any delimiters or characters in the template.

  -- Table in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
 
  -- Table in schema SALES in database ORG
  CREATE TABLE org.sales.product (name VARCHAR(70));

Let's convert identifiers using template: database.schema_object i.e. merging schema and table name with underscore as the delimiter between them:

sqlines -s=sql -t=mysql -in=script.sql -ident=database.schema_object

Target script:

  -- Schema merged with table name
  CREATE TABLE hr_dept (name VARCHAR(70));
 
  -- Schema merged with table name
  CREATE TABLE org.sales_product (name VARCHAR(70));

Note that -omapf option overrides -ident option. Also ident option is applied after -remd, -rems and -smap options.

-omapf - Object Name Mapping File

You can rename the specified database objects (tables, views, functions, procedures etc.) using an object mapping file specified by -omapf option.

By default, the tool uses sqlines_omap.txt file.

  -- Tables in schema HR 
  CREATE TABLE hr.dept (name VARCHAR(70));
  CREATE TABLE hr.emp (name VARCHAR(70));
 
  -- Table in schema SALES 
  CREATE TABLE sales.product (name VARCHAR(70));

Mapping is specified in the ASCII text file in the following format:

-- Comment
hr.dept, hr_new.department

Let's use -omapf, -rems and -smap options as follows:

sqlines -s=postgresql -t=oracle -in=script.sql -rems -smap=hr:hr2 -omapf=sqlines_omap.txt

Target script:

  -- Tables were in source schema HR 
  CREATE TABLE hr_new.department (name VARCHAR(70));
  CREATE TABLE hr2.emp (name VARCHAR(70));
 
  -- Table was in source schema SALES 
  CREATE TABLE product (name VARCHAR(70));

You can see that:

  • Table HR.DEPT was renamed to HR_NEW.DEPARTMENT according to the object mapping file. Note that -omapf option overrides -smap and -rems options.
  • Table HR.EMP was renamed to HR2.EMP according to the schema name mapping specified by -smap option. Note that -smap option overrides -rems option.
  • Table SALES.PRODUCT was changed to PRODUCT according to -rems option.

-meta - Column Metadata File

SQLines SQL Converter tries to resolve the column data types from the context, but it is not always possible.

You can specify a file containing information about table columns and their data types. This is helpful to convert PL/SQL %TYPE attributes to SQL Server or MySQL i.e.

  DECLARE 
     id dept.department_id%TYPE; 
 
  SELECT department_id INTO id 
    FROM dept 
    WHERE name = 'SALES';

Metadata is specified in the ASCII text file in the following format:

-- Comment
dept, department_id, integer

You can use -meta option as follows:

sqlines -s=oracle -t=mysql -in=script.sql -meta=sqlines_meta.txt

-dtmapf - Data Type Mapping File

SQLines SQL Converter provides the default conversion for data types, but you can redefine it.

You can provide your custom data type mapping using an data type mapping file specified by -dtmapf option. By default, the tool uses sqlines_dtmap.txt file.

  CREATE TABLE cities (
     id      INT, 
     name VARCHAR(70));

Mapping is specified in the ASCII text file in the following format:

-- Comment
-- source_datatype(length, scale), target_datatype
INT, BIGINT

We specified to map INT data type to BIGINT.

sqlines -s=postgresql -t=oracle -in=script.sql -dtmapf=sqlines_dtmap.txt

Target script:

  CREATE TABLE cities (
     id      BIGINT, 
     name VARCHAR(70));

Need an Option?

Feel free to contact us at support@sqlines.com.