Oracle Sql How to Upload a Table

Importing and Exporting using the Oracle SQL Developer three.0

<Practise not delete this text because it is a placeholder for the generated listing of "chief" topics when run in a browser>

Purpose

This tutorial shows you how to import and export your database objects using Oracle SQL Developer 3.0.

Fourth dimension to Complete

Approximately xx minutes.

Overview

Oracle SQL Developer is a free graphical tool that enhances productivity and simplifies database development tasks. Using SQL Developer, users can browse database objects, run SQL statements, edit and debug PL/SQL statements and run reports, whether provided or created.

Default connectivity to the database is through the JDBC Sparse driver, hence an Oracle Dwelling house is non required. To install SQL Developer, simply download and unzip the file. SQL Programmer users tin connect to any supported Oracle Database.

Oracle SQL Developer three.0 introduces a DBA Navigator and provides support for DBMS Scheduling. Other features include a new Query Architect, SQL Tuning Advisor, and Explain Plan Diff utility. For a complete list of new features, click here.

This tutorial focuses on Import and Export functionality.

Prerequisites

Before starting this tutorial, you should:

.	Install Oracle SQL Developer 3.0 from OTN. Follow the release notes.
.	Install Oracle Database 11g with sample schema.

.	Unlock the HR user. Login to SQL Developer as the SYS user and execute the following command: alter user hr identified by hr account unlock;
.	Download and unzip the files.zip file that contains all the files you lot need to perform this tutorial.

Creating a Database Connection

The beginning step to managing database objects using Oracle SQL Programmer iii.0 is to create a database connection. To create a connectedness, perform the following steps:

.	If you installed the SQL Developer icon on your desktop, click the icon to start your SQL Programmer session. If you practise not have the icon located on your desktop, find the executable file (either named "sqldeveloper.exe" on Windows or "sqldeveloper.sh" on Linux), and run it.
.	Your Oracle SQL Developer application starts.
.	In the Connections navigator, right-click Connections and select New Connection.
.	The New / Select Database Connectedness dialog window opens. Enter the connection details as follows: Connection Proper noun: hr_orcl Username: hour Password: <your_system_password> Hostname: <your_machine> SID: <your_SID> Click Examination.
.	If the condition is success, click Save to save the connectedness, and then click Connect.
.	The connection is saved and you come across the connexion in the Connections list.

Configuring the Import Environment

You lot tin can use the Import Magician to customize your preferences for the import environment and import information using the dissimilar methods like Insert, Insert Script, External Table, Staging External Table and SQL*Loader.

Perform the following steps to set the preferences in your Import Wizard.

.	Select Tools > Preferences.
.	In the preferences navigator, expand Database and select Import. Here you tin can specify default properties for import. Set the Import Row Limit to 100 to be able to view 100 rows at a time. This helps while working with large files. Prepare a value for the Preview File Read Maximum. This ensures that the unabridged file is not read into the memory.
.	Aggrandize File Formats in the preferences navigator. There are 4 types of file formats. You can specify default attributes for each supported format for the imported data.
.	For example, select the Delimited Format to set the default preferences.
.	Select Header to display the beginning row of data as the header, if this choice is not selected then the information is displayed without a header. Then select the left and right enclosure every bit '[' and ']' respectively. Follow the aforementioned steps to gear up the default preferences for the other file formats.
.	The Import Methods pane allows you to specify the default way for importing data into the existing table or a new tabular array. Select Import Methods in the preferences navigator.
.	The v methods used to import data to a new table are: Insert: Imports the data directly into the table. Insert Script: Creates a script with insert statements. External Table: Generates External Table DDL for reading the file. Staging External Tabular array: Creates a temporary External Tabular array for Importing into a table. SQL Loader Utility: Generates files for SQL Loader to Import a table. Select New Tabular array Import Method as Staging External Table from the listing.
.	Select External Tabular array in the preferences navigator. Exercise not specify any values here. The default directory volition be used. Click OK. Note: If you lot desire to specify a default directory, make sure that the appropriate permissions are granted.
.	Select Insert in the preferences navigator and prepare the Insert Batch Size (Rows) as fifty.
.	Select SQL*Loader in the preferences navigator and specify the log and bad files directories hither. If the directories are not specified, by default they will be written to the Import file directory.
.	Click OK to save your default preferences.

Importing Data into a Tabular array using Insert Method

Oracle SQL Developer makes it very easy to import information into an existing tabular array.

Perform the following steps to import data into the DEPENDENTS table using Insert Method.

.	Before importing the data into a table, make sure you have the table with the specified cavalcade. Expand Tables and select DEPENDENTS.
.	Click Information.
.	Observe that there is no data in the DEPENDENTS table.
.	To import the data to the DEPENDENTS table from some other location, right-click DEPENDENTS and select Import Data.
.	Select the load_dep.dsv file from the directory where you lot unzipped the files from the Prerequisites section and click Open.
.	Click Side by side.
.	Select Import Method as Insert and click Next.
.	In the Choose Columns window, click Next.

.	In the Column Definition window, select Name from the Friction match By drib down to match the Source Data and Target Data past Name.
.	In the Target Tabular array Columns click the Proper name drop down and make sure that the Source Information Columns and the Target Table Columns are mapped correctly.
.	Click Adjacent.
.	Click Verify to verify the parameters earlier importing.
.	Annotation that the exam condition is SUCCESS. Click Stop.
.	Click OK.
.	To view the data, make sure the Data tab is selected and click Refresh to testify all the data.

Configuring the Export Environment

You tin can utilise the Export wizard to export some or all database objects of a database connection to a file. The export file will not only incorporate the SQL data definition language (DDL) statements to create the exported objects, only if yous choose to export the information too, the export file will comprise Data Manipulation Language (DML) to populate the exported objects.

Perform the post-obit steps to set your default environment in the Export Wizard.

.	Select Tools > Preferences.
.	Expand Database in the preferences navigator.
.	Aggrandize the Export/View DDL Options to display all the file format types.
.	Yous tin alter default values for the source and target pages in this export wizard. Select Consign/View DDL Options.
.	Select Format as insert from the drop down. Insert exports the data in SQL format.
.	Select Salve As as Single File to export data into a single file. Click OK to save your settings.

Export the Database

In this topic, you learn to Export your total database into the required file format.

.	Click Tools > Database Export.
.	In the Connection drop downwardly, select the connection from which yous want to consign objects. In this case yous choose the connection hr_orcl.
.	Deselect the Export Data check box and Click Next.
.	Retain the default values of all object types selected. Click Next. Notation: You must select at least 1 object.
.	Click Next.
.	Yous can review the information that is used to create the output file. The Export Summary data will contain statements to consign database objects and data co-ordinate to your specifications. To create the output file, click End.
.	Since the preferences was ready to open as SQL file, detect that after exporting, the export.sql file automatically opens in SQL Worksheet. All the DDL statements in the 'hr_orcl' schema are displayed. You tin can scroll downwardly to view the entire information.
.	You can also consign data using Connection Navigator. Perform the following steps: Select hr_orcl in Connections navigator.

.	Expand Tables and right-click EMPLOYEES > Quick DDL > Save to Worksheet.
.	The DDL for the EMPLOYEES table is displayed. Scroll to the lesser of the window to view the unabridged data.

Expand Tables and right-click EMPLOYEES > Quick DDL > Save to Worksheet.

The DDL for the EMPLOYEES table is displayed. Scroll to the lesser of the window to view the unabridged data.

Exporting from the Data Filigree

In this topic, you invoke the export wizard from the Information Grid, filter the information, select but the required columns and export the data.

.	Select the EMPLOYEES table.
.	Select Information from the tab.
.	To Consign the data, right-click anywhere in the information grid and select Export from the context carte du jour.
.	Yous can export the information in different formats. For our example, select Format as pdf from the driblet down listing.
.	Enter the following values: Championship: Emplist Subject: Employees list Keywords: Emp listing Click Adjacent.
.	Click Columns.
.	Select the Data Columns, which you want to consign. By default, all the object types are selected. To select or deselect all the object types, click Toggle All check box.
.	In this instance you lot choose the columns EMPLOYEE_ID, FIRST_NAME, LAST_NAME, Salary and deselect the others. Click OK.
.	To employ filters, click Where ( ).
.	Enter Where clause equally SALARY > 10000 and click Go.
.	Observe that all the employees whose salaries are greater than 10000 are displayed. Click OK.
.	Click Next.
.	Review Consign Summary and click Finish.
.	If you review the export.pdf file, you should see the following: