This is an old revision of the document!
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.
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 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.
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.
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.
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:
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 |
Feel free to contact us at support@sqlines.com.