Data Loading and the Oracle Database Schema Service

 

Before You Begin

Purpose

This tutorial covers how to load data into an Oracle Database Schema Service using SQL Developer.

Time to Complete

Approximately 20 minutes

Overview

The Oracle Database Cloud is an ideal platform for building data-centric applications universally available in the Cloud. These applications allow users to add, change, and manipulate data to extract valuable information to allow for better operations and strategic decisions.

Some applications will begin with a clean slate – with data structures defined, but without any data populating those structures. The data is added through user interaction.

A more common scenario is an application where some amount of data already exists, although not in an Oracle Database Schema Service. This scenario will require data to be loaded into the Oracle Database Schema Service.

You can load data into your Oracle Database Schema Service with two different tools – Oracle Application Express or SQL Developer.

  • In Oracle Application Express, SQL Workshop contains an area which allows you to run SQL scripts. These scripts can include both SQL statements and embedded data, and can be used to load data into the Oracle Database Schema Service. In addition, Oracle Application Express has a set of utilities that allow you to load data from a variety of formats, including .csv files, Excel spreadsheets, and text files.
  • SQL Developer is a Java-based tool that runs on a client machine. SQL Developer accesses an Oracle Database Schema Service through a set of RESTful Web Service calls and allows you to access and load data and data structures into your Oracle Database Schema Service.
In this tutorial, you learn how to load data into an Oracle Database Schema Service using SQL Developer

What Do You Need?

  • Before starting this tutorial, you should have performed the Signing Up for a Database Schema Service tutorial.
  • To load data into your Oracle Database Schema Service, you will need access to an Oracle database and Oracle SQL Developer release 3.2.10.09 or later.
 

Installing an Application into your Database Schema Service

There are three tasks you need to perform before you can perform the data upload from SQL Developer:

  • Change the password for the SFTP user
  • Create a cloud connection user in your Database Schema Service
  • Create a cloud connection in SQL Developer
 

Change the Password for the SFTP User

You need to change the password for the SFTP user defined for your Database Schema Service. Perform the following steps:

  1. Sign In to your Database Schema Service. Click Sign In from the main cloud page.

    Description of this image

  2. Make sure the correct Data Center is selected and click Sign In to My Services.

    Description of this image

  3. Enter your Identity Domain value as received in the Oracle Cloud Service activation email and click Go.

    Description of this image

  4. Sign in as the database administrator user for your service and click Sign In.

    Description of this image

  5. In My Services, click the name of your Database Schema Service.

    Description of this image

  6. Make note of the Service SFTP Host & Port and the Service SFTP User Name. You will be using these later to define a connection.
    Note: You want the Service SFTP information for your Database Service, not the Domain SFTP information for your entire identity domain.

    Description of this image

  7. Click the link in the Service SFTP User Name field. This will bring you directly into the Users tab, with only your Service SFTP User listed. On the right side of the screen, click the icon with 4 horizontal bars, and from the pop-up window, click Reset Password.

    Description of this image

  8. Enter a new password, confirm the new password, and click Save. Keep a note of the new password that you entered.

    Description of this image

  9. Click Save. Next, you need to create a a cloud connection user in your Database Schema Service.

    Description of this image
 

Create a Cloud Connection User in Your Database Schema Service

You need to create a user to load the tables and data into your Database Schema Service. Perform the following steps:

  1. In the previous step, you were in the Users tab of My Services. Click the Dashboard tab. Click your database name.

    Description of this image

  2. You are in the Overview tile of the Dashboard tab. Note the URL in the Service Instance URL field. If you hover your mouse over the URL, a pop-up shows the complete URL. Right-click the URL and select Copy link address from the context menu. Then paste the URL into a text file so that you can copy and paste it in the next section.

    Description of this image

  3. Make note of the port number at the end of the Service SFTP Host & Port field. In this example, the port number is 22. This is the URL and Port Number you need to specify in the next section when you create a SQL Developer connection to this Database Schema Service.

    Description of this image

  4. Click Open Service Console.

    Description of this image

  5. Click tools icon from top right corner of Oracle Application Express home page and select Manage Users and Groups.

    Description of this image

  6. Click Create User>.

    Description of this image

  7. Enter the required information (Username, Email Address, Password). Change Require Change of Password on first User to No, and select SQL Developer from the list of User Groups. Then click Create User.

    Description of this image

  8. The user is created successfully. Next, you create a Cloud Connection in SQL Developer.

    Description of this image
 

Create a Cloud Connection in SQL Developer

In this section, you add some tables from another database into your Cart and deploy to the cloud. Perform the following steps:

  1. Open SQL Developer.
    Note: You must have release 3.2.10.09 or later. From the Connections window, Right-click Cloud Connections and select New Cloud Connection...

    Description of this image

  2. Enter the following information and click OK.

    Connection Name Identity Domain of your service
    Database Username Name of user with SQL Developer user group
    Database URL URL to Database Schema Service, which you saw in My Services -> database -> Details -> Overview tab, as shown earlier in the screenshot of Step 2 in the "Create a Cloud Connection User in Your Database Schema Service" section of this OBE
    SFTP Username User from email that you changed password for
    SFTP Hostname Service sftp hostname from email
    SFTP Port Service sftp port which you saw in My Services -> database -> Details -> Overview tab

    Description of this image

  3. The Cloud Connection is created. Click + symbol beside your cloud connection to expand the node.

    Description of this image

  4. Enter the password of the user you created in your database Schema Service and click OK.

    Description of this image

  5. The connection to your Database Schema Service is successful. You are now ready to load data into your Database Schema Service.

    Description of this image
 

Loading Data into your Database Schema Service from SQL Developer.

In this section, you add some tables from another database into your Cart and deploy to the cloud. Perform the following steps:

  1. Select View > Cart.

    Description of this image

  2. Open a connection to the database that contains the data you want to load. Drag the database objects to the cart. In this example, you drag the EMPLOYEES table to the cart. You can drag additional tables into the cart as required.

    Description of this image

  3. In this example, EMPLOYEES,DEPARTMENTS, and JOBS from HR schema are dragged to the cart.

    Description of this image

  4. You can deploy the DDL and/or the Data and also specify a Where clause. Once all the objects you want to load are in the cart, click the Deploy Cloud icon.

    Description of this image

  5. Enter a Title (no spaces) and click Replace existing destination objects for Deploy DDL and click Apply.
    Note that the Deploy Data section is not available because you only only selected the DDL option (from the cart). Click Apply.
    (If an error message appears, check your SFTP User and Password settings. If Port 22 does not work, try 2222.)

    Description of this image

  6. The file deploy.zip will be created and uploaded to the SFTP server and then imported into your database Schema Service.

    Description of this image

  7. Expand Deployments and select the deployment you just created. Review the details. You may need to click the Refresh button. When the status is PROCESSED this means the deployment completed successfully.

    Description of this image

  8. If you review the list of tables now, you see the tables you just deployed in the list.

    Description of this image
 

Summary

In this tutorial, you have learned how to:

  • Set up your Database Schema Service to allow for data uploads from SQL Developer
  • Load data into your Database Schema Service from SQL Developer
 

Want to Learn More?

 

Credits

  • Curriculum Developer: Anjani Pothula

  • Other Contributors:Drue Swadener