Create Oracle Database Connection in Data Virtuality Studio

Data Virtuality provides numerous data source connectors including Oracle database connectors for Data Virtualization platform developers. SQL developers can easily connect to Oracle databases using Data Virtuality Studio and combine data from different data platforms in a single SQL view query for building a corporate data model

In this Data Virtuality tutorial, I will quickly list down the steps for a connection to Oracle database launched as an Amazon RDS for Oracle database within Data Virtuality Studio for SQL developers.

connect Data Virtuality to Oracle database

First of all, launch Data Virtuality Studio and connect to the server with an administrator account

On Data Virtuality Studio click on "Add data source" icon

Oracle data source to connect Amazon RDS for Oracle

As seen in below screenshot, administrator can provide below connection parameter values.
Host is the Amazon RDS for Oracle endpoint which is the server to be connected

Alias:
Host: test-rds-old.cujdg0zxotse.eu-central-1.rds.amazonaws.com
Port: 1521
Service name or SID: Database name (ORCL)
User name: master user (administrator)
Password: master user password
Data source parameters: importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW", importer.importIndexes=TRUE

connection parameters for Amazon RDS for Oracle connection on Data Virtuality

These information can be collected from RDS console
On Connectivity & security tab, the endpoint and port provides the Data Virtuality administrator the target host and port information for the new database connection which is being added as a new Data Source.

Amazon RDS for Oracle endpoint and port

The Oracle database name can also be found on the Configuration tab of the Amazon RDS instance.
The master username is seen as "administrator" on the same tab

Oracle database and master user name

In order to establish the traffic from Data Virtuality to Oracle database Amazon RDS instace, allow incoming connections to RDS instance using Security Group settings. AWS developer will find the related security group name which has to be modified in Connectivity & security tab.

VPC Security Group to allow Data Virtuality connection

Launch the security group by clicking on the link and Add Inbound rule to allow connections from Data Virtuality server IP address or security group using TCP 1521 port.

On Data Virtuality Studio, click on Next button to continue to add Oracle RDS instance as new data source

statistics for Oracle database connection

Click Finish button to see if connection is successfully established

Amazon RDS for Oracle connection

Now, if the Data Virtuality administrator and LDW developers check the new data source, they will see the new Amazon RDS for Oracle database and its tables/views are added and listed

Oracle data source in Data Virtuality Studio

Of course, if the Data Virtuality administrator wants to add the new Oracle database using a script for automation of the tasks, following script can be used


/* Create connection */

call SYSADMIN.createConnection(name => 'ORACLE-Test-RDS', jbossCliTemplateName => 'oracle', connectionOrResourceAdapterProperties => 'host=test-rds-old.cujdg0zxotse.eu-central-1.rds.amazonaws.com,port=1521,db=ORCL,user-name=administrator', encryptedProperties => 'password=+0fexHHsViJCtnMAycXVNQ==');;



/* Create data source */

call SYSADMIN.createDatasource(name => 'ORACLE-Test-RDS', translator => 'oracle', modelProperties => 'importer.useFullSchemaName=FALSE,importer.tableTypes="TABLE,VIEW",importer.importIndexes=TRUE', translatorProperties => '', encryptedModelProperties => '', encryptedTranslatorProperties => '');;

And to remove the Oracle data source from Data Virtuality server, below script is a sample


EXEC "SYSADMIN.removeDataSource"('ORACLE_Test_RDS')

EXEC "SYSADMIN.removeConnection"('ORACLE_Test_RDS')

I hope this Data Virtuality tutorial helps data virtualization developers to connect Oracle databases