Import Data

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

Topics:

• Use Oracle SQL Developer for Data Loading

• Use Oracle Application Express SQL Workshop Data Upload Utility

• Use Oracle Application Express Application Data Load Utility

The following sections describe the various methods used to load your Schema Service with data.

See Also:

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

Use Oracle SQL Developer for Data Loading

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

Topics:

• Overview of the Oracle SQL Developer Data Loading Process

• Create or Use an Existing Oracle Database Cloud Service for Data Loading

• Install Oracle SQL Developer

• Configure Oracle SQL Developer Cloud Connection

• Set Up Secure FTP Account

• Create and Deploy a Cart of Objects

• Check Deployment Status

• Restart a Deployment

• Remove a Deployment

• Clear Logs

• Create Additional Users for Oracle SQL Developer Cloud Connections

Overview of the Oracle SQL Developer Data Loading Process

Oracle SQL Developer, along with your Oracle Database Cloud Schema Service SFTP server, provides the ability to upload data to your Schema Service. Oracle SQL Developer creates a cart containing objects you want to load in to your Schema Service, 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 Schema Service. 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 Schema Service to allow access to the service from additional Oracle SQL Developer users. See Creating Additional Users for Oracle SQL Developer Cloud Connections.

Create or Use 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 Schema Service creation process.

If the Schema Service does not yet exist, see Request and Manage Free Oracle Cloud Promotions and Buy an Oracle Cloud Subscription 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 Schema Service you are loading with data. This email contains information required for the data loading configuration process.

Install Oracle SQL Developer

Oracle SQL Developer creates carts of data structures, DDLs, and data to deploy to theOracle Database Cloud Schema Service. 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

Configure Oracle SQL Developer Cloud Connection

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

Additional users can access the service through Oracle SQL Developer if they are given the Developer role. See Managing User Accounts in Managing and Monitoring Oracle Cloud and Managing User Roles in Managing and Monitoring 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.

   
   Description of the illustration sql_dev.gif
2. Under Connections, right click Cloud Connections.

   The Cloud Connection menu appears.

   
   Description of the illustration sl_dev_connect.gif
3. Select New Cloud Connection.

   The New Cloud Connection dialog appears.

   
   Description of the illustration new_cloud_conn.jpg
4. Navigate to the Service Details page for your Schema Service. This example uses a service purchased before August 2017, see Access Service Details.
5. Under Additional Information, locate the Service SFTP User Name. Make a note of this name for use in a later step.
   
   

   
   Description of the illustration additional_info_1.png

   
   
6. Click Users.

   The Users page displays.

7. Click SFTP Users.

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

8. Find the Service SFTP User Name you located in the Service Detail page, click the Menu icon.
   
   Description of the illustration sftp_users_1.png
9. Select Reset Password.

   The Reset Password dialog appears.

   
   Description of the illustration reset_password.gif
10. Enter a new password, confirm the new password and click Save.

    The Reset Password confirmation message displays.

11. To confirm change, click Save.
12. Go back to the Service Detail page and locate the following information required to configure the new cloud connection:

    • Service Instance URL - The Schema Service URL.

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

    • Service SFTP User Name - The SFTP user for this Schema Service.

13. 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 Schema Service.

    • 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 host portion of the Service SFTP Host & Port from the Service Detail page.

    • Port - Enter 22.

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

    The Authentication dialog appears.

16. Enter the Password required during sign in when accessing Schema Service.

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

    
    Description of the illustration sqldev_tbls.gif

Set 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 your account, navigate to the User Management page, 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. Sign in to your account. See Sign In To Your Account.
   The Infrastructure Console displays.

   
   

   
   Description of the illustration infra_console_1.png

   
   
2. Open the navigation menu. Under Account Management, click Users.

   The User Management page appears.

3. Click SFTP Users.

   The list of SFTP Users appears.

4. Locate the Secure FTP user and click the Menu icon to the right.
   
   Description of the illustration sftp_users_1.png
5. Select Reset Password.

   The Reset Password dialog appears.

   
   Description of the illustration reset_password.gif
6. Enter a new password, confirm the new password and click Save.

   The Reset Password confirmation message displays.

7. To confirm change, click Save.

Create and Deploy a Cart of Objects

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

Note:

If you want to build an automated process to upload one or more carts of data from an on-premises Oracle Database to Oracle Database Cloud Schema Service, you can use Oracle SQL Developer command-line interface, also known as SDCLI.

To create and deploy a cart of objects to Schema Service:

1. From Oracle SQL Developer, click View.

   The View drop down menu displays.

   
   Description of the illustration view_cart.gif
2. From the drop down menu, click Cart.

   The Cart window appears on the bottom right.

   
   Description of the illustration empty_cart.gif
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.
   
   Description of the illustration full_cart.gif
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.
   
   Description of the illustration deploy_icon.gif

   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 Schema Service 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 Schema Service.

   • 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.

   
   Description of the illustration deployment.gif
7. Click Apply.

   The Exporting dialog displays.

   
   Description of the illustration exporting.gif

Check 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.

   
   

   
   Description of the illustration approved_1.png

   
   
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.

   The LOGS tab displays.

   
   Description of the illustration logs.gif
6. To see the latest uploaded tables, on the Connections panel, click the Refresh icon.
   
   Description of the illustration refresh.gif

   Note:

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

7. From the Oracle SQL Workshop Object browser for your Schema Service, 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 Schema Service uses the database utility SQL*Loader to perform the data load. To find out more about restrictions on data loading, see Oracle Database Utilities.

Restart 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.

   Menu options appear.

   
   Description of the illustration restart_logs.gif
5. From the menu options, select Restart.
6. Follow on-screen instructions.

Remove 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.

   Menu options appear.

   
   Description of the illustration delete_logs.gif
5. From the menu options, select Delete.
6. Follow on-screen instructions.

Clear 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.

   Menu options appear.

   
   Description of the illustration clear_logs.gif
5. From the menu options, select Clear Logs.
6. Follow on-screen instructions.

Create 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 Schema Service. See Accessing an Oracle Database Cloud Schema Service.

   The Oracle Application Express home page appears.

2. From the Oracle Application Express home page, click the Administration menu.
   The Administration Menu displays.
3. Select Administration.

   The Administration home page displays.

   
   Description of the illustration admin5_home.jpg
4. Click Manage Users and Groups.

   The Manage Users and Groups page appears.

   
   Description of the illustration manage_usrs5.jpg
5. Click Create User.

   The Create User page appears.

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

Use 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.

Note:

Files uploaded with Oracle Application Express SQL Workshop must not exceed 100MB. For files larger than 100MB, please use Oracle SQL Developer. See Using SQL Developer for Data Loading.

To upload data using the Data Upload utility:

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

   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 Data Workshop in Oracle Application Express SQL Workshop Guide.

Upload 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 Schema Service.

Note:

Files uploaded with Oracle Application Express SQL Workshop must not exceed 100MB. For files larger than 100MB, please use Oracle SQL Developer. See Using SQL Developer for Data Loading.

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

To insert data using SQL Scripts:

1. Launch the Schema Service. See Accessing an Oracle Database Cloud Schema Service.
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 Oracle Application Express SQL Workshop Guide.

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

Use 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 a Data Load Wizard in Oracle Application Express Application Builder User's Guide.

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