Connect to the Database
A direct connection to the Unifier Essentials database using SQL Developer is not possible. The creation of SQL queries must be done in Data Views, or by pulling rows down from Unifier Essentials into a local database.
If you must pull the data into a local database for the purpose of SQL development, Oracle offers Oracle Database 11g Express Edition (XE) free of charge. You can install this lightweight DB on your computer and use it for development purposes.
Download Oracle Database Express Edition (XE). Refer to the XE documentation for instructions on how to install the software and create a local database.
(Optional) Pulling Down the Data
After you know the table names, you can export 200 lines so it can be inserted into your local Database (DB).
To pull down the data:
- Sign in as a Company Administrator.
- Go to the Company Workspace tab and switch to Admin mode.
- In the left Navigator, click Data Structure Setup > Data Views.
- Click New to create a Data View.
- Enter a name (for example, Export DV) and a Label (for example, Export Data View).
- In the SQL Definition field, enter the following for a particular table:
SELECT * FROM <tablename>
- Click OK.
- Click the Status drop-down list and set the newly created Data View to Published.
- Highlight the Data View you just created and click Data on the toolbar.
- In the window, click Export as SQL on the toolbar to save the .sql file locally. This SQL file includes the CREATE and INSERT commands required to get your data into your local XE database.
At this point, you can create the table and insert the exported rows into your local XE database using tools like SQL Developer.
Repeat the steps above for any tables that you like to access offline.
Note: You can reuse the same Data View multiple times; however, you must perform a find and replace in the downloaded SQL to ensure that the table names match the names that are in Unifier Essentials.
Because you have a subset of the database locally, you can use SQL Developer to write complex queries, offline, before bringing them into Unifier Essentials.
Last Published Tuesday, June 4, 2024