How to Export Data using SQL Developer. This is a repost from Oracle blog, how to export data from SQL Developer. Ref: How to Export Data using SQL Developer
SQL Developer provides the ability to export user data to a variety of formats: CSV, XML, LOADER, TEXT, INSERT, HTML and XLS.
- In order to export the data from a table you can either use the SQL Worksheet and write a SQL query to retrieve the required data or you can Click on the Data tab of a table definition. We’ll use this latter approach.
Expand the Connections Navigator and the Tables node.
Click on DEPARTMENTS.
Select the Data tab.
![Exporting Data](https://revion.com/wp-content/uploads/2023/02/sql-1.jpg)
2. Right-click anywhere on the data to invoke the context menu.
Select insert from the Export Data context menu. This will produce a file of insert statements.
![](https://revion.com/wp-content/uploads/2023/02/sql-2.jpg)
3. Choose an appropriate directory and name for the file.
You can also fine tune what is exported by selecting the columns and adding a where clause to reduce the number of records returned. In this example, we’ll take the default of all the records and columns.
![](https://revion.com/wp-content/uploads/2023/02/sql-3.jpg)
- Click Apply to create the file.
- To review the file created, click the Open Folder icon
. Select the new file DEPARTMENTS.sql from your file directory. The sql file will open into a SQL Worksheet.
![](https://revion.com/wp-content/uploads/2023/02/sql-4.jpg)
4. Exporting Object Definitions
SQL Developer provides the ability to export some or all of your object definitions to script files. You can use these as a backup of the object definitions or run them in another schema. In this exercise, you export all the object definitions and the data for the Departments table.
- Using the main menu, select Tools->Database Export.
![](https://revion.com/wp-content/uploads/2023/02/sql-5.jpg)
- An Export wizard will open. At the top of the screen, enter a directory and file name. e.g. C:\Working\exportDept.sql. and select the Connection.
![](https://revion.com/wp-content/uploads/2023/02/sql-6.jpg)
- Set the DDL Options for this Export. e.g. check Include Drop Statement.
Click Next.
![](https://revion.com/wp-content/uploads/2023/02/sql-7.jpg)
- In this step, you can slect what Object Types to export. We will take the default for all object types.
Click Next.
![](https://revion.com/wp-content/uploads/2023/02/sql-8.jpg)
- In this step, you can specify the objects to export. Instead of querying all objects by immediately selecting Go, you can enter a restriction criteria. Enter D% and then click Go.
![](https://revion.com/wp-content/uploads/2023/02/sql-9.jpg)
- You can restrict the list of choices further by using the drop list on the right.. Select Table from the list of object types.
![](https://revion.com/wp-content/uploads/2023/02/sql-10.jpg)
Click Go. The list now displays tables that start with “D”.
- Select the table Departments and shuttle to the right hand panel.
Click Next.
![](https://revion.com/wp-content/uploads/2023/02/sql-11.jpg)
- In this step, you can specify the data that gets exported. Click Go and a list of tables will be listed. Shuttle Departments to the right hand panel..
Click Next.
![](https://revion.com/wp-content/uploads/2023/02/sql-12.jpg)
- In this final step, you can review your Export Summary.
Click Finish.
![](https://revion.com/wp-content/uploads/2023/02/sql-13.jpg)
- To review the file created, go to the main menu and select View-> Files to open the Files Navigator tab. Select the new file exportDept.sql from your file directory. The sql file will open into a SQL Worksheet.
![](https://revion.com/wp-content/uploads/2023/02/sql-14.jpg)