How to import data from Excel to PL/SQL Developer-CodePudding

I need to compare data from tables in Oracle with data from tables in excel, how can I import data from excel. How can I import data from excel to test table in oracle pl/sql dev?

CodePudding user response：

There are two options:

Option I:

Create test table with no. of columns and data type matching with excel data.
Open the test table in PL/SQL developer in edit mode. You can do this by selecting rowid in SELECT statement or writing the SELECT with 'for update'. For example:

SELECT t.*, t.rowid FROM test t;
OR
SELECT t.* FROM test t for update;

Click on the lock ICON to keep it in pressed status for opening the fetched rows for editing.
Now copy the data range from excel and in PL/SQL developers SQL result grid, select the empty column and paste the copied data. (Note: you would need to add one empty column in the beginning of your first data column and include that blank column as well while copying data. This blank column is a placeholder for the serial no. column in PL/SQL developer's result grid. Otherwise your first data column will be eaten up by the serial no. column of result grid. :-) ).
Click on green tick to commit the data to database.

Option II: In excel you can compose INSERT query referencing data cells with help of Excel's concatenation (using & ) and execute all INSERT in PL/SQL developer.