Posts in Category: Oracle

Create Oracle Linked server in SQL Server (64 bit)

One of the most misguided series of posts are those discussing how to create a linked server connection to Oracle on a 64-bit installation of SQL Server.  The one that gets you there if no other problems exist is found here by David Browne:  (however, the guidance on that page is generic and uses locations specific to their example – below modifies those directions to a more generic version to any installation).

At large company installations it is more usual that there is a common LDAP or TNSNAMES.ora file managed by a corporate infrastructure team that resides somewhere on the network.  In those cases, using the alias for the connection is all that is needed (as the service name, port and server name are all mapped within the alias entry).  I find that using these corporate managed sources for the aliases is the safer approach for individual installs by allowing he infrastructure team to abstract the actual connection parameters which decouples the local install and relieves the local person from having to keep up with the physical location of their server on the network (especially useful when there are clustered servers or backup servers that can be modified by the infrastructure team at a moment's notice to accommodate other priorities).

Other setup should already be in place before working through this approach to fix a failed installation, as this approach relies upon advanced and potentially dangerous modification of the PC's registry to eliminate prior failed attempts.  Do not work with the registry without having a full backup of the system to restore to the condition it is in prior to working with the registry!  Prior to that action, be sure that other setup conditions have been examined:

  1. Create an empty file and name it Test.UDL.  Once created, click to open it, then navigate to the first tab 'Provider'.  If "Oracle Provider for OLE DB" appears, then the provider is already installed.  If not, turn off any antivirus / root protection services before attempting to install the driver as noted in the blog by David Browne. 
  2. Make sure that the installed location of the Oracle Client and its bin directory are found in the Environment Path variable - preferably towards the start of the Path variable.  On servers where multiple people may have remote login access, it is important to have only one installatoin of Oracle Client that everyone uses.  I've found that if another user installs Oracle Client after the server has been setup for linked servers, that later installation notoriously confuses the system and lots of different types of error messages occur.  I prefer using a common location rather than in a person's user directory (which is the Oracle install default appraoch).  In this example, I'll use C:\App\OracleClient\product\11.2.0\Client_1 and C:\App\OracleClient\product\11.2.0\Client_1\bin.
  3. Remove the Oracle_Home from the environment variable if using LDAP as the location for the alias listing.  If you are using a corporate TNSNAMES.ora file, be sure to map it using UNC naming convention, not as a mapped drive (e.g.  \\[servername]\[some directory path statements].  Another tip, be sure to check that the UNC path that you put actually opens the folder where the TNSNAMES.ora exists (too often folks type this in and when checking with windows explorer, what they typed in actually doesn't get to the right directory -- be safe, just paste what you type into windows explorer, if it works, no harm, no foul.  If it doesn't, back off the later parts of the UNC until something does appear in windows explorer and then 'walk' down the directory tree to find the right directory.  Copy that from the windows explorer address bar and use that in the Oracle_Home environment variable.
  4. Don't forget to Reboot -- the Test.UDL won't show the new provider until the reboot is performed.
  5. If the new provider doesn't show up in Test.UDL -- check with Regedit whether the dll was registered (search for OraOLEDB*.dll and it should pick up the .dll -- ignore any other items).  If the dll isn't found in the registry, then you must manually register the dll.  So, for instance, if you installed the v11 ODAC to C:\App\Oracle\product\11.2.0\client_1\BIN, then the dll would be OraOLEDB11.dll.  With windows explorer, right mouse on the file, select properties -- see if in the General tab there is a message: "This file came from another computer and might be blocked to help protect this computer."  If so, click the 'Unblock' button.  Then, open a command prompt with administrative privileges and run this command (change the path and file name as appropriate for your install location and version)  %systemroot%\System32\regsvr32.exe C:\App\Oracle\product\11.2.0\client_1\BIN\OraOLEDB11.dll .  Once that is complete, once again check the Test.UDL and registry.  The provider should show up as an option and the registry search on the specific filename should find a hit.
  6. Once the Provider is found with Test.UDL, open SSMS and change the properties of the provider to "Allow InProcess" and "Dynamic Parameter".
  7. Add the Linked Server using connection information as you would for any other Oracle product.

Oracle SQL - check for no table, no rows

I was recently tasked with an interesting issue.  The report that I created connected to a database table that would be refreshed regularly.  During the refresh process (sometimes taking minutes, sometimes longer), the report would fail to show any data, since the table was empty at that exact moment.  I needed a means to determine if the report had no rows, then I would display a message that the data was being updated.  The report was then to be migrated to another database, where the table didn't even exist yet.  In this case, I wanted to also display the same wait message.  Oracle doesn't have an obvious method to determine if a table exists.  Attempting to run Oracle SQL against a non-existent table results in an Oracle 904 error. 

I figured out that this code will do the trick, whether the table exists or not and whether the table has any rows in it or not.  It returns a binary 1 if the table doesn't exist or doesn't have any rows in it, otherwise a zero is returned.

   CASE WHEN (select num_rows from all_tables where table_name = '<your table name>') IS NULL
   THEN 1
   ELSE 0
from dual


Copyright© Brad Earle
Site Map | Printable View | © 2008 - 2021 KB Earle Associates LLC | |