Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Oracle ODBC

Posted on 2011-10-25
46
Medium Priority
?
952 Views
Last Modified: 2013-01-21
I am able to connect to Oracle at the CMD screen with userid SYS and my password. I can run SQL commands like SELECT and  DESC. So my TNSNAMES.ORA file must be good.

When I try to connect through ODBC, it doesn't work. I get the Oracle ODBC Driver Connect and put in the UID and PW. I get:

"ORA-12154: TNS:could not resolve the connect identifier soecified"

I am running Windows XP Professional. I installed the Oracle instantclient 11.2. I have the environment variable set.

Do I need other *.ORA files before the ODBC links will work? Like LISTENER.ORA?
0
Comment
Question by:jkasavan
  • 19
  • 16
  • 8
  • +1
44 Comments
 
LVL 74

Expert Comment

by:sdstuber
ID: 37030186
Is the odbc  dsn setup with the same tns alias you're using from the command line?

is odbc using the same oracle home you use from command line?


odbc doesn't require extra *.ora files
0
 
LVL 23

Expert Comment

by:OP_Zaharin
ID: 37030208
- which provider that you use? Microsoft ODBC for Oracle or Oracle in OraDB...? both requires you to enter the 'Server' or 'TNS Names Service' which refers to the database alias/service name entry in the TNSNAMES.ORA.

- from command prompt, try the following and paste what it returns:
c:\> tnsping servicename
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37030547
>>I have the environment variable set.

What environment variable?

To repeat a question above slightly differently:  Do you have more than one Oracle product installed?
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 2

Author Comment

by:jkasavan
ID: 37038635
sdstuber -

In my Oracle ODBC Configuration I only have a User DSN defined:

Data Source Name - ORA11
Description - Oracle Database
TNS Service Name - INSIGHT
User ID - uid/pw

TNSNAMES.ORA -

INSIGHT =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = url)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = INSIGHT
 )
)

I run SET from command prompt

oracle_home=C:\Oracle\instantclient_11_2\
TNS_ADMIN=c:\oracle\instantclient_11_2\

OP-Zaharin:

I am running the Oracle ODBC connection.

My instantclient install did not include tnsping.

slightwv:

The environment variables are shown above. I only have one Oracle product installed.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37038871
>>I run SET from command prompt

For the ODBC driver to find them they need set in the 'environment' not from a command prompt.  using SET in a command prompt is local to that prompt.

http://support.microsoft.com/kb/310519
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37038880
>>TNS_ADMIN=c:\oracle\instantclient_11_2\

Is your tnsnames.ora file in that folder or in a sub folder?
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37039691
Those variables are set in the Environment.

Yes, tnsnames.ora is in c:\oracle\instantclient_11_2\
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039734
try putting it in

c:\oracle\instantclient_11_2\network\admin
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039743
you will likely need to manually create those folders.  At least I had to on my instant client install.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37039781
OK - I tried it. Same error.

Do I need to modify the PATH variable?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 37039797
no but you may need to change TNS_ADMIN to point to it  or unset the variable and let it default
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039821
I believe if it isn't found in TNS_ADMIN it will attempt to look in the 'default' network/admin.

This is older but I do not think it has changed for a long time:
http://www.dba-oracle.com/t_windows_tnsnames.ora_file_location.htm

0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039844
>>I only have a User DSN defined

for grins can you try a System DSN?
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37039912
Test Connection in the System DSN results in same error.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37039944
I'm running out of ideas.  Oracle configurations are pretty straight forward.

Can you post a screenshot of the ODBC administrator for the Oracle driver?
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37040965
I thought so too.

The screen shot is in the attached file. It is a .RTF, but I had to call it a .TXT.


ODBC-Configuration.rtf.txt
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37041685
Sorry I missed this from your first post and didn't think about it until now.

SYS is a special user and needs 'as sysdba'.  My guess is ODBC isn't smart enough to handle that user.

Try connecting as system or some other user and see if it works.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37048290
That didn't work. I also tried to confirm the SERVICE_NAME from the SQL prompt:

SQL > select * from global name; gave OLTP
SQL > select instance_name from v$instance; gave oltp_1

Neither of those worked as my SERVICE_NAME entry in TNSNAMES.ORA
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37048297
Now - I DID get to link to Oracle with this VBA code in Excel:


Sub DisplayDBATables()

  Dim cn As ADODB.Connection
  Dim rs As ADODB.Recordset
  Dim col As Integer
  Dim row As Integer
 
  Set cn = New ADODB.Connection
  Set rs = New ADODB.Recordset
   
  cn.Open ( _
     "User ID=user" & _
    ";Password=password" & _
    ";Data Source=aaaaa.bbb.net:1521/insight" & _
    ";Provider=MSDAORA.1")
 
  rs.Open "select * from dba_tables", cn
 
  col = 0
 
  ' First Row: names of columns
  Do While col < rs.Fields.Count
    Cells(1, col + 1) = rs.Fields(col).Name
    col = col + 1
  Loop
 
  ' Now actual data as fetched from select statement
  row = 1
  Do While Not rs.EOF
    row = row + 1
    col = 0
   
    Do While col < rs.Fields.Count
      Cells(row, col + 1) = rs.Fields(col).Value
      col = col + 1
    Loop
 
    rs.MoveNext
  Loop

End Sub
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048311
Can you log in to the database server?

From a command prompt please do the following and post the results: lsnrctl status
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37048329
All I can do is get to the SQL > prompt. lsnrctl is not a SQL command. Is there a way to edit or otherwise copy/read/display the lnrctl on the server with SQL commands?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 37048338
sql prompt on the database server or from a remote client?

If remote: ask the DBAs of the database server to provide the output.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37051113
I am using the remote client. I'll see what I can find out from the DBAs.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 37143335
Still waiting to see what I can find out.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38408306
I captured the output of "show parameter".

So, from that, what do I look at to create a valid TNSNAMES.ORA file so that I can generate a valid ODBC link?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38409370
I'm still waiting for the output of the lsnrctl status that I asked for in http:#a37048311

I'm not sure what 'show parameter' was supposed to do.  That is the database parameters.  I'm looking for the listener information.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38409387
"SQL> lsnrctl status
SP2-0734: unknown command beginning "lsnrctl st..." - rest of line ignored."
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38409408
lsnrctl is an OS command not a sqlplus command.  This command needs to be executed on the database server as the oracle user or DBA account.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38410693
So - from the SQL prompt, is there a command to connect to the database server and then run the lsnrctl command?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38410701
No.
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38410711
Let me correct my "NO".

You 'could' create a database job using dbms_scheduler that would run an OS command and you could probably figure out some way to get the results back but that is WAY overkill for this.

Just have your DBA run the command and provide you the results.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38410722
if you are connected to sql*plus from the server that the listener is running you can use HOST  or !

SQL> !lsnrctl status

SQL> host lsnrctl status


if you are running sql*plus locally to connect to a db server then no. you can't


however, if you are running sql*plus on the db server simply exit from sql*plus and run the command from the command line
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38431167
OK - I hope to meet with our Oracle DB administrator this week to get the TNSNAMES.ORA file,
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38443615
So the Oracle IT person said that they don't "encourage" end users to have TNSNAMES.ORA. But, I should request data browser function to be added to my Oracle screen. So - I guess that's all I can do at this point. Does data browser enable queries to be designed, stored and refreshed for updates?
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38444468
I'm not sure what they mean by 'data browser function'.  The only thing I could Google seems to be a database access program called Data Browser.

To connect to Oracle, something has to tell the app where the database is located (server, port and instance name).  This is typically done with the tnsnames.ora file.  There are other methods but tnsnames is probably the most common.

If the DBA doesn't want you to use it, then they need to assist you directly with solving the issues.  There really isn't much we can do here with the internal politics.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38447911
I have been reading some configuration documents have a section on listener. Can these help me create the TNSNAMES.ORA file?

There are various servers with these database names:

JDA0TS
JDA0DV
JDA0SB
JDA0PD
DRM0TS
SOA0TS
EPS0PD
SOA0PD
JDE0DV

1. Is there a definition for what these are?

Here is the info from one of these configuration documents. (Changed IP and urls for privacy.)

1.) HOSTNAME:                    Name:   xxx-yyy001.zzz.net
                            Address: nn.ooo.p.qq

6.1)      LIST OF TABLESPACES FOR DATABASE    EBS0PD

SYSTEM
CTXD
OWAPUB
TEMP1
ODM
PORTAL
APPS_UNDOTS1
APPS_TS_TX_DATA
APPS_TS_TX_IDX
APPS_TS_SEED
APPS_TS_INTERFACE
APPS_TS_SUMMARY
APPS_TS_NOLOGGING
APPS_TS_ARCHIVE
APPS_TS_QUEUES
APPS_TS_MEDIA
OLAP
SYSAUX
APPS_TS_TOOLS
TEMP2
USERS

6.2) LIST OF DATAFILES:

/VolMount_DB/oracle/oradata/EBS0PD/system12.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system11.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system10.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system03.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system04.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system05.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system06.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system07.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system08.dbf
/VolMount_DB/oracle/oradata/EBS0PD/system09.dbf
/VolMount_DB/oracle/oradata/EBS0PD/ctxd01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/owad01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_queue02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/odm.dbf
/VolMount_DB/oracle/oradata/EBS0PD/olap.dbf
/VolMount_DB/oracle/oradata/EBS0PD/sysaux01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/apps_ts_tools01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_data04.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind06.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_ref03.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_int02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/sysaux02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/users01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/portal01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/undo01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_data01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_ref01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_int01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_summ01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_nolog01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_archive01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_queue01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_media01.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_data02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_data03.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind02.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind03.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind04.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_txn_ind05.dbf
/VolMount_DB/oracle/oradata/EBS0PD/a_ref02.dbf

10.)      LISTENERS LIST:

# Net8 definition for Database listener
#

EBS0PD =
  (DESCRIPTION_LIST =
    (DESCRIPTION =
      (ADDRESS = (PROTOCOL = TCP)(HOST = xxx-yyy001.zzz.net)(PORT = 1521))
    )
  )

SID_LIST_EBS0PD =
  (SID_LIST =
    (SID_DESC =
      (ORACLE_HOME= /app/oracle/product/11.2.0/ebs)
      (SID_NAME = EBS0PD)
    )
  )

STARTUP_WAIT_TIME_EBS0PD = 0
CONNECT_TIMEOUT_EBS0PD = 10
TRACE_LEVEL_EBS0PD = OFF

LOG_DIRECTORY_EBS0PD = /app/oracle/product/11.2.0/ebs/network/admin
LOG_FILE_EBS0PD = EBS0PD
TRACE_DIRECTORY_EBS0PD = /app/oracle/product/11.2.0/ebs/network/admin
TRACE_FILE_EBS0PD = EBS0PD
ADMIN_RESTRICTIONS_EBS0PD = OFF
SUBSCRIBE_FOR_NODE_DOWN_EVENT_EBS0PD = OFF

IFILE=/app/oracle/product/11.2.0/ebs/network/admin/listener_ifile.ora

Note: Default listener no listener configured


2. Is this enough information to create the TNSNAMES.ORA file?
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38448712
I tried creating the TNSNAMES.ORA file:

EBS0PD =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = nn.mmm.o.pp)(PORT = 1521))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = EBS0PD
 )
)

JDA0PD =
 (DESCRIPTION =
   (ADDRESS_LIST =
     (ADDRESS = (PROTOCOL = TCP)(HOST = nn.mmm.o.qq)(PORT = 1801))
   )
 (CONNECT_DATA =
   (SERVICE_NAME = JDA0PD
 )
)


When I test this User DSN connection in the ODBC Data Source Administrator it says

Unable to connect
SQLState=08004
[Oracle][ODBC][Ora]Ora-12154: TNS:could  not resolve the connect identifier specified
0
 
LVL 78

Expert Comment

by:slightwv (䄆 Netminder)
ID: 38455224
The tnsnames.ora file you attempted appears to be correct but I didn't copy it and actually check it for syntax.

I never did receive the listener status that I've asked for several times.  First back in http:#a37048311

I'm not looking for config files from who knows where.  I want to see the actual listener report what it is doing.
0
 
LVL 2

Accepted Solution

by:
jkasavan earned 0 total points
ID: 38478366
The IT people say:

"We have not been granting ODBC access to JDE 9.0 PROD Database.
This is a SOX compliance issue, we cannot enforce any JDE Application access security rules.
This is not approved."

I didn't realize that the ODBC connections makes JDE Application access security unenforceable.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38478411
going through ODBC, or any direct connection to the database,
rather than through the application itself means the application can't enforce how the data is extracted and used in other sources.  

That's where the SOX compliance comes in.

A common solution to this is to create a view within the SOX-controlled db that exposes the data you need with whatever application/business constraints are appropriate.  Then create a dedicated user that only has access to read that view.  Your odbc connection (or any other direct connection methodology) uses that user to authenticate and extract the needed data.

ODBC itself doesn't create any additional SOX issues, only the act of connecting to the database directly.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38478424
If an external pull is still not allowed, then you will need to flip the SOX constraint back on the Oracle side and require them to push the data in a SOX compliant method to your sql server system.

If there is any question as to who "is in the right," contact your legal and/or accounting offices before committing to any particular IT solution.
0
 
LVL 2

Author Comment

by:jkasavan
ID: 38478497
I think that I probably won't be able to move forward at this point. I'd like to close out the thread and award points - how do I allocate?
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38478522
award points to whichever post or posts that were helpful.
By default any split will be weighted equally, but you have to option of altering the points per post if you want.
0
 
LVL 74

Expert Comment

by:sdstuber
ID: 38782434
If none of the answers were helpful I recommend simply deleting the question.
The author's post isn't an a solution either.
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article started out as an Experts-Exchange question, which then grew into a quick tip to go along with an IOUG presentation for the Collaborate confernce and then later grew again into a full blown article with expanded functionality and legacy…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.
Suggested Courses
Course of the Month21 days, 7 hours left to enroll

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question