Data Loading and the Oracle Database Cloud Service

Overview

    Purpose

    This tutorial covers how to load data into an Oracle Database Cloud Service using SQL Developer, as well as export data from your Database Cloud Service.

    Time to Complete

    Approximately 20 minutes

    Introduction

    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 Cloud Service.  This scenario will require data to be loaded into the Oracle Database Cloud Service. 

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

    • In 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 Cloud Service.  In addition, 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 Cloud Service through a set of RESTful Web Service calls and allows you to access and load data and data structures into your Oracle Database Cloud Service.

    In this tutorial, you learn how to load data into an Oracle Database Cloud Service using SQL Developer, as well as export data from your Database Cloud Service from My Services.

    Prerequisites

    Before starting this tutorial, you should have performed the Signing Up for a Database Cloud Service tutorial.

    To load data into your Oracle Database Cloud Service, you will need access to an Oracle database and Oracle SQL Developer release 3.2.10.09 or later.

Setting Up Your Database Cloud Service to Allow for Data Uploads from SQL Developer

    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

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

      Review the email you received when you created your Database Cloud Service. Identify the sftp user and Secure FTP Site in the email.

      Sign In to your Cloud Service. Click Sign In from the main cloud page.

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

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

      Click Identity Console.

      Select Manage Users.

      Enter * in the search field and click Search.

      Select the SFTP user and click Reset Password.

      Click the Manually change the password option and enter a new password that satisfies the password policy and click Reset Password.

      The password was changed successfully. Next you need to create a a cloud connection user in your database cloud service.

    Create a Cloud Connection User in Your Database Cloud Service

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

      From the My Services window, click the Details icon.

      Note the URL in the Host field and the SFTP Port Number. This is the URL and Port Number you need to specify in the next section
      when you create a connection to your Cloud Service. Click Launch Service.

      Select Administration > Manage Users and Groups.

      Click Create User.

      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.

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

    Create a Cloud Connection in SQL Developer

      You need to create a connection to your Database Cloud Service from SQL Developer. Perform the following steps:

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

      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 Cloud 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 Cloud Service" section of this OBE>

      SFTP Username: <user from email that you changed password for>
      SFTP Hostname: <sftp hostname from email>
      SFTP Port: <sftp port which you saw in My Services -> database -> Details -> Overview tab>

      The Cloud Connection was created. Expand the node to connect.

      Enter the password of the user you created in your database cloud service and click OK.

      The connection to your Database Cloud Service was successful. You are now ready to load data into your Database Cloud Service..

Loading Data into your Database Cloud 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:

    Select View > Cart.

    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 PROJECTS table to the cart. You can drag additional tables into the cart as required.

    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.

    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 2222 does not work, try 22.)

    The file deploy.zip will be created and uploaded to the SFTP server and then executed into your database cloud service.

    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.

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

    You can also switch to your database service and review SQL Workshop to see that the tables you deployed are now listed in the Recently Created Tables area.

Exporting Data from Your Database Cloud Service

    In this section, you export your data from your Database Cloud Service. Perform the following steps:

    Switch to the browser where you display the Service details. Click the Data Export tab.

    Click Export Data.

    Note that the Data Structures (DDL) will always be exported but you can choose whether you want to include the data. Click Create Data Export.

    Note the export was created. Click the Overview tab to view the SFTP user the export was uploaded to.

    The SFTP Account is the same account that you reset the password for. Click the Data Export tab again.

    Click Refresh to see that the status has changed to Available. You can now view the dmp file using a SFTP tool such as Filezilla.

    Connect to the SFTP Host with the Username and password. Note that the dmp file is contained in the download directory. You can drag these files to your machine or any other desire location

Summary

    In this tutorial, you have learned how to:

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

    Resources

To help navigate this Oracle by Example, note the following:

Hiding Header Buttons:
Click the Title to hide the buttons in the header. To show the buttons again, simply click the Title again.
Topic List Button:
A list of all the topics. Click one of the topics to navigate to that section.
Expand/Collapse All Topics:
To show/hide all the detail for all the sections. By default, all topics are collapsed
Show/Hide All Images:
To show/hide all the screenshots. By default, all images are displayed.
Print:
To print the content. The content currently displayed or hidden will be printed.

To navigate to a particular section in this tutorial, select the topic from the list.