Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

At my work we used to use MS Access now we are forced to use SQL developer to create queries and export the data.

Details:

  1. We can only query to the database
  2. We cannot create a new database
  3. We use SQL Developer to query
  4. The data we need is in an oracle database
  5. We need to match data from a excel file and find detailed information about that in the database.
  6. ODBC in Excel is not an option, we cant use this.

In MS Access there was a Tab called "External Data > Excel" which let us open a excel file in MS Access, and CrossRef that with data in our database

Question: Is there such a option/addon/module in SQL Developer(4.0) to open excel without having to write the data to a database on the server? (we aren't allowed)

I'd like to import a excel file with part-numbers and find detail information of these part-numbers from the database and show these as the results so I can export and save that as a new excel file. Can SQL developer do that?

share|improve this question
    
are you not allowed to create "GLOBAL TEMPORARY TABLE" as well? –  Anup Shah Mar 3 at 22:59
    
Im afraid not: 01031. 00000 - "insufficient privileges" –  ErikdR Mar 5 at 13:55

6 Answers 6

What Office Excel version? What Oracle Database version? SQL Developer version? As long as I remember, you can connect excel with an oracle database since many years ago, and handle data like another excel datasheet (with its limitations, of course). I'm sure they've improved this feature. http://office.microsoft.com/en-ca/excel-help/connect-to-an-oracle-database-HA104019815.aspx

Another solution is to buid the query by hand. Using an advanced text editor/IDE, such "Notepad++", and scripting the task its easy. I do this in my job sometimes.

e.g. You have in a excel file the past month sells summary, where there are only Reference numbers, and you want to obtain the items descriptions from the database.

enter image description here

Then select them, copy, and paste into notepad++

enter image description here

Next. Put the cursor in the beginning and press the "record" button (Or menu "Macro" > "Start Recording"). Then by using special text keys you can format as it needs. For our case, we need to format those rows into a "IN" clause syntax. So, we type: Single quote ('), "End" key, single quote ('), comma (,), blank space, "Delete" key. Then, press "Stop" recording. enter image description here

Now just press the "Play" button to reproduce the secuence you've recorded as many times as we need (or press the "double Play" button to reproduce until end of file) enter image description here

finally You can write the whole query with the where-in clause enter image description here

And thats it. Run this query in SQL Developer, and export the results in XLS format (right click in the results grid >> export)

BUT, if your everyday work consist in cross many excel data with the database, this is not the most appropriate and professional solution. This can save you a couple of times, but If you're going to do this EVERY DAY, then you must talk to the person in charge of the security policy, or your database administrator, and explain him your problem. If you need a ODBC connection (or another kind of special privilege) for your everyday work and you are not allowed to have it, its like if you're a lumberjack and are not allowed to use an axe just because its dangerous.

share|improve this answer
    
I think connecting excel with the database requires ODBC which we aren't allowed to use. Excel Version: 2007 Oracle version: Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 - 64bit Production PL/SQL Release 11.2.0.3.0 - Production "CORE 11.2.0.3.0 Production" TNS for Linux: Version 11.2.0.3.0 - Production NLSRTL Version 11.2.0.3.0 - Production And export is impossible as the database is way to big. –  ErikdR Mar 7 at 12:18
    
I've updated my answer! –  vegatripy Mar 7 at 17:09
    
Thank you for your answer, it was already a really big hassle for me to get the query rights on the production database. We used to have odbc, but that's taken away. So now we need to find other ways of getting our data. I'am such a useless lumberjack at the moment. –  ErikdR Mar 8 at 10:24
    
Im gonna try this monday and ill see if this is workable. –  ErikdR Mar 8 at 14:50

Just work with Excel - it can open the connection to the oracle and query the required data..

Google first hit: http://hoopercharles.wordpress.com/2010/01/12/select-from-or-update-a-database-table-based-on-the-contents-of-an-excel-spreadsheet/

share|improve this answer
    
I'm afraid I cannot connect to the database via ODBC, I wish I could do that, that's one of the reasons we cannot use MS access either. Its so annoying all these rules and 'security' measures. –  ErikdR Mar 7 at 11:54
4  
Security rules exist by a million good reasons, and they must be applied in every environment. When those security rules are a headache for your everyday work, then the problem is not the security rules themselves. The problem is the person who badly setup them. Instead of twisting your job and adopt it to the new security policy, I should go to person in charge and tell him how badly the new security policy is to my job, and it needs to be correctly set up. –  vegatripy Mar 7 at 16:25

Prerequisites

  • You need access to an Oracle Database 10g or Oracle Database 11g for your migration repository and an oracle database for the target migration. These can be the same database.
  • Download Oracle SQL Developer 3.0. Earlier versions of SQL Developer also support database migrations, however the flow of work has been changed in SQL Developer 3.0 and we recommend that you use the latest for your migrations.

Follow this link to get more info here

share|improve this answer
    
I'm afraid this is not what i asked for. –  ErikdR Feb 27 at 10:21

I propose you another solution. In your excel file write rule which will generate sql with your part-numbers/keys for quering details. The generated sql you can execute on your server. Finally, result from sql you can export as excel file.

share|improve this answer
    
This will not do, as i need to join multiple tables to get the results i need. –  ErikdR Mar 3 at 22:00

I've not used them myself, but perhaps Linked Servers and Distributed Queries can be used (see here and here).

Examples:

SELECT * INTO XLImport3 FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=C:\test\xltest.xls;Extended Properties=Excel 8.0')...[Customers$]

SELECT * INTO XLImport4 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', [Customers$])

SELECT * INTO XLImport5 FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'Excel 8.0;Database=C:\test\xltest.xls', 'SELECT * FROM [Customers$]')
share|improve this answer
    
Connecting to the database from excel is not possible for me it seems. –  ErikdR Mar 7 at 15:42
    
That's for Microsoft SQL Server. FYI This is also possible in Oracle with Oracle Heterogeneous Services, but it will require to configure it on server's TNS, create a database link (if the DB user have the privileges to), and create a System DNS ODBC Data Source on the host machine where the excel file is. technology.amis.nl/2006/08/04/… –  vegatripy Mar 10 at 11:08

I am not sure that this would work.... but you never know. Setup SQL developer to connect to MS Access by following the instructions here:

http://www.oracle.com/technetwork/developer-tools/sql-developer/ms-access-connection-viewlet-swf-086613.html

And attempt to copy the table from the Oracle connection to the MS Access connection. You would have all the tables you need in Access and then you would be able to import your Excel document as a table, thus working around needing an ODBC connection.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.