Develop with Oracle SQL Developer

SQL Developer provides a powerful integrated development environment (IDE) for authoring SQL and PL/SQL on databases. You can use SQL Developer to connect to your Oracle Database Exadata Express Cloud Service and develop on the database. You can use Oracle SQL Developer to load data to Exadata Express service from either an on-premise database or other cloud service.

Oracle SQL Developer provides various options for loading data into your database. Although you can use any of these options, each of them will be more beneficial in a specific scenario, as explained in individual topics. Depending on your requirement, you can choose the most suitable option to perform data loading.

Topics

• Use Database Copy for Data Load

• Use SQL Developer Cart for Data Load

• Use Drag and Drop for Data Load

• Execute SQL Scripts for Data Loading

To learn more about SQL Developer, see About SQL Developer in the SQL Developer User’s Guide.

Use Database Copy for Data Load

Oracle SQL Developer provides an option called Database Copy to choose and copy database objects into your  Oracle Database Exadata Express Cloud Service from an on-premise connection or any other cloud connection. This option is more suitable when you want to copy objects up and have the ability to filter data out using WHERE clauses or pull all or selected objects from one or more schemas.

To copy database objects from another connection into your Exadata Express, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, click Tools.

   The Tools drop down menu displays.

   
   

   
   Description of the illustration database_copy.png

   
   
3. From the drop down menu, click Database Copy.

   The Database Copy Wizard appears.

   
   

   
   Description of the illustration database_copy_wizard.png

   
   
4. Follow the on-screen instructions, and select the suitable options as you proceed through the various steps of the wizard.

5. Click Finish.

   The Copying dialog displays.

   
   

   
   Description of the illustration copying.png

   
   

All the database objects specified in the Database Copy Wizard are copied to your Exadata Express connection in Oracle SQL Developer. You can browse the Connections tree to verify if the selected database objects are copied in your Exadata Express connection.

Use SQL Developer Cart for Data Load

Oracle SQL Developer creates a cart containing objects you want to load in to your  Oracle Database Exadata Express Cloud Service, connects to your service and deploys data from the cart to the service. Using cart is more convenient, when you need to create a subset of objects from one or more schemas locally that you want to deploy to another database on a regular basis.

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

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, click View.

   The View drop down menu displays.

   
   

   
   Description of the illustration view_cart.png

   
   
3. From the drop down menu, click Cart.

   The Cart window appears on the bottom right.

   
   Description of the illustration empty_cart.png
4. Drag the required Oracle Database objects from your on-premise database, and drop them in to the Cart window on the right.
   
   

   
   Description of the illustration sqldev_cart.png

   
   
5. If you want to include data with the cart deployment, in the Cart window, click the Data check box.

6. To deploy this cart, click the Copy (Ctrl+C) icon at the top right of the Cart window.
   
   

   
   Description of the illustration copy_cart.png

   
   

   The Copy Objects dialog displays.

7. Select your Exadata Express connection for Destination Connection. Choose the appropriate options under Copy DDL and Copy Data categories.
   
   

   
   Description of the illustration copy_objects.png

   
   
8. Click Apply.

   The Copying dialog displays.

   
   

   
   Description of the illustration copying.png

   
   

All the database objects added to the cart are copied to your Exadata Express connection in Oracle SQL Developer. You can browse the Connections tree to verify if the selected database objects are copied in your Exadata Express connection.

Use Drag and Drop for Data Load

Oracle SQL Developer provides an option to drag and drop database objects into your  Oracle Database Exadata Express Cloud Service from an on-premise connection or any other cloud connection. Drag and Drop option is perfect for ad-hoc, on-the-fly operations. For example, you can just select a table or view in the connections tree, and drag it over when you need to set up a table or view quickly. 

To drag and drop a database object from another connection into your Exadata Express, you must perform the following steps:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. From Oracle SQL Developer, select one or more database objects in the source connection. Drag it to the destination location in your Exadata Express service in the connections tree, and drop it.

   The Copy To Oracle dialog opens. It provides you with options to choose to copy DDL or data or both. Under each category, you have options to choose an action to be performed if the database object already exists in your Exadata Express service.

   
   

   
   Description of the illustration dragndrop.jpg

   
   

   Note:

   • The source database must be connected before performing the Drag and Drop operation.  If you drop on a disconnected database, Oracle SQL Developer will open/connect it, for you to copy.

   • You can also drag an entire node, such as 'tables' and it will drag all the tables, and their related objects such as triggers, indexes, and constraints, for dropping into your destination database.

3. Select the required options, and click OK.

   The Copying dialog displays.

   
   

   
   Description of the illustration copying2.jpg

   
   
4. Follow the on-screen instructions, and the selected database object will be copied into your Exadata Express connection in Oracle SQL Developer.

You can browse the Connections tree to verify if the selected database objects are copied in your Exadata Express connection. You can also look at the Database Copy report which is provided at the end of the copy activity.

Execute SQL Scripts for Data Loading

You can execute SQL*Plus scripts from Oracle SQL Developer to load data into your Oracle Database Exadata Express Cloud Service. This option is more beneficial for the users that already have deployment scripts. If required, they can even open these scripts and modify them using the powerful SQL Worksheet with error highlighting, SQL Recall, Code Insight, formatting and so on, before executing them.

Follow these steps to load and execute SQL scripts from Oracle SQL Developer:

1. Install Oracle SQL Developer locally, and create a cloud connection from Oracle SQL Developer to your service. see Connect SQL Developer.

2. Click Files from the View menu.

   The Files explorer tab appears.

3. Browse to the folder where the SQL script resides, and double-click the script to be executed.

   Note:

   The file having the SQL script must be with a .sql extension.
4. The SQL script loads into the SQL Worksheet. Click the Run Script (F5) icon.

   The script executes, and loads the data into the required tables as per the script.