Arrhhh!! I RAN INTO THIS AGAIN!!!
Just install ToadForOracle in C: or any directory without parenthesis in the path.
In my case its because I was on a x64 PC and still using the old Oracle 9i with the 32bit drivers!
I am using SQL Reporting Services with an Oracle Database. The problem is the brackets in the path to Visual Studio (BIDS). Oracle doesn’t like apps that start in a path with brackets:
RDBMS 10g XE problem with parenthesis in path
So I made a BAT file to open Visual Studio with Progra~2 as the short path name for «Program Files (x86)».
Here is the contents of the BAT file:
rem Progra~2 is short path name for "Program Files (x86)" and works around an Oracle client bug that doesn't like the ()'s in the path
start /B "C:Progra~2Microsoft Visual Studio 9.0Common7IDE" "C:Progra~2Microsoft Visual Studio 9.0Common7IDEdevenv.exe"
I name this BAT file StartBIDS.BAT and put it in the directory:
«C:Program FilesMicrosoft SQL ServerStart BIDS.bat»
Then I make a short cut to the BAT file on my Desktop and also my Start Menu and change the ShortCuts icon. This allows me to open TOAD, Visual Studio, BIDS and etc apps that I use to work with Oracle.
Update:
Alternatively make a Junction:
mklink /J "C:Program Files (x86)" "C:Program Files x86"
Then remove the brackets in the shortcut:
Issue
How to troubleshoot Oracle Connection errors.
Oracle drivers require very specific connection statements in a unique format, though a TNSNames.ora file may not always be required. For instance, if you have installed only the Tableau-provided Oracle files and do not have a stand-alone Oracle client, the Oracle error messages will still refer to the TNSNames.ora file, making troubleshooting complicated.
Environment
- Tableau Desktop
- Tableau Server
- Oracle
Resolution
Often, correcting route or naming syntax in the Advanced Oracle Connection dialog box or using your full .WORLD database name resolves most Oracle connection issues. If your connection error requires more troubleshooting, refer to the five common connection errors listed below.
- ORA-03113: end-of-file on communication channel
- ORA-12154: TNS: could not resolve the connect identifier specified: HOST value incorrect or Global name incorrect or unknown
- ORA-12514: TNS listener does not currently know of service requested in connect descriptor: SERVICE value incorrect
- ORA-12541: TNS: no listener: PORT value incorrect
- ORA-00932: inconsistent data types
ORA-03113: end-of-file on communication channel
ORA-03113 is a catch-all type error for any problem interrupting an Oracle session. There can be numerous causes for this error. Please refer to the list below for some troubleshooting guidance.
- Refer to Oracle documentation specific to this error: My Oracle Support.
- Refer to Oracle’s B Troubleshooting Common Errors page.
- Oracle recommends that you check for network problems and review the SQL*Net setup.
- If you are connecting to Oracle 9.2.0.5, in many cases the primary cause of this error is Oracle bug 3010227. Ask your Oracle database administrator to apply Oracle patch 9.2.0.6 or another patch appropriate for your server.
- Set the Oracle initialization parameter ‘STAR_TRANSFORMATION_ENABLED’ to FALSE.
- Test changing the scheduled time of the extract refresh
- Alternatively, if you would like to test this issue further follow the optional procedure listed below.
Step 1
From the Tableau Desktop start page, select Connect to Data.
Step 2
On the Connect page, click Oracle, then click OK.
For more information about completing the connection steps, refer to the Oracle Database topic in the Desktop Help.
Step 3
- In the join area, hover over the Custom SQL table until the edit icon displays, and then click the icon.
- Copy the query in the Edit Custom SQL dialog box.
SELECT "NumericBins", "Key" as "Key",
"NumericBins", "Measure E-2" AS "Measure E-2",
"NumericBins", "Measure E-1" AS "Measure E-1",
"NumericBins", "Measure E+0" AS "Measure E+0",
"NumericBins", "Measure E+1" AS "Measure E+1",
"NumericBins", "Measure E+4" AS "Measure E+4",
"NumericBins", "Measure E+7" AS "Measure E+7"
FROM "TestV1", "NumericBins" "NumericBins"
Where «TestV1» is the name of your connection in Tableau.
Step 4
In a SQL session connected to this database, paste and run the query. The expected response is error ORA-7445: exception encountered: core dump, which confirms that the problem is ORA-3113, as expected.
ORA-12154: TNS: could not resolve the connect identifier specified
ORA-12154 occurs when the transparent network substrate (TNS) cannot resolve the service name. The service name is specified in the TNSNames.ora file, which is located in your %ORACLE_HOME%networkadmin folder. Most often, this error occurs when information in the TNSNames.ora file is incorrect. For example:
- The .world extension is not included on the database name.
- The SERVICE_NAME or HOST variable is incorrect.
To resolve this issue, try one of the three following troubleshooting options, in the order listed below.
- Option 1: Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora
- Option 2: Error «ORA-12154» Connecting to Oracle When Not Using TNSNames.ora
Option 1: Edit TNSNames.ora
Provide the full database name, including the .world extension in both of the following locations:
- The TNSNames.ora file.
And
- The Server text box of the Connect page.
Option 2: Ensure that Tableau Server Run As User account has permissions to TNSNames.ora (Tableau Server only)
If you have Tableau Server installed, complete the procedure below to ensure that the Tableau Server Run As user account has permissions to the location of the TNSNames.ora file. If the Run As user account does not have permissions, Tableau Server is unable to access the Oracle Data source details.
Step 1
Verify the location of the TNSNames.ora file, or the equivalent SQLNET.ora and LDAP.ora files on the machine.
Note: By default, the TNSNames.ora file is located in <oracle-directory>networkadmin directory. For example, C:Oracle_Clientnetworkadmin.
Step 2
Confirm that the TNS_ADMIN variable points to the location of the file or files described in step 1.
Note: To check the TNS_ADMIN variable, click the Start button, and select Control Panel > System. Click Advanced system settings, click the Advanced tab, and click Environmental Variables button.
The system variable file path must be in UNC format.
Step 3
Open TSM in a browser: https://<tsm-computer-name>:8850 For more information, see Sign in to Tableau Services Manager Web UI.
Step 4
Click the Security tab, and then click the Run As Service Account tab.
Under Server Run As User, copy the information in the Username field.
Step 5
Go to the folder where the TNSNames.ora file is located.
Step 6
Right-click the folder and select Properties. Click the Security tab and click the Edit button.
Step 7
Under Group or user names, click the Add button.
Step 8
In the Enter the object names to select text box, paste the details of the Run As User account you copied in step 6.
Step 9
When finished, click OK.
Step 10
In the Permissions area,ensure that the Full control and Modify check boxes are selected.
Step 11
Click OK to close the dialog boxes.
Option 3: Verify that all information in TNSNames.ora is correct
If the above troubleshooting steps do not resolve the issue, continue reading and complete the procedure to verify the other information in the TNSNames.ora file is provided correctly.
An example of a TNSNames.ora file is shown here:
QAORCL10.world =
(DESCRIPTION =
(ADDRESS_LIST =
(ADDRESS = (PROTOCOL = TCP)(HOST = MY_HOST_NAME)(PORT = 1521))
)
(CONNECT_DATA =
(SERVICE_NAME = MY_SERVICE_NAME)
)
)
The three variables of interest in the file are HOST, PORT, and SERVICE_NAME. Copy these variables from the TNSNames.ora file and keep them available. These variables are case sensitive.The following steps describe how to provide these variables for your connection.
Step 1
From the Tableau Desktop start page, select Connect to Data.
Step 2
On the Connect page, click Oracle.
Step 3
Provide the following information from the TNSNames.ora file:
- In the Server name text box, type the HOST name.
- In the Service text box, type the SERVICE_NAME.
- In the Port text box, type the PORT number.
- Specify whether to use Windows Authentication or a specific user name and password, and then click Connect.
Note: Variables are case sensitive.
Step 4
Select a schema from the Schema drop-down list, drag a table to the join area, and then click Go to Worksheet.
Step 5
Complete the steps in the Setting an Oracle Connection to Use TNSNames.ora or LDAP.ora article.
Important:
- Make sure that you save the TNSNames.ora file you use in ASCII encoding. Any other encoding besides ASCII, for example UTF-8 or Unicode, causes the ORA-12154 error message.
- These steps are usually required even if the Oracle software is already installed on the machine.
Step 6
Download and install the appropriate Oracle drivers from the Tableau Drivers page. Even if an Oracle driver is installed on your computer, it could be incompatible with Tableau and will require the version specified on the Drivers page.
ORA-12514: TNS listener does not currently know of service requested in connect descriptor
Typically this error occurs when the SERVICE value is incorrect.
To resolve this issue, find out what the correct SERVICE value is, open the TNSNames.ora file located in your %ORACLE_HOME%networkadmin folder. Refer to the steps under ORA_12154 if necessary.
ORA-12541: TNS: no listener
Typically this error occurs when the PORT value is incorrect.
To resolve this issue, replace the PORT value with either 1521 or 1526. Try the value that is currently not in use.
ORA-00932: inconsistent data types
This error occurs when connecting to Oracle or when creating an extract from an Oracle data source. Typically this error is caused by the installation of incorrect Oracle drivers.
To resolve this issue, install the correct Oracle drivers from the Drivers page for the version of Tableau you are using.
In addition to the above common errors, if you are using Tableau Desktop/Server 2020.2 or later and experiencing performance issues e.g. extract refresh taking long time, you can try downloading and installing the Oracle OCI driver. Refer to the article in Related Links. Driver can be downloaded from here.
Additional Information
Suggestions
If you do not have an Oracle Client installed on your machine, be sure to get the necessary files from your database administrator. If the Oracle data connection errors persist, do the following:
- Check the TNSNames.ora folder path used to create the TNS_ADMIN variable.
- Restart your machine to ensure that the TNS_ADMIN variable is recognized.
- Check that the Oracle connection name used in Tableau exactly matches the TNSNames.ora Net Service Name entry. This name is case sensitive.
- In some cases Windows will need to be restarted before the Oracle driver will pick up the TNS_ADMIN system variable
- Contact local IT to verify that the TNSNames.ora file is current.
- If the Oracle connection uses LDAP, make sure to include the SQLNet.ora file as well as the TNSNames.ora file.
ORA-12154: TNS:could not resolve service name error occurs when the connect identifier used to connect to a database or other database service could not be resolved into a connect descriptor using one of the naming methods configured. This issue might be caused by a number of factors, which makes it difficult to address. Users may need to test more than one approach before finding the underlying cause of the ORA-12154: TNS:could not resolve service name.
If the issue is occurring on multiple PCs after attempting to connect to the network, the target database server may have gone down for a short period of time. Connection problems might potentially be at cause, or the destination name could differ from the input address. This error ORA-12154: TNS:could not resolve service name indicates that the Oracle client was unable to connect to the listener on the requested database server.
Root Cause
A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.
Solution 1
There could be error in connect identifier. Revisit the parameters in the connect identifier. If any configuration is misspelled, correct it. The connect identifier should be as per the connect descriptor in the database. Check the following parameters.
host name / ip :
port number. :
service name /sid :
username. :
password. :Solution 2
Check the oracle database is running. The database may be shutdown temporarily or maintenance is going on. Check with the database administrator about the health of the database. If you are using the database installed locally, you can run the below command to check the database status
sudo service oracle status
sudo service lcnrctl statusSolution 3
Check the network connectivity between the oracle client and the oracle database server. If any network issue or IP address or host name change will cause the issue. The DHCP configuration might reroute the connection. If any network issue occurs, fix the network issue. This will resolve the error.
Solution 4
If you are using local naming files, do the following actions
- Make sure that “TNSNAMES” is listed as one of the values of the NAMES. DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA)
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
Solution 5
If you are using LDAP naming configurations, do the following actions
- Verify that “LDAP” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Verify that the LDAP directory server is up and that it is accessible.
- Verify that the net service name or database name used as the connect identifier is configured in the directory.
- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier
Solution 6
If you are using the easy connect naming configurations, do. the. following action
- Verify that “EZCONNECT” is listed as one of the values of the NAMES. DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Make sure the host, port and service name specified are correct.
- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
This is an old question but Oracle’s latest installers are no improvement, so I recently found myself back in this swamp, thrashing around for several days …
My scenario was SQL Server 2016 RTM. 32-bit Oracle 12c Open Client + ODAC was eventually working fine for Visual Studio Report Designer and Integration Services designer, and also SSIS packages run through SQL Server Agent (with 32-bit option). 64-bit was working fine for Report Portal when defining and Testing an Data Source, but running the reports always gave the dreaded «ORA-12154» error.
My final solution was to switch to an EZCONNECT connection string — this avoids the TNSNAMES mess altogether. Here’s a link to a detailed description, but it’s basically just: host:port/sid
http://www.oracledistilled.com/oracle-database/oracle-net/using-easy-connect-ezconnect-naming-method-to-connect-to-oracle-databases/
In case it helps anyone in the future (or I get stuck on this again), here are my Oracle install steps (the full horror):
Install Oracle drivers: Oracle Client 12c (32-bit) plus ODAC.
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/utilsoft-087491.html ):
i. winnt_12102_client32.zip
ii. ODAC112040Xcopy_32bit.zip
b. Run winnt_12102_client32client32setup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to your ODAC112040Xcopy_32bit folder.
d. Enter the command: install.bat all C:OracleOracle12 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12networkadmin *
ii. C:OracleOracle12product12.1.0client_1networkadmin *
Install Oracle Client 12c (x64) plus ODAC
a. Download and unzip the following files from http://www.oracle.com/technetwork/database/enterprise-edition/downloads/database12c-win64-download-2297732.html and http://www.oracle.com/technetwork/database/windows/downloads/index-090165.html ):
i. winx64_12102_client.zip
ii. ODAC121024Xcopy_x64.zip
b. Run winx64_12102_clientclientsetup.exe. For the Installation Type, choose Admin. For the installation location enter C:OracleOracle12_x64. Accept other defaults.
c. Start a Command Prompt “As Administrator” and change directory (cd) to the C:SoftwareOracle ClientODAC121024Xcopy_x64 folder.
d. Enter the command: install.bat all C:OracleOracle12_x64 odac
e. Copy the tnsnames.ora file from another machine to these folders: *
i. C:OracleOracle12_x64networkadmin *
ii. C:OracleOracle12_x64product12.1.0client_1networkadmin *
* If you are going with the EZCONNECT method, then these steps are not required.
The ODAC installs are tricky and obscure — thanks to Dan English who gave me the method (detailed above) for that.
The Oracle message known as “ora-12154” may be the most common error experienced by Oracle users. Luckily, the frustration of so many previous users has prompted some fairly straightforward solutions to the issue.
The Problem
When you receive this error, it means that the Oracle client has failed to connect with the listener on the desired server. In Oracle’s own words:
“A connection to a database or other service was requested using a connect identifier, and the connect identifier specified could not be resolved into a connect descriptor using one of the naming methods configured. For example, if the type of connect identifier used was a net service name then the net service name could not be found in a naming method repository, or the repository could not be located or reached.”
There can be many possible causes for this problem, which is why it can be tricky to solve. Users may have to test more than one method before identifying the source of the ORA-12154.
The target server could have temporarily gone down, which is likely if the issue is occurring across multiple PCs after attempting to access the network. Connection issues could also be the culprit, or the destination name may not match the input address.
It should be noted that if the ORA-12154 error is identifying in a set along with an additional error titled “TNS-03505: failed to resolve name,” the problem should have occurred before the server was located.
The Solution
For those using local naming (TNSNAMES.ORA file), Oracle recommends the following actions:
- Make sure that “TNSNAMES” is listed as one of the values of the NAMES.DIRECTORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Verify that a TNSNAMES.ORA file exists and is in the proper directory and is accessible.
- Check that the net service name used as the connect identifier exists in the TNSNAMES.ORA file.
- Make sure there are no syntax errors anywhere in the TNSNAMES.ORA file. Look for unmatched parentheses or stray characters. Errors in a TNSNAMES.ORA file may make it unusable.
For those using directory naming:
- Verify that “LDAP” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Verify that the LDAP directory server is up and that it is accessible.
- Verify that the net service name or database name used as the connect identifier is configured in the directory.
- Verify that the default context being used is correct by specifying a fully qualified net service name or a full LDAP DN as the connect identifier.
And, finally, for those using easy connect naming:
- Verify that “EZCONNECT” is listed as one of the values of the NAMES.DIRETORY_PATH parameter in the Oracle Net profile (SQLNET.ORA).
- Make sure the host, port and service name specified are correct.
- Try enclosing the connect identifier in quote marks. See the Oracle Net Services Administrators Guide or the Oracle operating system specific guide for more information on naming.
Consulting an experienced firm for their expertise can be vital when dealing with the large-scale implementation of data management systems.