Driver information
Connection string format, see https://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/
//for oracle xe instance
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<your-oracle-server>)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=xe)));
User Id=<your-oracle-user>;Password=<your-oracle-user-password>;
//for oracle pluggable instance
Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=<your-oracle-server>)(PORT=1521))
(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)));
User Id=<your-oracle-user>;Password=<your-oracle-user-password>;
//for oracle autonomous database on oracle cloud
Data Source=(description= (retry_count=20)(retry_delay=3)(address=(protocol=tcps)(port=1522)
(host=<your-adb-host-name>.oraclecloud.com))(connect_data=(service_name=<your-adb-database-name>))
(security=(<your-adb-server-certificate>)(MY_WALLET_DIRECTORY=<your-walltet-directory>)));
User Id=<your-adb-user>;Password=<your-adb-password>;
| Supported versions: | Oracle 11g* and later |
| Supports transactional DDL | No (Per statement) |
| Supports CSV bulk import | Yes (Text fields only) |
| Supports batch statements | Yes, you can use / for commands and ; for DDL statements |
| Driver package | ODP.NET, see https://www.oracle.com/database/technologies/appdev/dotnet/odp.html |
Getting started
Install yuniql CLI with Chocolatey or use alternative ways listed here https://yuniql.io/docs/install-yuniql/
choco install yuniql
Download samples for Oracle. For samples of other platforms, visit https://github.com/rdagumampan/yuniql/tree/master/samples
git clone https://github.com/rdagumampan/yuniql.git c:\temp\yuniql
cd c:\temp\yuniql\samples\basic-oracle-sample
Deploy a local oracle database container. For guidelines, visit https://medium.com/@firzhan/installing-oracle-12c-as-a-docker-container-44985b29bcae
docker login -u <dockerhub-user-id> -p <dockerhub-password>
docker run --rm -dit --name oracle12c -p 1521:1521 store/oracle/database-enterprise:12.2.0.1-slim
docker ps
CONTAINER ID IMAGE COMMAND CREATED STATUS PORTS NAMES
a8433450faf2 store/oracle/database-enterprise:12.2.0.1-slim "/bin/sh -c '/bin/ba…" About a minute ago Up About a minute (healthy) 0.0.0.0:1521->1521/tcp, 5500/tcp oracle12c
Verify connection with your SQL Developer or any oracle client using these connection information
hostname: localhost
port: 1521
service name: ORCLCDB.localdomain
username: sys
password: Oradoc_db1
Prepare your connection string and environment variables. You can also pass this directly as CLI parameters using --platform oracle, -p <your-workspace> and -c <your-connection-string>. For more connection string samples, visit https://www.connectionstrings.com/oracle-data-provider-for-net-odp-net/.
SETX YUNIQL_PLATFORM "oracle"
SETX YUNIQL_WORKSPACE "c:\temp\yuniql\samples\basic-oracle-sample"
SETX YUNIQL_CONNECTION_STRING "Data Source=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)));User Id=sys;Password=Oradoc_db1;DBA Privilege=SYSDBA;"
Verify connectivity with yuniql check. Here yuniql probes if your connectiong string works by sending ping requests to the server, verifying if the TCP port is open and attempting to connect to the target database.
yuniql check --debug
Running yuniql v1.0.0 for windows-x64
Copyright 2019 (C) Rodel E. Dagumampan. Apache License v2.0
Visit https://yuniql.io for documentation and working samples
INF 2022-01-08 12:17:02Z Verifying ping connectivity to server/cluster localhost...
INF 2022-01-08 12:17:03Z Ping connectivity to server/cluster localhost - Successful
INF 2022-01-08 12:17:03Z Verifying port opening to server/cluster on localhost...
INF 2022-01-08 12:17:03Z Port opening verification to server/cluster localhost - Successful
INF 2022-01-08 12:17:03Z Verifying sql/odbc connectivity to master/catalog on localhost...
ERR 2022-01-08 12:17:05Z Sql/odbc connectivity to master/catalog on localhost - Failed. Error message: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor...
INF 2022-01-08 12:17:05Z Verifying sql/odbc connectivity to database ORCLCDB.localdomain on localhost...
ERR 2022-01-08 12:17:07Z Sql/odbc connectivity to database ORCLCDB.localdomain on localhost - Failed. Error message: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor...
Keep trying yuniql check after few seconds until the container is ready to accept requests. Enable --debug for verbose and richer diagnostic messages.
yuniql check --debug
INF 2022-01-08 12:17:12Z Verifying ping connectivity to server/cluster localhost...
INF 2022-01-08 12:17:12Z Ping connectivity to server/cluster localhost - Successful
INF 2022-01-08 12:17:12Z Verifying port opening to server/cluster on localhost...
INF 2022-01-08 12:17:12Z Port opening verification to server/cluster localhost - Successful
INF 2022-01-08 12:17:12Z Verifying sql/odbc connectivity to master/catalog on localhost...
INF 2022-01-08 12:17:13Z Sql/odbc connectivity to master/catalog on localhost - Successful
INF 2022-01-08 12:17:13Z Verifying sql/odbc connectivity to database ORCLCDB.localdomain on localhost...
INF 2022-01-08 12:17:13Z Sql/odbc connectivity to database ORCLCDB.localdomain on localhost - Successful
Apply migrations with yuniql run. Here, yuniql discovers the project directory, sorts all versions, creates the target database if it doesn’t exist and runs all migration steps in the right order. These includes .sql files, directories, subdirectories, and csv files.
yuniql run -a
WRN 2022-01-08 12:57:22Z Target platform does not support for full transactional DDL operations. All operations will be executed with transaction mode = statement. When transaction mode is set to statement, each batch of sql statement does not participate in a shared transaction context. In the event of failure, the rollback attempt is limited to the individual batch of statement.
INF 2022-01-08 12:57:23Z No explicit target version requested. We'll use latest available locally v0.02 on c:\temp\yuniql\samples\basic-oracle-sample.
INF 2022-01-08 12:57:23Z Target database ORCLCDB.localdomain on localhost not yet configured for migration.
INF 2022-01-08 12:57:24Z Configured database migration support for ORCLCDB.localdomain on localhost.
INF 2022-01-08 12:57:24Z The schema version tracking table is up to date for ORCLCDB.localdomain on localhost.
INF 2022-01-08 12:57:24Z Found 0 script files on c:\temp\yuniql\samples\basic-oracle-sample\_init
INF 2022-01-08 12:57:24Z Found 0 bulk files on c:\temp\yuniql\samples\basic-oracle-sample\_init
INF 2022-01-08 12:57:24Z Executed script files on c:\temp\yuniql\samples\basic-oracle-sample\_init
INF 2022-01-08 12:57:24Z Found 0 script files on c:\temp\yuniql\samples\basic-oracle-sample\_pre
INF 2022-01-08 12:57:24Z Found 0 bulk files on c:\temp\yuniql\samples\basic-oracle-sample\_pre
INF 2022-01-08 12:57:24Z Executed script files on c:\temp\yuniql\samples\basic-oracle-sample\_pre
WRN 2022-01-08 12:57:24Z Transaction is disabled for current session. This version migration run will be executed without explicit transaction context.
INF 2022-01-08 12:57:24Z Found 2 script files on c:\temp\yuniql\samples\basic-oracle-sample\v0.00
+ 01-setup-tables.sql
+ 02-setup-data.sql
...
...
...
WRN 2022-01-08 12:57:25Z Transaction has been committed before the end of the session. Please verify if all schema migrations has been successfully applied. If there was fault in the process, the database changes during migration process will be rolled back.
INF 2022-01-08 12:57:25Z Schema migration completed successfuly on c:\temp\yuniql\samples\basic-oracle-sample.
Verify applied migration versions with yuniql list
yuniql list
+---------------+----------------------+------------+---------------+---------------------+--------------+
| SchemaVersion | AppliedOnUtc | Status | AppliedByUser | AppliedByTool | Duration |
+---------------+----------------------+------------+---------------+---------------------+--------------+
| v0.00 | 2022-01-08 12:57:24Z | Successful | SYS | yuniql-cli v1.0.0.0 | 358 ms / 0 s |
| v0.01 | 2022-01-08 12:57:24Z | Successful | SYS | yuniql-cli v1.0.0.0 | 135 ms / 0 s |
| v0.02 | 2022-01-08 12:57:25Z | Successful | SYS | yuniql-cli v1.0.0.0 | 379 ms / 0 s |
+---------------+----------------------+------------+---------------+---------------------+--------------+
INF 2022-01-08 12:58:48Z Listed all schema versions applied to database on c:\temp\yuniql\samples\basic-oracle-sample workspace.
For platforms not supporting full transactional DDL operations (ex. MySql, Snowflake, CockroachDB), unsuccessful migrations will show the status as Failed and you can look for FailedScriptPath and FailedScriptError in the schema version tracking table.
Verify results with your preferred Oracle Client. Here we show the results from Oracle SQL Developer. Open in new window/tab for better visibility.
Erase and clean your database with yuniql erase. Here, yuniql probes and executes all scripts inside _erase directory. Developer needs to handcraft the appropriate cleanup scripts when erasing database.
WRN 2022-01-08 12:55:57Z Target platform does not support for full transactional DDL operations.
All operations will be executed with transaction mode = statement. When transaction mode is set to statement, each batch of sql statement does not participate in a shared transaction context.
In the event of failure, the rollback attempt is limited to the individual batch of statement.
INF 2022-01-08 12:55:57Z Found 1 script files on c:\temp\yuniql\samples\basic-oracle-sample\_erase
+ 01-remove-tables.sql
INF 2022-01-08 12:55:59Z Executed script file c:\temp\yuniql\samples\basic-oracle-sample\_erase\01-remove-tables.sql.
INF 2022-01-08 12:55:59Z Executed script files on c:\temp\yuniql\samples\basic-oracle-sample\_erase
INF 2022-01-08 12:55:59Z Schema erase completed successfuly on c:\temp\yuniql\samples\basic-oracle-sample.
Known Issues and limitations
- Bulk files are converted into multi-value insert and only supports string or varchar data types. If you need to support other data types you may create a script to move data from staging table into your final destination table. As alternative, you may opt out from using bulk files and instead prepare a seed script with values to load.
- While this is tested on a Oracle version that supports multi-tenant architecture, pluggable or container database, it was not specifically tested to make use of such feature during testing. We hope to improve this release further with more feedback for our users. Please send your comments and suggestions by creating an issue ticket.
Found bugs?
Help us improve further please create an issue.