Using the Cart in SQL Developer 4.0

Overview

    Purpose

    This tutorial describes how to use the cart feature in Oracle SQL Developer 4.0.

    Time to Complete

    Approximately 25 minutes

Introduction

    In this tutorial, you add database objects to a cart, diff multiple carts, and export a cart to another schema.

    Prerequisites

    Before starting this tutorial, you should have:


Setting Up the Cart Environment

    In this section, you set up the users and connections that you will use for this tutorial.

    Double-click on sqldeveloper.exe to launch SQL Developer.


    In the Connections window, click New Connection.


    In the New / Select Database Connection dialog box, enter or select the following values, then click Test:

    Name: SYSTEM
    Username: system
    Password: Enter the password for your system.
     Save Password: Select this check box
    Connection Type: Basic
    Role: default
    Hostname: localhost
    Port: 1521
    SID or Service Name: Enter SID or Service Name

    Your screen should look similar to this:

    Click Save and then click Connect. The new connection is saved and a worksheet is displayed.

    In the SYSTEM worksheet, enter the following SQL statement to create and provision new user hr_prod (production user).

    CREATE USER hr_prod IDENTIFIED BY oracle ;
    GRANT "RESOURCE" TO hr_prod ;
    GRANT "CONNECT" TO hr_prod ;
    GRANT UNLIMITED TABLESPACE TO hr_prod ; 

    Press F5 (Run Script) to execute the statements.


    Create an HR_PROD database connection with the following values:

    Name: HR_PROD
    Username: hr_prod
    Password: Enter the password for your system.
     Save Password: Select this check box
    Connection Type: Basic
    Role: default
    Hostname: localhost
    Port: 1521
    SID or Service Name: Enter SID or Service Name

    Click Test, then Save, and then Connect.

    Create a connection to HR with the following values:

    Name: HR
    Username: hr
    Password: Enter the password for your system.
    Save Password: Select this check box
    Connection Type: Basic
    Role: default
    Hostname: localhost
    Port: 1521
    SID or Service Name: Enter SID or Service Name

    As with previous connections, click Test, then Save, and then Connect.


    The HR worksheet is displayed.

    Select Tools > Database Copy to display the Database Copy Wizard.

    On the Source/Destination page, select HR for the source connection..


    For the destination connection, select HR_PROD.

    Deselect Copy Data, accept the remaining defaults, and click Next.

    On the Object Types page, deselect Toggle All, select Tables, and then click Next.

    On the Specify Objects page, click Lookup.

    Your table selections are confirmed.

    Click Next.

    On the Copy Summary page, expand each folder to confirm your Copy selections.

    Click Finish.

    When execution is complete, the Database Copy log is displayed, showing the DDL statements generated for each table. Here is an excerpt:

    Review the log and then close it.

    In the Connections window, expand HR_PROD and then Tables to view the results of the database copy.

    Close the SYSTEM and Start Page tabs. Do not save changes.



    Your setup is complete.
     

Adding Objects to a Cart

    You can add objects to the Cart by dragging them from the Connections window to the Cart.

    In the Connections window, Select View > Cart.

    A new cart, Cart_1, is opened to receive selected objects.


    Expand the HR connection and then expand Tables (Filtered).


    Drag and drop the EMPLOYEES table to the Cart.


    The EMPLOYEES table is added to the Cart.

    Deselect DDL and select Data.

    Your Cart_1 tab should look like this:

    In the selected row, click the Where field to filter your data.

    Click the pencil icon to display the Data Where dialog box.

    In the Object Where text box, enter employee_id > 120.

    Click Go to view the filter.

    Click OK.

    In the Connections window under the HR connection, expand Procedures.

    Drag ADD_JOB_HISTORY to the Cart.

    The ADD_JOB_HISTORY procedure is added to the Cart.

Saving a Cart

    You can save carts for later use.

    On the Cart menu, click Save Cart.

    Browse to the save location, confirm the File and Encoding values, and then click Apply.

    The Cart is saved as an XML file.


Diffing Multiple Carts

    You can use multiple carts to handle multiple subsets of objects. You can also reveal the differences between carts through a process called "diffiing."

    Click New Cart.

    Under the HR connection, expand Views.

    Drag EMP_DETAILS_VIEW to Cart_2.

    Drag the ADD_JOB_HISTORY procedure to Cart_2. Your screen should look like this:

    Select Diff Carts.

    Review the Diff Objects defaults and then click Apply.

    The Diff report displays the differences between Cart_2 and Cart_1. Since the same procedure appears in both carts, only the view EMP_DETAILS_VIEW is displayed.

    Click EMP_DETAILS_VIEW to review the DDL.

    The DDL is displayed.

    If necessary, use the Drag to resize arrows to resize the script window and the scroll bar at the bottom of the panel (not shown) to reposition the script text.



Exporting a Cart

    From the Cart, you can create an export file containing all or selected database objects and associated data for deploying later to other databases (either through the Cloud or conventional file import). In this section, you export data for the EMPLOYEES table and the ADD_JOB_HISTORY procedure to the hr_prod schema.

    Click Cart to return to the original Cart.

    Select Cart_1.

    Click Export Cart.

    The Export Objects dialog box is displayed.

    Uncheck Show Schema. Objects can be exported in a variety of formats. Accept the default insert and click Apply.

    The export.sql worksheet is displayed. Examine the SQL statements in the worksheet.

    You will now insert the data into the EMPLOYEES table and create a procedure in HR_PROD. Select the HR_PROD connection.

    Click Run Script (F5) to export the data.

    Under the Tables (Filtered) connection, click EMPLOYEES to check the results.

    Select the Data tab.

        

    Data is displayed.

    Optional: Under the HR_PROD connection, collapse EMPLOYEES and then Tables (Filtered).

    Expand Procedures.

    The ADD_JOB_HISTORY procedure is displayed, confirming a successful export.



    Congratulations! You have completed the tutorial.

Summary

    This tutorial described how to use the Cart with Oracle SQL developer 4.0. You learned how to add database objects to a cart, diff multiple carts, and export a cart to another schema.

    Resources

    Additional information about Oracle and Oracle technologies is available at the following sites:

    Credits

    • Lead Curriculum Developer: Pete DeHaan
    • Other Contributors: Ashley Chen, Nancy Greenberg

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

Hide Header Buttons:
Click the title to hide the buttons in the header. To show the buttons again, click the title again.
Topic List:
Click a topic to navigate to that section.
Expand All Topics:
Click the button to show or hide the details for the sections. By default, all topics are collapsed.
Hide All Images:
Click the button to show or hide the screenshots. By default, all images are displayed.
Print:
Click the button to print the content. The content that is currently displayed or hidden is printed.

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