Load CSV file into Oracle Database
In this blogpost I will describe how to load data from a CSV file into an Oracle Database with Dell Boomi. This integration runs completely in the local network. No data is processed within the Boomi Cloud.
I use an Oracle XE database with example schemas installed. The product_information table is located in the OE schema. The Atom inside the VM doesn’t have an Oracle JDBC driver installed. So it is necessary to install the required driver (18c) in our environment. The configuration steps include everything needed to achieve this.
- Setup vagrant image with local Atom and Oracle XE database (see previous post for details)
- Login with SSH into the virtual machine
vagrant ssh node1
- Start Atom locally
cd /home/vagrant/Boomi_AtomSphere/Atom/Atom_javadev01_esentri_com/bin ./atom start
Login to AtomSphere in a web browser.
Download Oracle JDBC driver from https://www.oracle.com/database/technologies/appdev/jdbc-ucp-183-downloads.html
Create a new component of Type Connection and select Database as connector.
Select Custom driver type, because we want to connect with service name to the Oracle database:
Class Name: oracle.jdbc.OracleDriver
Password: <secure password>
Connection URL: jdbc:oracle:thin:@<host>:<port>/<service_name>All other options have default values.
Create a new component of Type Profile and select Database as Profile Format. Name the new component Products.
In Options view select Write as Execution Type. Go back to Data Elements and select Dynamic Insert in field Type. Afterwards click Import.
Select the appropriate Atom from the list and select the right database connection in the Connection field. Afterwards click Next. Then select the right database table from the list. In this example select OE.Product_Information.
Select all fields from the table PRODUCT_INFORMATION.
Review the profile configuration and finish the profile creation by clicking on Save and Close.
Create a new Map and choose Profile (Flat File) “Product CSV” as source and Profile “Products” (Database) as target. Map the fields like seen in the following figure:
In this particular example a lookup is needed for the category name. In order to achieve this add a new function to the map and select Lookup from the Category list. Within the Functions area select Sql Lookup.
Define Input and Output parameters for the lookup. Parameters in SQL statement are used with “?” and mapped according to the order defined in Inputs. The same applies to the Output definitions. The mapping of the SQL result is mapped in order defined in the select clause.
At the end the Map should look like the following figure:
The Map can also be represented as an Excel file. This is helpful for documentation requirements and discussions with different stakeholders when developing integrations.
Create a new Process called CSV to Database Importer. This process should include a File Connection to read the CSV file. Then use the previously created Map component and a Database Connection for the Oracle Database. It won’t explain every step in this process because there is nothing special. However I use “Extensions” for some configuration parameters of the used Connectors. It is useful when deploying the process to different environments.
Extension for CSV Import Directory (File Connector). I configured the Directory as Extension for this particular Connection. This configuration allows you to modify the input directory
Extension for Oracle OE Database (Database Connector). I configured all connection settings as Extension for this particular Connection. This configuration allows you to modify all these values during deployment.
Next Step will be to deploy this process to the local Atom runtime. See the next chapter how to achieve this.
Select Connection Settings and choose the appropriate Connection from the dropdown list. Modify the values as needed for your environment. In the example the database connection settings will be prepared to access Oracle XE database.
Now it’s time to test the deployed process. See the next chapter.
Test the process
Now everything is prepared to execute the process. In order to do this add a csv file with test data to the configured input directory. When everything works fine you should see new rows in the table product_information like in the following figure:
In this example I explained how to import CSV data into an Oracle XE database. Some important considerations are JDBC driver configuration to use connection with service name instead of SID. Furthermore this example shows how to use SQL Lookup function in a Map. Finally I described how to prepare and configure connection configuration for different environments by using extensions.
- Blog: Develop synchronous Web service with Dell Boomi
- Database Integration Guide FAQ
- Database Connection Configuration
- Database Common Errors
- Overriding pre-loaded database drivers