Oracle ODBC

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?
LVL 2
jkasavanAsked:
Who is Participating?
 
jkasavanAuthor Commented:
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
 
sdstuberCommented:
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
 
OP_ZaharinCommented:
- 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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
slightwv (䄆 Netminder) Commented:
>>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
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>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
 
slightwv (䄆 Netminder) Commented:
>>TNS_ADMIN=c:\oracle\instantclient_11_2\

Is your tnsnames.ora file in that folder or in a sub folder?
0
 
jkasavanAuthor Commented:
Those variables are set in the Environment.

Yes, tnsnames.ora is in c:\oracle\instantclient_11_2\
0
 
sdstuberCommented:
try putting it in

c:\oracle\instantclient_11_2\network\admin
0
 
slightwv (䄆 Netminder) Commented:
you will likely need to manually create those folders.  At least I had to on my instant client install.
0
 
jkasavanAuthor Commented:
OK - I tried it. Same error.

Do I need to modify the PATH variable?
0
 
sdstuberCommented:
no but you may need to change TNS_ADMIN to point to it  or unset the variable and let it default
0
 
slightwv (䄆 Netminder) Commented:
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
 
slightwv (䄆 Netminder) Commented:
>>I only have a User DSN defined

for grins can you try a System DSN?
0
 
jkasavanAuthor Commented:
Test Connection in the System DSN results in same error.
0
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
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
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
Can you log in to the database server?

From a command prompt please do the following and post the results: lsnrctl status
0
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
I am using the remote client. I'll see what I can find out from the DBAs.
0
 
jkasavanAuthor Commented:
Still waiting to see what I can find out.
0
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
"SQL> lsnrctl status
SP2-0734: unknown command beginning "lsnrctl st..." - rest of line ignored."
0
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
So - from the SQL prompt, is there a command to connect to the database server and then run the lsnrctl command?
0
 
slightwv (䄆 Netminder) Commented:
No.
0
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
jkasavanAuthor Commented:
OK - I hope to meet with our Oracle DB administrator this week to get the TNSNAMES.ORA file,
0
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
jkasavanAuthor Commented:
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
 
jkasavanAuthor Commented:
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
 
slightwv (䄆 Netminder) Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
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
 
jkasavanAuthor Commented:
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
 
sdstuberCommented:
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
 
sdstuberCommented:
If none of the answers were helpful I recommend simply deleting the question.
The author's post isn't an a solution either.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.