Oracle Access via cx_Oracle

[Paul Moore]

"Greg Lindstrom" <greg.lindstrom at novasyshealth.com> writes:

>>Shouldn't this be '/u01/app/oracle/product/9.2.0' ? Based on the following
> values, that's what I'd expect.
> I changed the ORCALE_HOME environment variable to the above suggested value
> and got a new error!!  That's progress, right?  I'm now told:
>  cx_Oracle.DatabaseError: ORA-12154: TNS:could not resolve service name
> /usr/bin/sh: cx_Oracle.DatabaseError:: not found
> Is this any help?

It is, insofar as this means that Oracle is doing the right thing, but
can't find the database. So let's check how we are telling Oracle
where the database is...

First, when you connect using SQL*Plus, you connect something like
this:

    sqlplus user/password at database

Here, "database" is what Oracle refers to as a "connect string",
which translates to IP/port/SID using the Oracle TNSNAMES.ORA file.

> myIP = "ww.xx.yy.zzz"
> myPort = nnnn
> myDsn = cx_Oracle.makedsn(myIP, myPort, 'test1')

This, on the other hand, constructs a connection descriptor (what a
connect string translates to) by hand. Something like

  (DESCRIPTION =
    (ADDRESS_LIST =
      (ADDRESS = (PROTOCOL = TCP)(HOST = ww.xx.yy.xxx)(PORT = nnnn))
    )
    (CONNECT_DATA = (SID = test1))
  )

> oracle = cx_Oracle.connect(myUid, myPwd, myDsn)

Instead of this, could you try

oracle = cx_Oracle.connect(myUid, myPwd, 'database')

where 'database' here is the bit of the SQL*Plus command abover after
the '@' sign? That should make cx_Oracle connect using *exactly* the
same connection descriptor as SQL*Plus uses. If that works, we can
look at why your makedsn call isn't constructing the same descriptor.
(Or, more likely, you can just use the literal connect string that
works, and not bother about gory Oracle details :-))

[elsewhere]
> Yes, I can hit the Oracle database on the remote (posix) machine with
> SQL*Plus running on my Windows box.  Does that help me out? Please excuse my
> ignorance...I've been using MS-SQL Server for the past 4 or 5 years.

Hang on. Is your Python script running on the Windows box? If so, the
os.putenv stuff is both irrelevant and wrong. On Windows, you don't
need any environment variables. Just skip all the putenv stuff.

Hope this helps,
Paul.

PS If you don't follow any of the above, could you just paste into
your reply the *exact* SQL*Plus command that you use which works for
you. I can then give you a more specific example to try.
-- 
The most likely way for the world to be destroyed, most experts agree,
is by accident. That's where we come in; we're computer professionals.
We cause accidents. -- Nathaniel Borenstein