Solved

need to configure odbc connection

Posted on 2008-06-22
33
630 Views
Last Modified: 2013-12-18
I have the following image attached I don't know what is my listner. When it drops down i get a whole bunch of weird ascii characters.
see attached image.
Can you help.
image1.jpg
0
Comment
Question by:mathieu_cupryk
  • 19
  • 11
  • 3
33 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
> I don't know what is my listner
check out the contents of the tnsnames.ora file in your oracle home.
it will (should) contain the tns name config for the database you want to connect to.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
where can I check this tnsnames.ora?

it seems i cannot see this file in vista.
0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
in your oracle home folder, then network\admin
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
if i go to oracle folder all i can see is product folder

0
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
Comment Utility
what oracle client did you install on the machine you want to connect from?
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
Perhaps he's using instant client?

R.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
i downloaded the production db
production client
product companion
and installed all three.
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
Did you install all 3 on the same machine? If so, then you didn't need to install the client because the client is included in the main database server (production db).
Anyway, I'm assuming you're working on the same machine you installed all these things on. In which case, if you kept notes during your installation, then you should recall the ORACLE_HOME parameter you set.

The tnsnames.ora file is located in $ORACLE_HOME/network/admin directory. Sorry I don't know if you are windows or linux/unix.

If you open this file then you should see your connection details for any databases you have created.

It would look something like:

myConn =
  (DESCRIPTION =
    (ADDRESS_LIST =
        (ADDRESS = (PROTOCOL = TCP)(HOST = 192.168.1.2)(PORT = 1521))
    )
    (CONNECT_DATA =
      (SERVICE_NAME = myDB.mydomain.com)
    )
  )
   
In the above, my myConn is the alias and it is what I'll use to connect to my database.

Alternatively, you could search for tnsnames.ora
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
this is what I have i do not know where things are:
see attached image.
services1.jpg
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
OK.

Are you on Vista?
Did you search for tnsnames.ora?

I can guess that you would have an entry in yout tnsnames.ora for XE so when you're asked for a TNS Service Name, you could enter/select XE.

If this doesn't work then I'll have to insist that you create a tnsnames.ora and if one doesn't exist then we'll have to create one.

R.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
yes i am on vista. there is no tnsnames.ora
see attached file.

tns.jpg
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
Tell me if you have the following directory

oraclexe\app\oracle\product\10.2.0\server\network\admin

If you do then create tnsname.ora file here.

You should use Net Configuration Assistant or Net Manager to create tnsnames.ora but you appear not to be familiar with these tools so we'll try and create one manually.

The content should be as follows:


# tnsnames.ora Network Configuration File: C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\tnsnames.ora

# Generated manually.
 

xe =

  (DESCRIPTION =

    (ADDRESS_LIST =

        (ADDRESS = (PROTOCOL = TCP)(HOST = localhost)(PORT = 1521))

    )

    (CONNECT_DATA =

      (SERVICE_NAME = xe)

    )

  )

Open in new window

0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
You should also create sqlnet.ora in the same directory with the following simple content


# sqlnet.ora Network Configuration File: <your path>\sqlnet.ora

# Generated by Oracle configuration tools.
 

#SQLNET.AUTHENTICATION_SERVICES= (NTS)
 

NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)

Open in new window

0
 

Author Comment

by:mathieu_cupryk
Comment Utility
this is what I have in the file:

XE =
  (DESCRIPTION =
    (ADDRESS = (PROTOCOL = TCP)(HOST = matt-PC)(PORT = 1521))
    (CONNECT_DATA =
      (SERVER = DEDICATED)
      (SERVICE_NAME = XE)
    )
  )

EXTPROC_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = PLSExtProc)
      (PRESENTATION = RO)
    )
  )

ORACLR_CONNECTION_DATA =
  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = IPC)(KEY = EXTPROC_FOR_XE))
    )
    (CONNECT_DATA =
      (SID = CLRExtProc)
      (PRESENTATION = RO)
    )
  )
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
so I will create the file:

# sqlnet.ora Network Configuration File: <your path>\sqlnet.ora
# Generated by Oracle configuration tools.
 
#SQLNET.AUTHENTICATION_SERVICES= (NTS)
 
NAMES.DIRECTORY_PATH= (TNSNAMES, EZCONNECT)


and what should i modify above in the tsnames.ora
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
this is what i have already in the
# This file is actually generated by netca. But if customers choose to
# install "Software Only", this file wont exist and without the native
# authentication, they will not be able to connect to the database on NT.

SQLNET.AUTHENTICATION_SERVICES = (NTS)

what should i modify this.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

Author Comment

by:mathieu_cupryk
Comment Utility
it is saying access denied when i try to save.
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
So you already have it. That's fine.

Like I suspected, you do have an entry for XE so just enter/select XE as your TNS Service Name when you need to connect to your XE database or you need to configure your odbc connection.

R.
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
You have both files!!! You do not need to create or modify anything.

R.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
ok now what should i do?

cause it looks like the oracle maestro is looking for the TNS listner
can we use something else.
should i goto the odbc connections in administration.

0
 

Author Comment

by:mathieu_cupryk
Comment Utility
ok now what should i do?

cause it looks like the oracle maestro is looking for the TNS listner
can we use something else.
should i goto the odbc connections in administration.

0
 

Author Comment

by:mathieu_cupryk
Comment Utility
if i goto odbc
oracle in XE
oracle in Client
microsoft odbc for oracle.
which one do i select.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
unable to connect sqlstate=08004
[oracle][odbc] ora-12154: tns:could not resolve the connect indentifier specified.
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
Tell me what you're trying to do. And maybe show me a screen print of where you're faced with these choices.

From the command prompt do you get a positive response when you do tnsping xe?

This tells you that the listenener is working. And if the listener is working and it has registered the database so that it knows that it exists then anything should be able to connect to the database via the listener.

R.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
when i did the following system dns
see attached image
I have a connection that works.

connection.jpg
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
TNS Ping Utility for 32-bit Windows: Version 10.2.0.1.0 - Production on 22-JUN-2
008 19:54:30

Copyright (c) 1997, 2005, Oracle.  All rights reserved.

Used parameter files:
C:\oraclexe\app\oracle\product\10.2.0\server\network\admin\sqlnet.ora


Used TNSNAMES adapter to resolve the alias
Attempting to contact (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = matt-PC)
(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = XE)))
OK (20 msec)

C:\Users\matt>
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility
So you're OK now?

R.
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
i am using dreamcoder
they asm me for connection manager
I would like to create a schema name with init_price.
Should i log in with sysdba or system.
system worked for the odbc.
see image attached.


oracle-connection.jpg
0
 

Author Comment

by:mathieu_cupryk
Comment Utility
i got in with sysoper. mode.

I need to import the following file and put in under the schema init_price.

init_price should be granted sysoper.

Can you help me.

export.txt
0
 
LVL 16

Expert Comment

by:rolutola
Comment Utility

Good. It sounds like you've been successful.

Log in as SYS (with SYSDBA privilege) to create a user. You could log in as anybody with SYSDBA privileges.

R.


0
 

Author Comment

by:mathieu_cupryk
Comment Utility
i am in the sys@XE

i need to creat this user

I am in the sql editor.


0
 
LVL 16

Accepted Solution

by:
rolutola earned 500 total points
Comment Utility
Log in as SYS and select the SYSDBA mode. Then you can create a user.

Can this tool be used to create a user? If so, then you should be able to create a user when logged in as SYS.

If you have another question then I think you should close this one and start a different thread since the purpose of this thread has been met - To create an odbc connection.

R.
0
 

Author Closing Comment

by:mathieu_cupryk
Comment Utility
Well done. Excellent performance.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Cursors in Oracle: A cursor is used to process individual rows returned by database system for a query. In oracle every SQL statement executed by the oracle server has a private area. This area contains information about the SQL statement and the…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

771 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now