MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Query Oracle database

with 98 comments

I caught a post on the OTN forum asking how to do this, and it happened to be something I’m working on for a new course that I’ll be teaching on data analytics. Ultimately, Microsoft Excel is the de facto tool of many accounts and financial analysts, protests notwithstanding.

This shows you how to query an Oracle 11g database from Excel 2007 (only available when you do a FULL install of Excel 2007). Actually, it should work on any current version of the Oracle database. The key to making this work is having the Oracle 10g Client software or an Oracle 11g database on the same machine. The Oracle client software allows you to resolve an Oracle Network Alias (found in the %ORACLE_HOME%\network\admin\tnsnames.ora file).

Here are the instructions with screen shots:

1. Click on the Data ribbon, and then click on the From Other Sources icon in the Get External Data section, as shown:

2. When you click on the From Other Sources icon, you’ll get the following drop down list. Click on the From Data Connection Wizard item.

3.The Data Connection Wizard provides a number of Microsoft data sources, but you click the Other/Advanced option to connect to an Oracle database.

4. The Other/Advanced selection launches the Data Link Properties dialog with the Provider tab open and active. Click the Oracle Provider for OLE DB choice. If you don’t have the Oracle Provider for OLE DB option, download the Oracle Data Access Components for Windows from Oracle site. The installation should also load it as a new data source, if not you can follow the same instructions provided for MySQL’s ODBC here. After clicking the Oracle Provider for OLE DB choice, you click the Next button.

5. The Next button brings you to the Connection tab. You should fill it out as noted below, and if you wish check the Allow saving password checkbox.

6. Before preceding, you should verify that the connection works. Click the Test Connection button to do that, and if successful you’ll see the following. Click the OK button in the Microsoft Data Link dialog to complete the test and the OK button in the Data Link Properties dialog.

7. After setting up the data connection in the Data Link Properties dialog, you’ll be taken to the Data Connection Wizard dialog. Here you can pick any table that you have access to within the PL/SQL schema (remember a schema in Oracle is a database in Microsoft SQL Server or MySQL). Click the Next button to continue.

8. After clicking the Next button, you’ll see the second Data Connection Wizard dialog. Click the Finish button to begin importing the data.

9. The Import Data dialog asks you how and where you want to import it. The following chooses to import it as an ordinary table. Click the OK button to import the data from a table. If you want to write a query against one or more tables, click here for the additional instructions.

10. Imports into Excel 2007 as a table are automatically created as a table. The following screen shot formats the display columns.

You have a number of very advantageous features in Excel 2007. They allow a number of options that resemble working within a database but honestly they’re much more difficult than working in SQL. I’d advise you create preformatted data by using views. Then, query the views through these steps.

Written by maclochlainn

November 7th, 2008 at 12:25 am

98 Responses to 'Query Oracle database'

Subscribe to comments with RSS or TrackBack to 'Query Oracle database'.

  1. thanx…
    something i was looking for


    7 Nov 08 at 1:43 am

  2. One of the noticable enhancement in Excel 2007 is that you are no longer limited to 65536 rows and 256 columns !

  3. tks a lot, this is what i was searching for


    15 Jan 09 at 9:51 pm

  4. Hi,

    do you know how to pass cell values to filter the returned rows from the query? Something like ‘select * from table where col1 = cellCoordinates’


    19 Jan 09 at 10:51 am

  5. This is what i have been looking for however when i get to the data Link Properties page I do not have the option of selecting ‘Oracle provider for OLE DB’. How does one have this option show / install?

    John B

    21 Jan 09 at 5:14 pm

  6. Are you on MS Office 2007 or 2008? Did you choose typical or complete when you installed the MS Office Suite?


    21 Jan 09 at 11:17 pm

  7. Can I use pl script instead of simple “select * from Table” to get from DB calculated data in to Excel 2003/2007

    my script looks like


    create temp table


    select … from tempTable;
    drop tempTable;

    Ivan S

    29 Apr 09 at 4:24 am

  8. Check this post out. You could write a function like this and then return the results into a view. That’ll make it cleanly to Excel.

    Hope this helps.


    30 Apr 09 at 11:50 pm

  9. Thanks very much.

    Can i use a query to do this? I need a query (PL/SQL) to import the data. If i can run the query using a batch file or something and it automatically imports the data into the Excel.

    And also how can I import into Oracle from Excel?



    28 Aug 09 at 2:25 am

  10. I’m working on some examples like that for an advanced Excel class. I’ll post them as soon as they’re ready.


    28 Aug 09 at 7:59 am

  11. Can you get results from more than 1 table mapped to excel?


    28 Aug 09 at 2:38 pm

  12. And if you need the software in order to get the “Oracle Provider for OLE DB” provider use one of the folowing links:

    Installed as part of the developer tools:

    Listing of OLE DB Drivers (11g not included in list as of this posting):


    31 Aug 09 at 9:39 am

  13. Thanks gnash!


    15 Oct 09 at 3:17 pm

  14. Whenever I try to connect to a large table, even as a PivotTable report, I get an error : “Could not connect to the database”. I tried connecting to a table with less records (10000) and it failed again. Next I tried connecting to a table with 1000 records and it succeeded. Next I tried connecting to a table with 10000 records and just 4 columns, it succeeded.
    In Excel 2007 is there a limit to the size (rows x columns) of the Oracle table that it connect to?
    Couldn’t find much info through google.


    18 Nov 09 at 1:57 pm

  15. Is it possible to use an excel sheet which contains data, be used to query and pull back data/records in an Oracle data base.
    I have developed a Import data MS Query/Excel SQL which happily pulls back data as defined in the query or sql, but I really want my excel listing to be used as part of the query, effectively joining the excel sheet with oracle tables. Is this possible? Hope this makes sense!



    27 Nov 09 at 10:58 am

  16. To all inquiring about building a query …

    #9 listed above, if you click ‘Properties’ button, it will take you to the Connection Properties screen. There will be 2 tabs (Usage and Definition) Click on Definition. In the middle you have “Command type” … drop down and select “SQL” … then enter you query in the ‘Command Text’ box located beneath.

    NOTE: It’s important that you do not connect to 1 specific table. Just ‘unselect’ the Connect to Specific Table checkbox and you’ll have access to all the tables. This helps when you’re creating a query using multiple tables.

    Hope this helps,



    30 Nov 09 at 7:46 pm

  17. You want to use your Excel cell contents as parameters to a query against Oracle? That can be done in Visual Basic for Applications.


    30 Nov 09 at 11:54 pm

  18. Vernon, I really appreciate you’re comment. It got me to put it at the top of my heap. Here’s a set of screen shots to illustrate it.


    30 Nov 09 at 11:55 pm

  19. Sorry, I have used MS Query previously, however I am trying to set it up from scratch. When I test connection, I am getting an ORA-12154. TNS could not resolve the connect identifier specified.

    Can you please point me in the right direction?


    19 Jan 11 at 10:09 pm

  20. The ORA-12154 error may mean that the Data Source (Step #5) you’re using in Excel doesn’t match the one in your tnsnames.ora file. The screen shots were using a sample Oracle Database 11g database, which uses orcl. If you’re using Oracle XE, it would be xe.


    20 Jan 11 at 12:11 am

  21. […] I posted how you could query Oracle from Excel 2007 […]

  22. Hello Michael, I feel that I’m lucky enough to get to your website after searching on the internet on how to connect to oracle through excel for hours. This is a great step by step procedure. Thank you very much for all the effort to make the process much simpler.

    I have a question. Once we connect to a schema in the oracle database and save one of the tables in an Excel workbook, every time I try to refresh the data, I am asked to provide the password for the database connection. Is there a way that I can save the password and will not have to type it every time I refresh? Please reply. Thanks.


    1 Mar 11 at 1:54 pm

  23. In step #5 above, enable the Allow Saving Password check box. It should do the trick for you.


    1 Mar 11 at 3:05 pm

  24. Appreciate your prompt response. My task is to connect to Oracle through Excel and save the table data in the workbook and send it to some one who doesn’t have Oracle client installed on their PC’s to access the workbook and refresh it when ever needed. What are the changes required in order to let them access the document (who do not have oracle client on their PC’s).


    2 Mar 11 at 9:35 am

  25. You can post the spreadsheet and distribute it but the query part requires either the Oracle Client or Oracle Database on their client. Have you examined configuring the XDB server and writing the file to a download directory that can serve it back to your customer? A beginning example is in this blog post, which I didn’t test.


    2 Mar 11 at 8:39 pm

  26. Is there a benefit to using the “Oracle Provider for OLE DB” over an ODBC connection? I’ve run into the issue where trying to connect through the Oracle ODBC driver now takes a couple of hours to return the list of tables from my Oracle database (running 11g). I’ve determined that that’s due to the query Excel runs to retrieve that table list, and am looking for a quicker way around that.

    Once I have the table list, everything goes quickly and smoothly.

    Many thanks.


    8 Mar 11 at 6:04 pm

  27. David,

    I’ve never tested the comparative speeds of the ODBC versus the OLE but I doubt there’s that much difference between the two as bridges between Excel and Oracle. However, they do use different query mechanics, I would suggest that you try queries as opposed to tables, which you can find in this other post. If you find any performance characteristics that you would like to share, please post them.

    Thanks, Michael


    8 Mar 11 at 9:55 pm

  28. In order to populate the list of tables as shown in figure #7 in this thread, I found out that Excel is running a query against Oracle in the background. When I was trying to use the ODBC connection, that query took two or more hours to execute. I figured out how to find the actual query text being run, and what I was asking you was whether that query was different if I used the Oracle provider for OLE DB.

    I tried using the OLE provider instead, and there was a failure after 5 hours trying to populate the list of tables (again, figure #7). I checked the query being run behind the scenes, like I did for ODBC), and while I didn’t compare the two queries character-for-character, they were awfully similar.

    Your other post didn’t help, because the issue I’m having is prior to figure #9.


    13 Mar 11 at 4:34 pm

  29. David,

    Right, I should have caught that. I think the alternative would be a direct query using ADO in VBA. I’m actually working on examples for a class that another teacher will teach. When I get them done, I’ll post a note on my blog.



    13 Mar 11 at 9:49 pm

  30. I have setup the Connection precisely the same way other than on the definition in the command type i have SQL and in Command Text I have the actual sql code. My question is how can i setup a bind paramtere in the command text so that i can have the user of the spreadsheet put in the start and end dates and have the command text use those values instead of hard coding them into the text??

    Is it even possible?

    Tom Cusick

    16 Mar 11 at 11:49 am

  31. Unfortunately, you need to write this as a SUB and pass parameters via cell references or VBA form values. Here’s the best reference that I know, and it’s at MSDN. But here’s an untested copy of a FUNCTION that you could call in your SUBroutine:

    Function ORAQUERY(strHost As String, strDatabase As String, strSQL As String, strUser As String, strPassword As String)
      Dim strConOracle, oConOracle, oRsOracle
      Dim StrResult As String
      StrResult = ""
      strConOracle = "Driver={Microsoft ODBC for Oracle}; " & _
             "(ADDRESS=(PROTOCOL=TCP)" & _
             "(HOST=" & strHost & ")(PORT=1521))" & _
             "(CONNECT_DATA=(SERVICE_NAME=" & strDatabase & "))); uid=" & strUser & " ;pwd=" & strPassword & ";"
      Set oConOracle = CreateObject("ADODB.Connection")
      Set oRsOracle = CreateObject("ADODB.Recordset")
      oConOracle.Open strConOracle
      Set oRsOracle = oConOracle.Execute(strSQL)
      Do While Not oRsOracle.EOF
          If StrResult <> "" Then
            StrResult = StrResult & Chr(10) & oRsOracle.Fields(0).Value
            StrResult = oRsOracle.Fields(0).Value
          End If
      Set oRsOracle = Nothing
      Set oConOracle = Nothing
      ORAQUERY = StrResult
    End Function

    If you install Oracle Client software on the same machine as Excel, you can replace the TNS connection strings with the network alias from the tnsnames.ora file.


    16 Mar 11 at 9:39 pm

  32. Tom, To prompt for a parameter, use a ? (tested with Excel sql). For example, instead of WHERE (`Sheet1$`.PROJECT_NAME=’foo’) use WHERE (`Sheet1$`.PROJECT_NAME=?) and you will get a popup for Parameter1. You will also get a selector button allowing you to choose a cell reference, and a couple of useful checkboxes. This may help Perry as well. To use an actual bind parameter against an Oracle function, you will need to investigate passing the OCI syntax via ODBC. I have read about it, but have never tried it. I always use ADODB. :)

    Alan B

    24 May 11 at 12:35 pm

  33. How insert data from spreadsheet Exel 2007 to Tables in Oracle 10G Express Edition?


    11 Jul 11 at 10:13 am

  34. Is it possible to call an Oracle procedure that has out parameters using ADODB?

    I know the code published above lets me query tables etc, but how to use it to call procedures?

    Thanks in advance!


    22 Aug 11 at 12:29 pm

  35. Yes, try this article.


    25 Aug 11 at 11:41 pm

  36. This is very helpful but I need to add that the oracle does not have to reside on the client machine. All you needed to add for the server end connection is the IP address/service name, e.g (where addey is the oracle service name)

    It worked for me..


    7 Oct 11 at 6:14 am

  37. Thanks for the most informative and easy to understand article about connecting Excel to Oracle.

    I wonder is it possible to export xls files to Oracle through ODBC? I mean instead of importing xls files through SQL loader of Oracle, how about exporting the Excel table directly to Oracle from Excel through ODBC. This is easy in Ms Access, but I couldn’t figure it out in Excel.


    24 Oct 11 at 2:50 am

  38. Yes, it’s possible.


    24 Oct 11 at 12:32 pm

  39. Thank you for the informative article, i was desperately looking for some information in this regard. However, I do not have the option of selecting ‘Oracle provider for OLE DB’. i am using MS office 2007, but i don’t know if it was installed as ‘typical’ or ‘complete’ i would really appreciate if you could guide me about this problem.


    9 Nov 11 at 11:21 am

  40. While you want to perform this for Oracle, the steps are pretty much the same for MySQL and they’re already document in this blog post.


    9 Nov 11 at 9:46 pm

  41. Simple and Clear
    Thank you.

    Nahed Nawar

    12 Nov 11 at 10:44 am

  42. Thanks for the prompt reply, I appreciate it. However, I want to know if the option ‘ORACLE provider for OLE DB’ can appear/be installed, as my database is in oracle only and I want to connect it with excel, can’t use MySQL. Is there some solution for this.


    12 Nov 11 at 8:07 pm

  43. Yes, you need to download and install the ODAC product on your Windows 7 machine. It was too much for a comment, so it’s here.


    13 Nov 11 at 1:01 am

  44. […] return to set up an Excel query […]

    ODBC for Excel

    13 Nov 11 at 10:53 am

  45. At #6 I can´t make the connection to succeed. I get a message saying that the listener does know the service requested (my translation).
    I use ‘orcl’ as the data source, anda that’s the name in tnsnames.ora.
    When I use the server name, the error message is: “the SERVICE_NAME was not provided to the listener at the CONNECT_DATA” (my translation)
    Can you help me !

    Many thanks


    16 Nov 11 at 6:30 pm

  46. Very nice one…!

    Is there any way to do the other way around? That is, entering the datas in the excel sheet to the oracle database.

    It would be good if you can come out with it quickly.



    17 Nov 11 at 3:12 am

  47. How can I get data from more tahn 1 queries..??
    For Ex..

    select * from table1;
    select * from table2;

    Thanks in Advance :)


    16 Dec 11 at 2:43 am

  48. You can retrieve data from two tables by joining records through a primary and secondary key columns. The following post provides instructions for performing a query.

    Excel maps a single table to a single worksheet unless a query joins two tables.


    16 Dec 11 at 11:12 pm

  49. Hi, I connect to a database through Citrix.
    Hence I am unable to perform the above steps.
    I always get the data connection error.
    Any alternative for that ?


    15 Mar 12 at 2:12 am

  50. I was successfully able to run a complex query on excel installed on my machine. But when I try to do the same setup and run the same query on another users machine, i get a “command not prepared” error. Any idea why I am getting this error and how I can resolve it?


    19 Jun 12 at 3:43 pm

  51. I’ve not encountered the error before, but it basically means it’s not packaged to send to the database correctly. Why don’t you save the query, close the Excel document, and then retry execution.


    19 Jun 12 at 11:48 pm

  52. I did try that as well.
    – SO I saved the excel workbook where I was able to run the query
    – reopened the same excel book on another user’s machine and got the “Command nor prepared” error


    25 Jun 12 at 3:54 pm

  53. My excel 2007 never bring the screen of step 7, I was able to successfully test the connection as per step 6.

    Any feedback will be helpful?


    9 Jul 12 at 3:10 pm

  54. Can’t seem to get past step #5 above without “ORA-12154”. I am using “Data Source: orlc”, user name and password = “plsql. I looked at my tnsnames.ora file and it is correct. Any other tips?


    18 Jul 12 at 11:33 am

  55. Are you using a hostname in your tnsnames.ora file that’s recognized by the DNS server or that is qualified in your hosts file?

    Do you have a PLSQL user/schema in the database instance?


    18 Jul 12 at 11:57 am

  56. Yes for the host name. Not sure how to check the PLSQL user/schema in the database instance, would this be in the sqlnet.ora file? If so, it is configured as follows:



    19 Jul 12 at 6:22 am

  57. Open a command shell and type the following at the DOS prompt:

    sqlplus plsql/plsql@orcl


    19 Jul 12 at 10:51 am

  58. “sqlplus” is not reconized as a command.I should mentioned that I am launching the command prompt as a user not admin (don’t have admin access)


    19 Jul 12 at 12:52 pm

  59. OK, well the others were from my iPhone. It appears you don’t have the Oracle client software installed. You need to ask your DBA which schema you’re allowed to connect to for queries. A schema is a private work area and most often thought of as a database. The example uses a schema named PLSQL, but if your Oracle instance doesn’t have a PLSQL user/schema the failure is normal.

    Discuss this with the DBA and find the USER name and PASSWORD for the schema where your data exists.


    19 Jul 12 at 2:56 pm

  60. hello maclochlainn

    which part of the TNSNAMES file will be used in the Data Source box? (step5)


    3 Oct 12 at 6:13 am

  61. It’s the TNS alias, which is orcl for the main sample database and xe.


    3 Oct 12 at 9:31 am

  62. Dear Maclochlainn,
    In Step 5, I also encounter error,ORA-12154 TNS could not resolve the connect identifier specified.
    In tnsnames.ora, the instance connection information should be correct, because if I open window command to test,using below to test
    C:\Users\ptian>sqlplus apps/apps@mc3yd213
    It can connect instance successfully.

    But in excel, I always have ORA-12154 error, do you have any idea?

    Thanks in advance


    28 Oct 12 at 8:38 am

  63. Have the same problem as Pan.
    Appreciate if you could find any reason for that.

    Thanks in advance!


    9 Jan 13 at 3:36 pm

  64. Pan & Gleb,

    The ORA-12154 error indicates that the network layer works and typically points to a problem finding the server or that the server is offline.


    16 Jan 13 at 12:00 am

  65. Hello,
    I am trying to connect oracle database to excel. My requirement is to import data(query data) from Oracle database to MS Excel 2010. This oracle database and all related tables are on server. the tnsnames.ora file is all maintained by third party and nothing is local on my machine. I am having a hard time connecting it through excel. Please let me know if any drivers are required to do this. And also would I be needing Oracle client software to do this?


    8 Feb 13 at 2:21 pm

  66. A 32-bit application (Excel), a 64-bit Oracle database. Which driver, 32-bit or 64-bit for ODBC?

    Mike Goins

    25 Feb 13 at 5:20 pm

  67. Either should work because the communication occurs over Oracle’s TNS.


    25 Feb 13 at 5:30 pm

  68. Created a connection using “Microsoft OLE DB provider for Oracle”. Connection test successful; however, not from Excel. Excel is 32-bit, the database and Oracle Client are 64-bit. Installing the Oracle client did NOT create an “Oracle provider for OLE DB” under data sources (it was installed from E:, not C:). OS is 64-bit Windows Server 2008. Error message something like “Oracle Client not installed” or “Oracle Client components could not be found”.

    Mike Goins

    25 Feb 13 at 5:43 pm

  69. “For those who don’t have an Oracle instance running locally, you can download the Oracle Data Access Components for Windows from the Oracle web site.” We DO have an Oracle instance running locally (64-bit), Excel is a 32-bit, on Windows Server 2008. No “Oracle provider for OLE DB” in the data sources. “Test connection” works from Data Sources, but not from Excel. Aggravating and interesting.

    Mike Goins

    25 Feb 13 at 5:56 pm

  70. Great catch, thanks!


    25 Feb 13 at 7:29 pm

  71. Thanks for the responses, Mac! I intend to fix this today, one way or another. Will let you know if I find anything useful. For now, getting the error in Excel “Unable to locate Oracle Client components…”.

    Mike Goins

    26 Feb 13 at 5:45 am

  72. maclochlainn,

    earlier in this thread, you responded to someone asking a question about the “Test Connection.” I have the same error as that person with the error ORA-12154. You told them to type in “xe” instead of “orcl”. I tried that but I still had the same error as before. What other methods would work?



    13 Mar 13 at 9:49 am

  73. thanks a lot


    31 Aug 13 at 4:34 am

  74. Greetings! Very helpful advice within this post! It’s the little changes that will make the biggest changes.
    Thanks a lot for sharing!

    10 Jan 14 at 9:24 pm

  75. After I saved as excel, I unlinked the table so I could use the information as is, with no impact upon the existing data. I wasn’t sure if there would be any issue with DB staying connected. Does this make sense? I was going to send the data to another for information only.

    Thank for your help!

    Allan Menezes

    18 Feb 14 at 1:31 pm

  76. Allan, That’s fine. It’s only a query, which means it won’t change the data. :-) As to sharing, that depends on your company/corporate policy, eh? 😉


    18 Feb 14 at 5:47 pm

  77. Hi, I’ve just setup OLE, on excel 2007, connecting to an oracle 10g database running on a separate server.

    Connection is successful, upto step 7.
    At that point, i see tables, views and synonyms for all users, not just the one I’ve logged in with.

    For info, ive setup the source in ODBC, and then am connecting using excel using the above, but in datasource put the name from ODBC.

    For both ODBC and OLEDB connections i am using a restricted userid.

    Any ideas how i can only bring back the tables from that user/schema?


    20 Feb 14 at 4:49 am

  78. Uncle Bob, You should be seeing the schema that you specified during your connection. At least that’s what I see. Which schema owns the tables and views that you see?


    20 Feb 14 at 8:05 pm

  79. Created connection using “PGNP Native OLEDB Provider for PostgresSQL” but i can’t able to view the result in excel. It’s showing error as “Cannot connect open DB connection”. Can anyone help on this?


    21 Feb 14 at 6:24 am

  80. I am using Excel 2007 and i can able to complete the steps up to 9 but i can’t get the select query result in excel as shown in step 10. i am getting error as “Cannot open DB Connection”. i am using “PGNP Natice OLEDB Provider for PostgresSQL” trail version to connect PostgreSQl in excel 2007. Can you please provide the answer to resolve this issue?


    22 Feb 14 at 3:16 am

  81. Mahi, I’ll try to test it for you but it won’t be until later in the week. Could you confirm where you downloaded the OLEDB?


    22 Feb 14 at 1:32 pm

  82. Maclochlainn, Ok. I have downloaded the OLEDB from here.


    24 Feb 14 at 9:36 am

  83. Mahi, You should download it from EnterpriseDB because they’re the authoratative source. The easy way is to just follow the instructions on my blog for Installing PostgreSQL with pgJDBC v9.3, psqlODBC (32 bit), and psqlODBC (64 bit).


    2 Mar 14 at 9:41 pm

  84. Hi all of you. What do you thing of that:
    This is a complete solution for excel, with 50 last selects in memory, variables, with SQL viewer, and export as pivot table, data table or non format dates.


    9 Mar 14 at 3:43 pm

  85. Hi,

    I did all in this forum, but still getting this error “Accessor is not a parameter accessor”, no error code or else…

    Im using Excel 2013 and Oracle 11G



    6 May 14 at 2:34 pm

  86. Wiston, are you passing parameter values?


    7 May 14 at 2:33 am

  87. Hi Maclochlainn, Thanks too much for your great efforts.
    I have a problem in writing an oracle query. The query is a select statement for ex.( select … from … where condition1 and condition2)
    condition1 debends on excel sheet parameter so I used this mark (?) in it . the problem is that condition2 does not be executed. The output debends on the first condition only and neglect the second condition. Can you help me please.
    Thanks alot


    22 Jul 14 at 8:54 pm

  88. Hi
    I have data in Excel i want that data pull in Oracle Database & same for Delete same type of data from Oracle Database please tell me how to do this Excel Macro


    26 Mar 15 at 7:49 am

  89. Abhijeet,

    You want delete data from the server that meets the condition of a range of values? Does the data contain the surrogate or natural key? If so, you can use that range to write a dynamic array of values and use the WHERE clause like one of these examples.

    Using a single column as the surrogate key:

    WHERE surrogate_key_column IN
           , ...)

    Using a collection of columns as the natural key:

    WHERE (natural_key_column1, natural_key_column2) IN
           , ... )

    Hope that helps. Unfortunately, I’m not in a position to write you an example because I’m too committed to other projects.


    27 Mar 15 at 10:16 pm

  90. Hi, I’m creating some reports in Excel from Oracle’s data. I’ve created 2 sheets and they go to oracle and report soma data from normal views there…. but there’s a thrid view and when i try to use this third, i get the error: cannot execute the query or cannot open the database table. after close this windows, i get the error: there’re problems getting the data. However, the sheet send a little message at the bottom: Connecting to data source…

    Could you help me? there’s some kind of log in order to review what’s the problem?

    Thanks a lot!


    11 May 15 at 5:09 pm

  91. CariS,

    Do you know if the third view uses any DB_LINKS? You can query the third view with the following:

    SET LONG 300000
    SELECT text
    FROM   user_views
    WHERE  view_name = 'UPPERCASE_VIEW_NAME';


    11 May 15 at 6:11 pm

  92. Gracias MacLochlainn, no, every view uses tables in the same db, in fact, if I use a materialized view instead a regular view, it works :( but I need the data on line, no delayed …. thanks for your help…


    12 May 15 at 9:06 am

  93. CariS, Is it a large data source? If it works with materialized views, then it may be a timeout on the request. Have you tried writing a DDL trigger to monitor the connection and timeout?


    12 May 15 at 10:46 am

  94. Hi maclochlainn, no, I’m not, but let me work around this topic, and I’ll let you know, the original universe of data (rows) is the same, however, there is a join for obtain the exact date for begining and ending of the week, in order to clasify and count rows for week and indicating the dates between that data….we’ll see…. thanks!


    12 May 15 at 3:49 pm

  95. CariS, I’ve a hunch from your description that you can optimize the view. If you’ve done that incorrectly, it could take quite a while to resolve. What’s the explain plan look like for the query?


    14 May 15 at 11:35 pm

  96. Hi, i did till 5th step, when i test the connection i am getting as error as “Test connection failed because of an error in intializing provider.

     ORA-12541: TSN:no listener

    but I used CMD command and started TNS listener and its still running.

    I am using Windows 7, 64 bit but excel is 34 Bit. please help me out on this…


    26 May 15 at 2:21 am

  97. Vinod,

    Did you select “Oracle Provider for OLE DB” in step #4? If so, did you follow the steps on configuring the Oracle ODBC driver?

    My guess is that the Oracle-ODBC driver isn’t working. Let me know what you find.


    26 May 15 at 8:20 pm

Leave a Reply