If you do not see the NEW_REGIONS table, disconnect from NICK (right-click NICK in the Connections navigator and select Disconnect) and connect again, and expand the Tables node. In the Connections navigator, expand the Tables node under the NICK connection. The Script Output pane shows that the NEW_REGIONS table has been created. In the SQL Worksheet for the NICK connection, type the following:Ĭreate table new_regions ( region_id number primary key, region_name varchar2(25)) In SQL Developer, if you have not already created a database connection for NICK, create the connection. To accomplish these goals, follow these steps:
(This example is trivial, and adding a row to the Excel file may not be typical, but it is presented merely to illustrate some capabilities.) This user wants to take the exported data, add one row in the Excel file, and import it into a new table that has the same column definitions as the REGIONS table. Figure 10-2 shows the result of these actions.Įxample: Importing Data from a Microsoft Excel FileĪssume that you wanted to import the data that was exported in "Example: Exporting Data to a Microsoft Excel File", into a new table that has the same column definitions as the original ( REGIONS) table.įor example, assume that you created a user named NICK following the instructions in "Example: Creating a User". On the Specify Objects page, click Lookup, then double-click the REGIONS table on the left to have it appear in a row in the bottom part of the page. On the Types to Export page, deselect Toggle All, then select only Tables (because you only want to export data for a table).
(In the figure, this file is C:\temp\export.xls.) Save As location: Enter or browse to a desired folder on your local hard drive, and specify the file name for the. sql script file is generated (which will not happen in this example), it will not contain any CREATE statements, but only INSERT statements.įormat: Select xls to have the data saved to a Microsoft Excel. Optionally, click the REGIONS table in the Connections navigator, and examine the information under the Columns and Data tabs in the main display area.įigure 10-3 Export Wizard: Source/Destination Specifying Data Export Onlyĭescription of "Figure 10-3 Export Wizard: Source/Destination Specifying Data Export Only"Īccept the default values for the Source/Destination page options, except as follows:Įxport DDL: Deselect (uncheck) this option. The Script Output pane shows that the REGIONS table has been created and four rows have been inserted. In the SQL Worksheet for the NICK connection, type the the Run Script icon. If you have not already created a database connection for NICK, create the connection. To re-create the REGIONS table in the schema of user NICK by invoking the script in C:\temp\export.sql follow these steps using SQL Developer: This other schema can be an existing one or one that you create.įor example, assume that you created a user named NICK following the instructions in "Example: Creating a User". Now you can check your file location for the generated CSV file Using Oracle SQL Developer.Example: Importing Metadata and Data Using a Script FileĪssume that you wanted to re-create the REGIONS table that you exported in "Example: Exporting Metadata and Data for a Table", but in a different schema. Then click on the Finish button to export the data. On the next step, it will show you the export summary.Specify the file location and name in File field then click on the Next button. Click the check-box Header if you want to export the column headers.
Then choose CSV option from the Format drop-down if you want to keep delimiter Comma for the CSV file, else if you're going to export data other than Comma delimiter, then choose Delimited Option from the drop-down and specify the field separator from the Delimiter drop-down.Then an Export Wizard window will open, as shown in below image.Now do the right-click on the query results grid and from the shortcut menu select Export option.Then type your SQL query and press Ctrl+Enter to get the result in a grid in Oracle SQL Developer.Then choose Database Tier > Database Files > SQL File and click OK to open an SQL editor. Then click on the menu File > New and the New Gallery window will open.Open Oracle SQL Developer and connect to the database.Follow These Steps to Export Table Data to a CSV File Using Oracle SQL Developer
0 kommentar(er)
