Linker IT Software
Google
Web www.oraxcel.com
menubar-top-links menubar-top-rechts
Home Help Search Login
Welcome, Guest. Please Login.
SQL*XL: Database to Excel bridge litLIB: Excel power functions pack ExcelLock: Locking and securing your valuable Excel spreadsheets encOffice: Protect your Excel file easy and safe encOffice: Protect your Excel file easy and safe
Pages: 1
Oracle connection problems (Read 23277 times)
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Oracle connection problems
31.10.05 at 09:56:17
 
Oracle connection problems
 

 
Question:
I am interested in SQL*XL to connect from Excel to my databases. I have installed SQL*XL but cannot connect to the Oracle database while I can connect using SQL*Plus or Designer2000.
 
Answer:
This is almost certainly linked to the setup problems with the OO4O driver. I have also experienced these problems in situations where there are more than one Oracle home installed on a PC. It seems that OO4O can only connect to databases defined in the first Oracle home in the path. Each Oracle home enters its directory in the path variable on your PC.
 
You can take the pragmatic decision to use a different driver. Switching to an OLE DB driver - also installed as standard on your PC - almost certainly fixes the problem. See:
http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/oracle_oledb.ht ml
 
You can also try to edit the path variable on your PC to switch the oracle directories.
 
Further, you can try to use the Oracle utilities to create a new database alias for your database. If you manually edit the tnsnames.ora file you may forget to edit some other configuration files.
Back to top
 
« Last Edit: 13.10.07 at 23:06:51 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle connection problems
Reply #1 - 03.02.06 at 13:26:29
 
Some default passwords for Oracle databases that may be useful:
 
UserName - Password
scott - tiger
sys - change_on_install
system - manager
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle connection problems
Reply #2 - 07.04.06 at 09:45:19
 
Frederique from France has problems connecting to Oracle today. Perhaps it is a good idea to summarise here how you can connect to Oracle:

  • OO4O: Oracle Objects for OLE
    Documentation: http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/oracle_oo4o.htm l
     
    Usually OO4O is installed with the Oracle Client software. If it is not on your PC you can install it by rerunning the Oracle Client installer. If you cannot find OO4O or if you cannot run the Oracle Client installer you can get OO4O from Oracle at technet.oracle.com. They sometimes change the url but currently it is at:
    http://www.oracle.com/technology/software/tech/windows/ole/index.html
     
    Now, Oracle only provides OO4O for supported Oracle versions here. I heard you can get previous versions from Metalink. You can try to call Oracle support to get OO4O for previous releases. I have my personal collection of OO4O installers going back to Oracle 7.
     
    One drawback I frequently hear is the size of the OO4O installer. Not uncommon to be 100MB or more. The OO4O driver for Oracle 10g is 170MB I believe.  
  • OLE DB
    Documentation: http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/oracle_oledb.ht ml
     
    This is my favorite. It is easy to setup connections, you are guaranteed to have the OLE DB drivers already installed as they come with SQL*XL. There are two flavors. There is an Oracle OLE DB Provider and there is the Microsoft OLE DB Provider. Note that Provider is how Microsoft calls a driver in ADO...
     
  • ODBC
    Documentation: http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/oracle_odbc.htm l
     
    ODBC is getting old but it still works fine. It is clumsy to setup but generally they work fine. ODBC is also good to use when trying to connect to really old Oracle databases like version 7 databases.
     
  • IC: Instant Client
    Documentation: http://www.oraxcel.com/projects/sqlxl/help/techniques/connecting/oracle_ic.html
     
    This is a little gem actually. Based on ODBC the IC offers conenctivity to Oracle without installing the always huge Oracle Client. If you don't want to spoil your system with tons of Oracle dlls this is for you. There is no installer. The IC comes in a zip file. You just unzip and it works. Great!

 
There are some common problems with setting up Oracle connections:

  • TNS Names / Database Aliases
    There is a little file called tnsnames.ora. In this file you setup a nickname for your database called the tns name or database alias. It is best if you use an Oracle tool to edit this file. Use the Oracle configuration tools to setup a new database alias.  
    Basically you can give your Oracle database a nick name. In the config you put the location of your database, which ports it connects on and the instance names. From then on you can just use the nick name on the PC with the user name and password.
     
    To test your tns name use the DOS command tnsping: tnsping tnsname
  • Multiple Oracle homes
    If you have multiple Oracle homes installed you may have the difficulty that Oracle looks at the wrong tnsnames.ora file. Look at the path variable on your PC. If it contains multiple paths to oracle/bin this may be your problem. Reorder the oracle/bin directories in the path. Place the oracle/bin directory of the Oracle Client you want to use at the beginning of the path usually solves the problem.
Back to top
 
« Last Edit: 07.04.06 at 10:02:24 by Gerrit-Jan Linker »  

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle connection problems
Reply #3 - 19.05.06 at 08:42:19
 
Viesta from China had also some connection problems.  
 
The problems had to do with configuring the TNS Names and still not being able to connect to the database with SQL*XL. He reports that adding the evironment variable TNS_ADMIN worked for him.
Back to top
 
 

Gerrit-Jan Linker
Linker IT Software
Email WWW Gerrit-Jan Linker   IP Logged
Gerrit-Jan Linker
YaBB Administrator
*****




Posts: 75
Re: Oracle connection problems
Reply #4 - 29.07.10 at 10:04:07
 
A user reported that all connection attemps by the connection wizard failed. This is possibly when entering a database name that does not exist or for which there is not entry in the TNS names file [Oracle_home]\admin\tnsnames.ora
 
Quote:

OO4O: ORA-12154
OLEDB MS: ORA-06413
OLEDB Oracle: ORA-12154
OLEDB Oracle OS Auth: ORA-12154
OLEDB Oracle Trusted: ORA-12154
ODBC Microsoft: ORA-06413
ODBC Microsoft: (error message in Spanish...)
ODBC Oracle: (error message in Spanish...)

 
Advice:  
 
  • Ensure that there is an entry in the tnsnames.ora file. You can use the DOS command tnsping to verify that the tnsname is working.
     
  • Verify that the tnsnames.ora file has the alias and the service name.  
     
    TNS entry for the Oracle database  
    ===========================  
     OracleDB_Dev =  
      (DESCRIPTION =  
        (ADDRESS_LIST =  
         (ADDRESS = (PROTOCOL = TCP)(HOST = server01.mydomain.com)(PORT = 1521))  
        )  
        (CONNECT_DATA =  
         (SERVICE_NAME = OracleDB)  
         (SERVER = DEDICATED)  
        )  
      )  
     
     
    In the above tnsnames.ora file Alias = OracleDB_Dev  
    Service Name: OracleDB (Actual Oracle service name [instance name in SQL])  
     
  •  Check the sqlnet.ora file under ‘Admin’ folder in Oracle home [Dir:\app\product\11.1.0\client_1\network\admin] and ensure that we have TNSNames in NAMES.DIRECTORY_PATH  
     
    NAMES.DIRECTORY_PATH= (TNSNAMES, ONAMES, HOSTNAME)  
     
  • Verify if you can connect to Oracle from other tools installed with Oracle Client [For example "SQL Developer" or “SQL Plus”] with the same user id/password or TNS alias.  
     
  • Check if the environment variable 'PATH' has the path for tnsnames.ora file specified.  
     
    Sample Value of Environment Variable PATH:  
     
    E:\app\product\11.1.0\client_1\bin;C:\Program Files\Business Objects\Common\3.5\bin\NOTES\;C:\Program Files\Business Objects\Common\3.5\bin\NOTES\DATA\;%Systemroot%\Microsoft.NET\Framework\v1.1.432
    2;%SystemRoot%\system32;%SystemRoot%;%SystemRoot%\System32\Wbem;C:\Program Files\Dell\SysMgt\oma\bin;C:\Program Files\Microsoft SQL Server\80\Tools\BINN;C:\Program Files\Common Files\Microsoft Shared\web server extensions\60\TEMPLATE\ADMIN\1033\;C:\Program Files\Microsoft SQL Server\80\Tools\Binn\;C:\Program Files\Microsoft SQL Server\90\DTS\Binn\;C:\Program Files\Microsoft SQL Server\90\Tools\binn\;C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\;C:\Program Files\Microsoft Visual Studio 8\Common7\IDE\PrivateAssemblies\;C:\Program Files\Microsoft Network Monitor 3\  
     
    Note: make sure that the path is a valid path and there is no space.  
     
  • Check the value of the key  ”Oracle_Home” in the registry under  HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\KEY_OraClient11g_home1  and verify that it has the right path for the Oracle home.  
     
  •  Check for the registry key “TNS_ADMIN” at HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE. If it exists then make sure it has the right value as “Dir:\app\product\11.1.0\client_1\network\admin”. If you don’t see the key then create the key and set appropriate value as below.  
     
    Regedit->HKEY_LOCAL_MACHINE->Software->Oracle->RightClick NEW->StringValue and name  
    it TNS_ADMIN and give the value  “X:\app\product\11.1.0\client_1\network\admin”  
     
    Note: This is not a must but in some cases this is what fixed the issue.  
     
  • Make sure you don’t have multiple Oracle homes or multiple Oracle clients installed. Check the "HKEY_LOCAL_MACHINE\SOFTWARE\ORACLE\ALL_HOMES\HOME_COUNTER" key value.  
     
  • Try to connect to Oracle using the UDL.  Use the same TNS name. If you get the same error that means the issue is not specific to SQL*XL.  
     
    Creating and Configuring Universal Data Link (.udl) Files  
     
    http://msdn.microsoft.com/en-us/library/e38h511e(VS.71).aspx
     
  •  Try to specify all the information in the data source instead of using the TNS alias to connect to the Oracle database (this is a way to bypass tnsnames.ora file when connecting to Oracle).  
     
    Sample Data Source:  
     
    Data Source=(DESCRIPTION=(CID=GTU_APP)(ADDRESS_LIST=(ADDRESS=(PROTOCOL=TCP)(HOST= server01.mydomain.com)(PORT=1521)))(CONNECT_DATA=(SID=OracleDB)(SERVER=DEDICATED
    )));
     
    Useful links:
    ORA-12154 when trying to connect
    http://www.oraxcel.com/cgi-bin/yabb2/YaBB.pl?num=1140092715
    “ORA-12154: TNS: could not resolve the connect identifier specified” error while creating a linked server to Oracle
    http://blogs.msdn.com/b/dataaccesstechnologies/archive/2010/06/30/ora-12154-tns- could-not-resolve-the-connect-identifier-specified-error-while-creating-a-linked-server-to-oracle.aspx
  • Back to top
     
    « Last Edit: 21.08.13 at 11:04:34 by Gerrit-Jan Linker »  

    Gerrit-Jan Linker
    Linker IT Software
    Email WWW Gerrit-Jan Linker   IP Logged
    Pages: 1