Connecting to OracleXE

[cariad]

Hello,

I'm really sorry having to repost this question, but unfortunately the previous one (along with an helpful answer) was lost during the webhosting switch.

I'm having troubles connecting to Oracle (and OracleXE) DB.

- Oracle XE (or Oracle) is installed locally
- I understand I need a TNS to connect to an existing DB.

What I don't understand is which parameters are needed when connecting. I usually (Firebird, MySQL, ...) give a server URL, DB name, username and password.

One of the line in tnsnames.ora is

XE =
(DESCRIPTION =
(ADDRESS = (PROTOCOL = TCP)(HOST = myserver)(PORT = 1521))
(CONNECT_DATA =
(SERVER = DEDICATED)
(SERVICE_NAME = XE)
)
)

Do I have to give the filename and path as server URL ? Only the 'XE' identifier ? I'm afraid Google haven't been really helpful so far. Anyone care to enlighten me, or point me in the right direction ?

Thanks in advance.

[Terence]

If installed locally following conf works 4me.

prot: oracle-9i
host:localhost
database:XE
and pay attention for that tranactionlevel thing if using manipulation sqls, see
thread http://zeos.firmos.at/viewtopic.php?p=2265#2265

For a new seperate shema create new user, login via this user, create tables.. and then also use this user to login with zeos later.

[mdaems]

There is some demo prog I've made here

Mark

[pol]

Cariad,

In your tnsnames.ora entry the SERVICE_NAME line looks wrong for me. Either write SID = XE or SERVICE_NAME = XE.mydomain.fr

hth
Rüdiger

[cariad]

Ok. Thanks for all the infos. Connection is now working fine. I don't know if it's intented, or documented, or expected, but all I had to do was remove the 'oci.dll' file from the program directory.

Which lead to another question :
- Connection is fine
- I can run SQL scripts against the DB (tables creation, ...) with no problem
- A simple SELECT statement always return "OCI_INVALID_HANDLE"

Basically my code create a TZQuery, set connection to TZConnection (already connected), set TZQuery.SQL.Text, ExecSQL (works fine), and Open. Query.Open fails.

It must be some Oracle idiosyncrasies I'm not aware of, because same code works already for 3 different DB. I tried to fiddle with TransactionSeparation, TZReadOnlyQuery, ... to no avail.

Any further ideas ?
Thanks in advance.

[mdaems]

How many OCI.dll's are available on your system? The one I use is the one in the Oraclexe installation directories. Maybe you have yet some other version installed somewhere, as he can find one after you deleted the file in your program directory. I've read somewhere that there are quite a lot of differences between different versions of OCI.dll

Mark

[cariad]

There was indeed a conflict with oci.dll from Oracle9 installation, and oci.dll from Oracle-XE.

However, for future reference, skipping Query.ExecSQL works fine.

Thanks a lot for your help.

[mdaems]

Hi,

I just saw you were usingExecSQL and Open together. That's not correct. ExecSql is more intended for non resultset returning queries (eg update). Open does everything to open a resultset from the query statement until the first row of the result being current.

Mark