VBA Script to run SQL in Oracle SQL Developer

[thisguy4000]

I need to be able to query an Oracle database with an SQL script through SQL Developer, then import the contents of that query into a new spreadsheet in Excel.

This seems like it would be possible to do, but I have no idea to go about coding this.

[Gregor y]

Do you need to specifically go through SQL Developer, or can you use an ODBC connection instead?

[thisguy4000]

I can use an ODBC connection.

My first problem is the error message:

Oracle client and networking components were not found. These components are supplied by Oracle Corporation...

We have an Oracle DB at work. I can connect through SQL developer and query, but I get this error message in Excel. I am not exactly sure what I need to install on my computer. Your help would be greatly appreciated!

[Gregor y]

Getting Oracle and Excel to work togeather can be a bit tricky.

Everything must be on the same page as far as 32-bit vs 64-bit, so if you have 32-bit Excel you must also have the 32-bit oracle client and setup the ODBC using the 32-bit setup (in win7 it's under C:\Windows\SysWOW64\odbcad32.exe). Otherwise the peices may work individually but not collectively.

Do you have a DSN setup on your machine for the Oracle database?

[thisguy4000]

I have win7 but there is no C:\Windows\SysWOW64 folder.

Not sure what a DSN setup is...

[thisguy4000]

I am connected to the Oracle database with a 32-bit Excel and 32-bit oracle client thru DSN.

Please let me know what I can use to run the script.

[Gregor y]

sorry about that i'm getting my terminology a little mixed up DSN is the microsoft Data Source Name, it's what's listed in the name column in the ODBC setup.

when you right click on the database connection in SQL developer and select properties, is the connection type setup as TNS, if so then you should be ready to setup ODBC in windows using the Network Alias listed there.

since you're not seeing the SysWOW64 folder, my guess is that you're running 32-bit Win7 you can use this link to check and make sure windows is 32-bit: http://windows.microsoft.com/en-us/w...t-32-or-64-bit

if it is then you can just run odbcad32.exe without specifying the path, since Win7 being 32-bit automatically means all the other pieces are too.

then under the ODBC setup click on add, depending on the version and level of the oracle client installed on your machine at the bottom of the list you may see a specific driver that was installed by oracle, mine says "Oracle in OraClient11g_home1". You can choose this driver, however in this case I recommend choosing the driver that ships with windows "Microsoft ODBC for Oracle" and clicking on the 'finish' button. Typically I've seen better performance out of the MS driver than the oracle one; however the MS driver is kinda old and will not run some of the newer oracle SQL code for example oracle's PIVOT statement, and if you need the new stuff to run then you should use the driver provided by oracle.

once you've made that decision you can put anything you like in the Data Source Name(DSN) and description, your user name is just that, and the "Server" or "TNS Service Name" is the Network Alias from above. Although I would suggest not putting any spaces in the DSN.

once you have that working for you, you can go into Excel and start recording a macro, then goto the Data tab under the get external data group click on the from other sources list button, and choose the ODBC option and use the new DSN you just built, use the query wizard to do any query, then close/save the query and pull up the VBA code you recorded or modify my over-worked code sample below.

Please Login or Register  to view this content.

[thisguy4000]

Hi thanks so much for your help! I managed to get it working using the Querytables object and add method - only problem now is that the .CommandText argument must have a character limit. I have queries that are 800 chars long that need to run, unfortunately. Let me know if you have any suggestions.

[Gregor y]

I'm not sure if .CommandText has a character limit, but I do know that VBA strings do.

Which may be why I've seen things like

Please Login or Register  to view this content.