SQLines Tool - Command Line Reference

SQLines 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
-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
-rems Remove schema name in target scripts No
-smap Schema name mapping No mapping
-omapf Database object name mapping file No mapping
-meta Column meta data file No meta data

-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

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.

-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.

-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.

  -- 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

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

Need an Option?

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