Work with the Data Manager

Use the Data Manager to manage the data stored in your application during the development, staging and live phases.

To open the Data Manager, click the Options menu on the Business Objects page and select Data Manager.

Manage Data During the Development Lifecycle

Oracle Visual Builder provides tools to help you migrate data between your databases and to import and export data.

When developing your application you might have three versions of your application, each in a different status: development, staging, live. Each version uses an independent database that is used for that phase of development, and during the development lifecycle you need to manage the data that is stored in each database. To populate your databases you can add data manually, migrate data between the development, staging and live databases, or import data from files. See Export the Data in Your Database and Import Data From a File.

Each database uses a schema to describe the fields of the business objects. In the development phase, the schema of your development database is modified as you modify the business objects in the application. The development database schema replaces the schema of the staging database when you stage the application, and the staging database schema replaces the schema of the live database when you publish the application.

Note:

You cannot use the Data Manager to manage the data for business objects for external services. The definitions for business objects provided by external services are stored in your database schema, but the data from the service is not stored in your database.

The following table describes the data typically stored in the database for each phase of the development lifecycle and the data management tasks performed during the phase.

Phase Description
Development

Your development database will typically only contain some basic data to help you while you build your pages. To check the behavior of the application, you might add some sample data manually by using the forms you created in your application or by editing the data in the Data tab.

You will typically perform the following tasks with the data in your development database:

  • Manually add and modify data in Live mode using the forms in your application

  • Import sample data from a file

Staging

Your staging database will typically contain a set of data that is as realistic as possible to be used when testing the staged version of the application. You can add data manually using your application’s user interface or import data from a file or database.

When you are ready to publish your application, if the database schema has changed since the previous version, you will want to import the data from the live database into your staging database. This data is then copied to the live database when you publish the application.

You will typically perform the following tasks in your staging database:

  • Add and modify data to test the application’s UI behavior and business logic

  • Import data from a file to test that the data and schema are compatible

  • Import data from the live database

Live

Usually you will not want to modify the data in your live database except as part of the publishing process if the data is copied from the staging database.

If it is necessary to modify your data because you changed the structure of your application, it is important to make and test the changes on your staging database before you publish the application.

During this stage you will typically perform the following tasks:

  • Export the data as a file or copy the data to the staging database using the Data Manager

  • Lock the application to prevent changes to the data

  • Unlock the application to enable changes to the data

Import Data From a File

You can replace the data in one or more of your custom business objects by importing CSV files and Excel spreadsheets. Use the Import from File tool in the Data Manager to simultaneously update the data for one or more business objects, for example, to import data for testing the application or in preparation for publishing the application.

To import data from a CSV file, you will need one CSV file for each of the business objects that you want to update, and the name of the CSV file must be the same as the business object. You can upload CSV files individually or upload a zip archive that contains multiple CSV files.

When importing an Excel spreadsheet (.xls or .xlsx), the spreadsheet can contain one or more sheets. The title of each sheet must be the same as the name of the business object that you want to update. If the data in a cell is calculated using a formula, only the data is imported. The formula is not imported.

When you use the import tool to replace the data in a business object, all the data for that business object is deleted from the database. If a field is defined in the schema for the object but no data for the field is contained in the file, the field is set to the default value, if there is one.
Description of bo-datamgr-csv.png follows
Description of the illustration bo-datamgr-csv.png

Each business object has five default fields: id, creationDate, lastUpdateDate, createdBy, and lastUpdatedBy.

The correct format for a Date field is yyyy-mm-dd (for example, 2006–06–17). If you edit a .csv file in Excel, Excel converts it to an incorrect format, as shown in the image above. To resolve this problem, you can use Format Cells in Excel to change the date format for the column; you need to specify a locale that supports yyyy-mm-dd, such as English (United Kingdom). Alternatively, edit the file in a text editor.

The import tool will not create or remove fields for business objects. You can use the Business Objects tabs to create or remove fields, to edit data, and to append data to a business object by importing a file.

To import data into a database:
  1. Click the Options menu on the Business Objects page and select Data Manager.
  2. Select the database that you want to update.
  3. Click Import from File.
  4. Drag the file into the Import Data dialog box.
    Description of bo-datamgr-import.png follows
    Description of the illustration bo-datamgr-import.png
    Alternatively, click and locate the file on your local system.
  5. Click Import.
When you import the file, you will see a success message if the import is successful. If there are any problems importing the data, you will see a message that describes the problem. For example, the message might list fields that were not imported because the fields were not defined in the schema. You can try to resolve the problem by comparing the fields in the schema to the columns in the CSV or spreadsheet file and either modifying the schema in the Fields tab or modifying the data in the file. Import the file again to correct the data.

Import Data From a Database

You can import data by using the import tool to copy data from one database to another.

Each phase in the development lifecycle of your application uses an independent database for storing data. You can use the import tools in the Data Manager to import data from one database into another, for example, to import the data in your live database into your staging database.

To import data into a database:
  1. Click the Options menu on the Business Objects page and select Data Manager.
  2. Select the database that you want to update in the drop-down list at the top of the page.
    Description of bo-datamgr-seldb.png follows
    Description of the illustration bo-datamgr-seldb.png
    Unavailable databases are grayed out. After you select the database, the page will display the import and export tools for the database.
  3. Click the tile to import data into your database.
    The page contains several import options. Unavailable options are grayed out.
  4. Click Import in the Import Data dialog.
    All the data in the target database is deleted and replaced when you import data. When the task is complete, a dialog opens that confirms that the data was successfully imported or warns you that there was a problem.

Export the Data in Your Database

You can export all the data contained in your database as CSV files. The export tool creates one CSV file for each of the custom business objects in your database and packages the files as a ZIP archive.

To export the database data as a CSV file:
  1. Click the Options menu on the Business Objects page and select Data Manager.
  2. Select the database that you want to export from the drop-down list.
  3. Click the Export All Data tile to download a ZIP archive that contains CSV files with the data.
Alternatively, you can export the data contained in an individual custom business object in the Data tab for the business object.

Resolve Problems When Importing Data

When you import data from a file, if you see a warning message that the data was not imported or only partially imported, you might need to make changes to the file and import the file again.

To resolve problems during import, you might want to compare the data in your file to the data in the database. You can see the actual data in the database in the Data tab of your business object. You can also export the current data as a CSV file and compare the data using a tool on your local system.

When you import a CSV file with a Date field, dates must be in the standard ISO format, for example, 2017-09-31.

When you see a warning message:

  1. Confirm that the name of the file or Excel workbook is the same as the name of the business object.
  2. Compare the columns in the files to the fields in the business objects.
    Importing a file will not create fields in the database schema. Columns in the file are ignored if a field with that name does not exist in the business object. The import tool expects the data in the first row of the CSV file or Excel workbook to be the name of the field.
  3. Confirm that the format and type of the data in the file are the same as those specified in the schema.

Import Data from the Command Line

You can perform bulk import and export of data from the command line using Oracle Visual Builder APIs.

The Oracle Visual Builder APIs are accessible from the command line to an application’s team members using basic authentication, just as the data APIs can be used to query individual objects and perform single-row operations. For example, you can set up a cron job to import and export data to ensure that the data in two tables are synchronized.

To import data in a business object from the command line, you will need to upload .csv, .xls, or .xlsx files containing the data. The name of the file (for .csv files) or the workbook name (for .xls and .xlsx files) must match the object ID of the business object that you want to update. To import data for a single business object, you can upload a single file. When importing data for multiple business objects, you will need to upload a ZIP archive containing one or more .csv, .xls, or .xlsx files.

To import the development schema for version 1.0 of an application named MyApp, you would use a POST method with the following endpoint to update multiple business objects.

POST https://host:port/design/MyApp/1.0/resources/datamgr/import

For the query parameter filename, you will need to specify the name of your ZIP archive.

To import data for a business object named MyObject in version 1.0 of an application named MyApp, you would use a POST method with the following endpoint.

POST https://host:port/design/MyApp/1.0/resources/datamgr/import/MyObject

For the query parameter filename, you will need to specify the name of your file. Additionally, you can use the Boolean query parameter append, which, when explicitly set to true, will add the rows in your file as new rows in your business object. The default value is false, which results in the data in your file replacing the existing data in the business object.

Export Data from the Command Line

To export the development data for version 1.0 of an application named MyApp, you would use a GET method with the following endpoint to get the data in all the business objects:

GET https://host:port/design/MyApp/1.0/resources/datamgr/export

The result of exporting the data for an application is a ZIP archive containing a .csv file for each business object in the application.

To export development data for the business object MyObject in version 1.0 of an application named MyApp, you would use a GET method with the following endpoint:

GET https://host:port/design/MyApp/1.0/resources/datamgr/export/myObject

The result of exporting the data in a single business object is a .csv file containing the data in the business object you specified.

Note:

To import or export data from the staging or live database, replace /design/ with /deployment/ in the path of your endpoint.

If your application is live, use the options menu to lock the application before you import or export data. Unlock the application after you finish.

Create Business Objects From a File

You can create new business objects by importing spreadsheet files and .csv files using the Import New Business Objects wizard.

The files that you upload are analyzed to determine the business objects that can be created. You may upload comma-separated value text files (.csv) or Excel spreadsheets (.xls, .xlsx). When using .csv files to create business objects, one business object is created for each file, and the name of the business object is based on the file name. When using .xls or .xlsx files to create business objects, one business object is created for each worksheet in the file, and the name of the business object is based on the worksheet name. If the worksheet contains one or more tables, a business object is created for each table based on the table name, and the worksheet name is ignored. The first row of .csv files, worksheets, and tables must be a header row, and the column headers are used to determine the names of the fields. The data in each column is parsed to help determine the data type for the field, but you should confirm the suggested data type is correct in the Fields step of the wizard.

To upload multiple files, you need to create a ZIP archive containing the files you want to import.

To open the Import New Business Objects wizard:

  1. Click the Options menu on the Business Objects page and select Data Manager .

  2. On the Data Manager page, click Import Business Objects.

To create business objects in the Import New Business Objects wizard:

  1. Open the Import New Business Objects wizard.
  2. In the Upload File step of the wizard, drag the file from your local system into the wizard, or click in the upload box and locate the file on your local system.

    After the upload is complete, the wizard displays a list of the business objects and records found in the upload.
    Description of bo-datamgr-import-new.png follows
    Description of the illustration bo-datamgr-import-new.png

    Click Next.

  3. In the Business Objects step of the wizard, select the business objects that you want to create.

    The wizard displays a list of the business objects that can be created and the files in your upload that they are based on. You can select which business objects you want to create, and edit the names and object IDs of the new business objects.
    Description of bo-datamgr-import-new-bo.png follows
    Description of the illustration bo-datamgr-import-new-bo.png

    Click Next.

  4. In the Fields step of the wizard, click the business object name to edit the names and types for each of the fields in the business object.

    The wizard displays tabs for each new business object. Each tab displays the fields that will be created in the business object, and a sample of the values stored in the field. For each business object you can edit the ID, display label, and data type of the fields, and specify whether they are required.
    Description of bo-datamgr-import-new-fields.png follows
    Description of the illustration bo-datamgr-import-new-fields.png

    You can click the Data Type icon for each field to open a dialog box where you can modify the type. For Reference fields, you can select the related business object from an existing business object or from those that you are importing, and select the field in the related object to display.
    Description of bo-datamgr-import-new-type.png follows
    Description of the illustration bo-datamgr-import-new-type.png

    Click Finish.

The wizard displays a list of the new business objects that were successfully imported.
Description of bo-datamgr-import-new-done.png follows
Description of the illustration bo-datamgr-import-new-done.png