Create temp table using Oracle Sql Developer or Insert Excel Worksheet 1

[BxWill]

Each week I perform the following;

Copy approximately 6000 account id numbers from one MS Excel worksheet(worksheet 1) to another worksheet (worksheet 2)within the same workbook.

Using advanced filter in MS Excel, obtain the unique account id numbers (approximately 4000 each week).

Save worksheet 2 as a text file.

Open MS Access and import text file as table.

Link text file table to Oracle table linking on common field within pre-built MS Access query.

Run MS Access query to extract phone numbers, from Oracle db, for each account id.

Copy paste query results to Excel worksheet and use vlookup to "merge" the phone numbers to the other data for each account id.

Total time = 1 hour+!

This needs to be performed in no more than 10 minutes!

Exploring two options;
Option 1 - Import Excel worksheet into Oracle sql developer
Option 2 - Create temporary tables in Oracle using the "Insert into ##TmpAcctID" syntax into MS Excel range and pasting into the Oracle sql developer pane (similar to what I perform using Mgmt Studio to extract data from Sql Server).

Just wanted to gather some insight before I proceed.

Can both options be performed?
Is it possible to provide the steps for each option above?
Which option above is preferred?

Thanks in advance.

 

[Thargy]

Bx,

I believe that there is a much better solution for you, but most of my ideas below depend on you having a later version of Oracle than 8i.

Is there any reason why you can't download oracle express and install version 11g2 on your desk top PC? If you can then read on, if not, can you let me know the whys and wherefores of your situation.

The method of choice for me (since I am an oracle DBA) would be to export the excel spreadsheet as a text file, and use oracle external tables to read it. Since oracle can then do all the processing in one go, you could script the entire process.

Alternatively, oracle application express (colloquially referred to as APEX) is freely available, and can directly import spread sheets to oracle, with the simple click of a mouse.

I'm most happy to assist, provided you can give me some details.

Regards

T

 

[BxWill]

It appears that I have Oracle version 10.2.0.

I cannot download any other version to the company-owned computer.

 

[Thargy]

Bx,

would you mind re-posting in the 10g forum? By posting in 8i, you restrict me (and anyone else) from making up-to-date recommendations, and/or providing current solutions.

Can you state in your post whether or not you have access to a 10g machine (either on your desktop, or a server based database).

See you there soon.

Regards

T