Importing Data

Several methods are available for uploading data in to the database. For an initial data upload to Oracle Database Cloud Service (Database Schema), use Oracle SQL Developer, the Oracle Application Express SQL Workshop Data Upload Utility or the Oracle Application Express Data Load utility.

Tutorial iconTutorial

Topics:

The following sections describe the various methods used to load your Oracle Database Cloud Service (Schema) with data.

See Also:

For instructions on using RESTful Services to access data, see Implementing RESTful Web Services.

Using Oracle SQL Developer for Data Loading

You can use Oracle SQL Developer to load data from an on-premise database to Oracle Database Cloud Service (Database Schema).

Topics:

Overview of the Oracle SQL Developer Data Loading Process

Oracle SQL Developer, along with your Oracle Database Cloud Service (Database Schema) SFTP server, provides the ability to upload data to your Oracle Database Cloud Service (Schema). Oracle SQL Developer creates a cart containing objects you want to load in to your Oracle Database Cloud Service (Schema), connects to your service and deploys data from the cart to the service.

To deploy objects to your service you must perform the following steps:

  1. Create or use an existing Oracle Database Cloud Service (Schema). See Creating or Using an Existing Oracle Database Cloud Service for Data Loading.
  2. Install SQL Developer locally. This application creates the cart of objects to load and to perform the deploying of data from the cart to your service. See Installing Oracle SQL Developer.
  3. Add a Cloud connection from Oracle SQL Developer to your service. See Configuring Oracle SQL Developer Cloud Connection.
  4. Reset the SFTP account user password. See Setting Up Secure FTP Account.
  5. Using Oracle SQL Developer, create a cart filled with objects and deploy to your service. See Creating and Deploying a Cart of Objects.
  6. Configure the Oracle Database Cloud Service (Schema) to allow access to the service from additional Oracle SQL Developer users. See Creating Additional Users for Oracle SQL Developer Cloud Connections.

Creating or Using an Existing Oracle Database Cloud Service for Data Loading

The first step in the data loading process is to locate the Welcome to Oracle Cloud email received during the Oracle Database Cloud Service (Database Schema) creation process.

If the Oracle Database Cloud Service (Schema) does not yet exist, see Requesting a Trial Subscription to an Oracle Cloud Service and Purchasing a Subscription to an Oracle Cloud Service in Getting Started with Oracle Cloud.

During the service creation process and after your service has been activated, a Welcome to Oracle Cloud email is sent. This email contains important usernames and URLs required for configuring Oracle SQL Developer and the Secure FTP site for data loading. See Configuring Oracle SQL Developer Cloud Connection and Setting Up Secure FTP Account.

Note:

Save the Welcome to Oracle Cloud email for the Oracle Database Cloud Service (Schema) you are loading with data. This email contains information required for the data loading configuration process.

Installing Oracle SQL Developer

Oracle SQL Developer creates carts of data structures, DDLs, and data to deploy to theOracle Database Cloud Service (Database Schema). This section describes how to install Oracle SQL Developer.

Note:

The functionality described here requires the Oracle SQL Developer Release 3.2 or later.

To install Oracle SQL Developer locally, see Oracle SQL Developer documentation on the Oracle Technology Network, for installation and setup instructions. http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Note:

If your computer is 64 bit, you may need to run the sqldeveloper.bat file instead of sqldeveloper.exe.

Note:

You must install JDE run time if you do not have JDE run time installed. For installation and setup instructions, see Oracle SQL Developer documentation on the Oracle Technology Network at: http://www.oracle.com/technetwork/developer-tools/sql-developer/downloads/index.html

Configuring Oracle SQL Developer Cloud Connection

Oracle SQL Developer must have an Oracle Cloud connection configured to connect to Oracle Database Cloud Service (Database Schema). The connection can use the default username and password provided for Oracle Database Cloud Service (Schema).

Additional users can access the service through Oracle SQL Developer if they are given the Developer role. See Managing User Accounts and Managing the Roles Assigned to a User in Getting Started with Oracle Cloud.

Oracle SQL Developer users can also be added through Oracle Application Express using the steps outlined in Creating Additional Users for Oracle SQL Developer Cloud Connections.

To add an Oracle Cloud connection:

  1. Run Oracle SQL Developer locally.

    The Oracle SQL Developer home page displays.

  2. Under Connections, right click Cloud Connections.

    The Cloud Connection menu appears.

  3. Select New Cloud Connection.

    The Edit Cloud Connection dialog appears.

  4. From the Oracle Cloud home page, click Sign In.

    The Sign In page appears.

  5. In the My Services box, select the data center and click Sign In to My Services.
  6. If not already signed in, the Sign in dialog displays. Enter Oracle Database Cloud Service (Schema) credentials and click Sign In.

    My Services appears.

  7. Click Platform Services.
  8. Click the name of Oracle Database Cloud Service (Schema).

    The Service Detail page displays.

  9. Under Additional Information, locate the Service SFTP User Name. Make a note of this name for use in a later step.
  10. Click Security.

    The Security page displays.

  11. Click SFTP Users.

    The tab lists the SFTP user accounts for the current identity domain and your services in that domain.

  12. Find the Service SFTP User Name you located in the Service Detail page, click the Menu icon.
  13. Select Reset Password.

    The Reset Password dialog appears.

  14. Enter a new password, confirm the new password and click Save.

    The Reset Password confirmation message displays.

  15. To confirm change, click Save.
  16. Go back to the Service Detail page and locate the following information required to configure the new cloud connection:
    • Service Instance URL - The Oracle Database Cloud Service (Schema) URL.

    • Service SFTP Host & Port - The SFTP host address.

    • Service SFTP User Name - The SFTP user for this Oracle Database Cloud Service (Schema).

  17. On the Edit Cloud Connection dialog of Oracle SQL Developer, make the following entries:
    • Connection Name - Enter the name for this cloud connection.

    Database

    • Username - Enter the Username required during sign in when accessing the Oracle Database Cloud Service (Schema).

    • URL - Enter the Service Instance URL from the Service Detail page.

    SFTP

    • Username - Enter the Service SFTP User Name from the Service Detail page.

    • Hostname - Enter the Service SFTP URL from the Service Detail page.

    • Port - Enter 22.

    Your Edit Cloud Connection dialog should look something like this:

  18. Click OK.
  19. Under Cloud Connections on Oracle SQL Developer, open the new cloud connection.

    The Authentication dialog appears.

  20. Enter the Password required during sign in when accessing Oracle Database Cloud Service (Schema).

    If you have connected successfully, the tables and other objects from Oracle Database Cloud Service (Schema) display under Cloud Connections.

Setting Up Secure FTP Account

The Secure FTP user password must be reset before you can deploy a data upload. To reset the Secure FTP user password, sign in to My Services, find the SFTP user name, and reset the password.

Note:

The Domain SFTP user will not see the data upload files.

To reset the password for the Secure FTP user account:

  1. From the Oracle Cloud home page, click Sign In.

    The Sign In page appears.

  2. In the My Services box, select the data center and click Sign In to My Services.
  3. If not already signed in, the Sign in dialog displays. Enter Oracle Database Cloud Service (Schema) credentials and click Sign In.

    My Services appears.

  4. Click Security.

    The Security page displays.

  5. Click SFTP Users.

    The list of SFTP Users appears.

  6. Locate the Secure FTP user and click the Menu icon to the right.
  7. Select Reset Password.

    The Reset Password dialog appears.

  8. Enter a new password, confirm the new password and click Save.

    The Reset Password confirmation message displays.

  9. To confirm change, click Save.

Creating and Deploying a Cart of Objects

Use Oracle SQL Developer to create a cart of objects and deploy the cart to a destination database.

To create and deploy a cart of objects to Oracle Database Cloud Service (Database Schema):

  1. From Oracle SQL Developer, click View.

    The View drop down menu displays.

  2. From the drop down menu, click Cart.

    The Cart window appears on the bottom right.

  3. From the left panel, drag the Oracle Database objects you want to load and drop them in to the Cart window on the right.
  4. If you want to include data with the cart deployment, in the Cart window, click the Data check box.
  5. To deploy this cart, click the Deploy Cloud icon at the top left of the Cart window.

    The Deploy Objects To Cloud dialog displays.

  6. For the Deploy Objects To Cloud dialog, make these changes:

    Under Cloud:

    These options supply information required to connect to the Secure FTP server.

    • Connection - Select the cloud connection for the Oracle Database Cloud Service (Schema) you are deploying to.

    • Title - Enter a title for this deployment. The title is restricted to 15 characters long and alphanumeric characters (a-z, A-Z, 0-9), _ (underscore) and - (dash). No special characters, such as spaces, are allowed.

    • Server - Enter the Secure FTP URL from the Welcome to Oracle Cloud email.

    • Port - Leave as default of 22.

    • SFTP User - Enter the Secure FTP username. See Setting Up Secure FTP Account.

    • SFTP Password - Enter the Secure FTP user's password. See Setting Up Secure FTP Account.

    Under Transfer:

    This option specifies the cart that is deployed.

    • File - Enter or browse for the file you want to deploy. This file is a zip file generated by SQL Developer and contains all objects and optionally data included in the cart.

    Under Deploy DDL:

    These options determine if the DDL will replace existing objects.

    • Do not replace existing destination objects - Creates objects in the destination schema only if objects do not already exist.

    • Replace existing destination objects - Drops existing objects in destination schema if they already exist then creates and optionally loads them.

    Under Deploy Data:

    These options determine how the data is deployed to the destination Oracle Database Cloud Service (Schema).

    • Truncate destination data - Select this option to truncate existing tables before adding rows.

    • Disable constraints before moving data - Select this option to disable all constraints before moving the data. Re-enables the constraints after the data has been moved.

    • Delimiter - Leave as the default.

    • Line Terminator - Leave as the default.

    • Left Enclosure - Leave as the default.

    • Right Enclosure - Leave as the default.

  7. Click Apply.

    The Exporting dialog displays.

Checking Deployment Status

Use Oracle SQL Developer to determine deployment status.

To check on deployment status:

  1. From Oracle SQL Developer, under Cloud Connections, open a cloud connection.
  2. Under the open cloud connection on left panel, click Deployments.

    The list of deployments displays.

  3. Under Deployments, click a deployment.

    The deployment DETAILS tab displays at the top right.

  4. The status value on the DETAILS page indicates the progress of the deployment.
    • APPROVED - The deployment passed a virus scan and is waiting for processing.

    • PROCESSING - A background daemon process that is scheduled to run periodically, found the APPROVED deployment, changed the status to PROCESSING and started the SQL*loader jobs.

    • PROCESSED - The deployment completed successfully.

    • DENIED - The deployment is not approved.

  5. To view deployment results, click LOGS.
  6. To see the latest uploaded tables, on the Connections panel, click the Refresh icon.

    Note:

    If the Refresh button is not available, the cloud connection is not connected.

  7. From the Oracle SQL Workshop Object browser for your Oracle Database Cloud Service (Schema), view the progress of the data upload and verify that objects and optionally data has uploaded correctly.
Oracle SQL Developer for Data Loading in to the Oracle Database Cloud Service (Schema) uses the database utility SQL*Loader to perform the data load. To find out more about restrictions on data loading, see the Oracle Database Utilities book.

Restarting a Deployment

Use Oracle SQL Developer to restart deployments of data exports.

To restart a deployment:

  1. From Oracle SQL Developer, click Cloud Connections.
  2. Click the cloud connection.
  3. Click Deployments.

    The list of deployments displays.

  4. Right click the deployment.
  5. From the menu options, select Restart.
  6. Follow on-screen instructions.

Removing a Deployment

Remove a deployment from Oracle SQL Developer.

To remove a deployment:

  1. From Oracle SQL Developer, click Cloud Connections.
  2. Click the cloud connection.
  3. Click Deployments.

    The list of deployments displays.

  4. Right click the deployment.
  5. From the menu options, select Delete.
  6. Follow on-screen instructions.

Clearing Logs

Use Oracle SQL Developer to clear deployment logs.

To clear the log for a deployment:

  1. From Oracle SQL Developer, click Cloud Connections.
  2. Click the cloud connection.
  3. Click Deployments.

    The list of deployments displays.

  4. Right click the deployment.
  5. From the menu options, select Clear Logs.
  6. Follow on-screen instructions.

Creating Additional Users for Oracle SQL Developer Cloud Connections

Oracle SQL Developer users can also be added through Oracle Application Express.

To add an Oracle Application Express user:

  1. Access Oracle Database Cloud Service (Schema). See Accessing Oracle Database Cloud Service (Database Schema).

    The Oracle Application Express home page appears.

  2. From the Oracle Application Express Builder, click Administration.

    The Administration home page displays.

  3. Click Manage Users and Groups.

    The Manage Users and Groups page appears.

  4. Click Create User.

    The Create User page appears.

  5. Enter user information. See Managing Application Express Users in the Oracle Application Express Administration Guide.
  6. Under Password, for Require Change of Password on First User select No.
  7. Under User Groups (For authentication against Application Express user account repository only), select at least SQL Developer.
  8. Click Create User.

Using Oracle Application Express SQL Workshop Data Upload Utility

The Data Upload utility enables you to upload data from a text file, an XML document or a spreadsheet. The utility is accessed from the Oracle Application Express SQL Workshop.

To upload data using the Data Upload utility:

  1. Access Oracle Database Cloud Service (Schema). See Accessing Oracle Database Cloud Service (Database Schema).

    The Oracle Application Express home page displays.

  2. Click SQL Workshop.
  3. Click Utilities.
  4. Click Data Workshop.
  5. Click one of the following types of data:
    • Text Data

    • XML Data

    • Spreadsheet Data

  6. Follow on-screen instructions.

To learn more about uploading data with the Data Upload utility, see Using the Data Workshop to Manage Data in Oracle Application Express SQL Workshop Guide.

Uploading Data with SQL Scripts

The SQL Scripts functionality allows you to create, upload, and run SQL scripts that insert data into the database. You could use an SQL script to load data into your Oracle Database Cloud Service (Database Schema).

These operations are accessed from the Oracle Application Express SQL Workshop.

To insert data using SQL Scripts:

  1. Launch the Oracle Database Cloud Service (Schema). See Accessing Oracle Database Cloud Service (Database Schema).
  2. Click SQL Workshop.
  3. Click SQL Scripts.
  4. Create or upload an SQL script.

    To learn more, see "Creating a SQL Script" in the Oracle Application Express SQL Workshop Guide.

  5. Click the Run icon for the SQL script you want to execute.

    To learn more about running SQL scripts, see "Executing a SQL Script" in the Oracle Application Express SQL Workshop Guide.

To learn more about using SQL scripts to load data, see "Using SQL Scripts" in the Oracle Application Express SQL Workshop Guide.

Using Oracle Application Express Application Data Load Utility

Applications with data loading capability allow application end users to dynamically import data in to a table within any schema to which the user has access. End users run a Data Load Wizard that uploads data from a file or copies and pastes data entered by the end user directly in to the wizard.

To learn more about developing Oracle Application Express applications with this capability, see Creating Applications with Data Loading Capability in the Oracle Application Express Application Builder User's Guide.

To learn more about how an application end user uses this capability, see About Uploading Data in Oracle Application Express End User Guide.