Oracle drivers.

[Michael M]

2.12.3-stable+timestamp.2015.08.19.00.18.03
(Running on Apache/2.4.16 (Fedora) OpenSSL/1.0.1k-fips mod_wsgi/4.4.8 Python/2.7.10, Python 2.7.10)

So "import cx_Oracle" works in python console but when I tried using it in web2py it errors out.  <type 'exceptions.ImportError'>(Cannot import module 'applications.test2.modules.cx_Oracle')

the only thing i have found is that the file structure looks different from cx_Oracle to another import i use.

pysnmp works in web2py and straight python:

>>> import pysnmp

>>> pysnmp.__file__

'/usr/lib/python2.7/site-packages/pysnmp/__init__.pyc'

cx_Oracle doesnt work in web2py but works in py files.

>>> import cx_Oracle

>>> cx_Oracle.__file__

'/usr/lib/python2.7/site-packages/cx_Oracle.so'

the only difference I saw was that its a .so file instead of __init__.pyc

Any thoughts.  Trying to get this to work in Fedora 22.  Thanks!

[Leonel Câmara]

You don't need to import it in your application the DAL does it for you.

[Michael M]

i was just trying to find some way to expose more of what going on.  When I call Oracle in my DB configs uri = oracle://... I get this

1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. 12. 13. 14. 15. 16. 17. 18. 19. 20. 21. 22.

Traceback (most recent call last): File "/opt/www-data/web2py/gluon/restricted.py", line 227, in restricted exec ccode in environment File "/opt/www-data/web2py/applications/test/models/db.py", line 37, in <module> db = DAL(myconf.take('db.uri'), pool_size=myconf.take('db.pool_size', cast=int), check_reserved=['all']) File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 174, in __call__ obj = super(MetaDAL, cls).__call__(*args, **kwargs) File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 459, in __init__ raise RuntimeError("Failure to connect, tried %d times:\n%s" % (attempts, tb)) RuntimeError: Failure to connect, tried 5 times: Traceback (most recent call last): File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__ self._adapter = ADAPTERS[self._dbname](**kwargs) File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__ obj = super(AdapterMeta, cls).__call__(*args, **kwargs) File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 105, in __init__ if do_connect: self.find_driver(adapter_args,uri) File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 188, in find_driver raise RuntimeError("no driver available %s" % str(self.drivers)) RuntimeError: no driver available ('cx_Oracle',)

[Leonel Câmara]

Well the dal tries to "import cx_Oracle" and it should work since it's installed - Weird. Did you already have web2py running before installing cx_Oracle and you didn't restart it (say by touching its uwsgi config file)?  

[Michael M]

yeah I tried spinning up another application in my Non-prod env. 

2.12.3-stable+timestamp.2015.08.19.00.18.03
(Running on Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.1e-fips mod_wsgi/3.4 Python/2.7.5, Python 2.7.5)

and just changed the URI from:

uri       = sqlite://storage.sqlite

to:

uri       = oracle://............

and got the same error.  and I didnt import anything just changed that one thing on that app and it gave be the same error just trying to load the index page as second error I pasted above.

[Michael M]

Forgot to paste the Error on my Non-Prod Virt.

Version

web2py™	Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Traceback

[Leonel Câmara]

Is it possible web2py is running in a virtualenv where you have not installed cx_Oracle?

Also don't forget to restart Apache.

[Michael M]

I was testing in non-prod (RHEL) to see if it was built in.  it wasnt.  on my Fedora 22 at my desk I installed cx_Oracle because it works when i am in CLI python and I can call it no errors.  I have yet to dabble in virtualenv.

I have systemctl restart httpd after every change.  even reboots to make sure.

[Michael M]

Just to cover more basis I installed the following:

sudo rpm -Uvh

oracle-instantclient11.2-basic-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-devel-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-jdbc-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-odbc-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-sqlplus-11.2.0.4.0-1.x86_64.rpm

oracle-instantclient11.2-tools-11.2.0.4.0-1.x86_64.rpm

then 

cx_Oracle-5.1.2-11g-py27-1.x86_64.rpm

Still no dice in Web2py

[Willoughby]

When you start web2py from a command line it should list 'Database drivers available' - is it on that list?

[Michael M]

Never checked there.  But it is.

$ python web2py.py

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2015

Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Database drivers available: cx_Oracle, pymysql, imaplib, sqlite3, pg8000, pyodbc

Weird.  just rebooted the Virt. and still getting:

Traceback (most recent call last):
  File "/opt/www-data/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment

  File "/opt/www-data/web2py/applications/test/models/db.py", line 20, in <module>
    

db = DAL(myconf.take('db.uri'), pool_size=myconf.take('db.pool_size', cast=int), check_reserved=['all'])
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 174, in __call__
    obj = super(MetaDAL, cls).__call__(*args, **kwargs)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 459, in __init__
    raise RuntimeError("Failure to connect, tried %d times:\n%s" % (attempts, tb))
RuntimeError: Failure to connect, tried 5 times:
Traceback (most recent call last):
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 437, in __init__
    self._adapter = ADAPTERS[self._dbname](**kwargs)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 57, in __call__
    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 105, in __init__
    if do_connect: self.find_driver(adapter_args,uri)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 188, in find_driver
    raise RuntimeError("no driver available %s" % str(self.drivers))
RuntimeError: no driver available ('cx_Oracle',)

[Massimo Di Pierro]

This may be a bug. Can you please open a pydal ticket and we will check it asap?

[Michael M]

Submitted:

https://github.com/web2py/web2py/issues/1082

[Niphlod]

the repo for pydal is the other one.... no worries though, I linked your one in https://github.com/web2py/pydal/issues/299

[Michael M]

Thank-you Niphlod!

[Michael M]

Has anyone been able to reproduce this?  And I don't think you actually need an Oracle system.  Just pointing to a Oracle URI will cause the error on my side.

[Michael M]

Tried it again.

This time I tried on another RHEL 7 server.

Downloaded Oracle's Instant Client Zips and extracted all of them instead of installing RPMs
Same with the cx_Oracle Module.  didnt install the RPM got the source and complied.

I set the envoirment variables

export ORACLE_HOME=/usr/include/oracle/11.1
export LD_LIBRARY_PATH=$ORACLE_HOME/
export PATH=$ORACLE_HOME/bin:$PATH

Created a symlink: ln -s libclntsh.so.11.1 libclntsh.so

I compiled this time 'cx_Oracle-5.1.2.tar.gz' with python setup.py build && python setup.py install

Still able to launch python from CLI and import cx_Oracle without problems
Still able to launch Web2py from CLI to see "cx_Oracle" in the Available drivers line.

Still unable to load an application that has the DB pointing to a oracle:// URI without a ticket being auto-generating.

Would finding an older version of Web2py maybe help?

[Michael M]

Is it Possible that the DAL is talking Python 3.4 when my driver is Python 2.7.  So when I call it in Web2py from CLI it works but when the framework is running its trying to call 3.4 to talk to cx_Oracle?

See below:

2.12.3-stable+timestamp.2015.08.19.00.18.03
(Running on Apache/2.4.6 (Red Hat Enterprise Linux) OpenSSL/1.0.1e-fips mod_wsgi/3.4 Python/2.7.5, Python 2.7.5)

The Running Shows 3.4 Python/2.7.5, Python 2.7.5. could that contribute to the issue?  Or am i reading it wrong?

Thanks!

[Michael Messmer]

Ok That was wrong.  mod_wsgi was 3.4

[Michael Messmer]

So i noticed something.  I was in root when when I was seeing cx_Oracle:

# python web2py.py 

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2015

Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Database drivers available: sqlite3, imaplib, cx_Oracle, pymysql, pg8000

When I was in Standard user:

$ python web2py.py

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2015

Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Database drivers available: sqlite3, imaplib, pymysql, pg8000

----

So I moved the cx_Oracle.so file into /usr/lib64/python2.7/site-packages/ and then chmod so that everyone could see and execute. 

and now when Im in a standard user:

$ python web2py.py 

web2py Web Framework

Created by Massimo Di Pierro, Copyright 2007-2015

Version 2.12.3-stable+timestamp.2015.08.19.00.18.03

Database drivers available: sqlite3, imaplib, cx_Oracle, pymysql, pg8000

did a 'systemctl restart httpd"

Still nothing.  I was so hopeful.  Any thoughts of what I may be doing wrong?

Does Apache or mod-wsgi need some special privileges that I have not given to 'cx_Oracle.so'?  As those is running the framework and not my user account nor root.  Thoughts?

[Michael Messmer]

Ok more info.

added this to a def and its a copy of code from 'line 1101' of web2py/gluon/widget.py :

def about():

    from pydal.drivers import DRIVERS

    print 'Database drivers available: %s' % ', '.join(DRIVERS)

    return locals()

the about page returns:

{'sqlite3': <module 'sqlite3.dbapi2' from '/usr/lib64/python2.7/sqlite3/dbapi2.pyc'>, 'imaplib': <module 'imaplib' from '/usr/lib64/python2.7/imaplib.pyc'>, 'pymysql': <module 'gluon.contrib.pymysql' from '/opt/www-data/web2py/gluon/contrib/pymysql/__init__.pyc'>, 'pg8000': <module 'gluon.contrib.pg8000' from '/opt/www-data/web2py/gluon/contrib/pg8000/__init__.pyc'>}

just trying to expose something to help jog anything.  And not giving up!  :)

[Cássio Botaro]

Do you already tried

sudo dnf install python-pip
sudo pip install cx_Oracle

?

[Michael M]

I did.  And still no luck.    It Jams me because run web2py from CLI and Rocket serves it.  It Oracle works flawlessly with Web2py.

So it maybe just an Apache/Mod_WSGI thing. 


I am trying to get NginX and ( UWSGI or Gunicorn ) to work on my environment ( Fedora 22 / RHEL 7 )

[Massimo Di Pierro]

Any news on Oracle and Nginx? Is it an Apache issue?

[Michael M]

I have been trying to find good a install script for nginx on rhel/cent 7.

It has been slow going on my part due to my new job. I just joined the ranks of Unix OS admins. Coming from a Windows world my job of getting up to speed has been a monumental task. ;-)

If there is an updated nginx and uwsgi or gunicorn scripts for version 7 of rhel/cent?

[Rakesh Singh]

My guess is that this is a permission issue.

cx_Oracle may be accessible to Web2Py, but the Oracle libraries may be readable by your nginx user

What are the permissions your ORACLE_HOME directory and files within it?

/usr/include/oracle/11.1

[Michael Messmer]

I think it is too.  Just made some progress.  And a bunch of VM's later:

Error ticket for "test"

Ticket ID

172.21.140.228.2015-12-24.14-29-42.af6f82d8-d7b2-4a69-8409-1cf04d67c994

<type 'exceptions.RuntimeError'> Failure to connect, tried 5 times: Traceback (most recent call last): File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 446, in __init__ self._adapter = ADAPTERS[self._dbname](**kwargs) File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 60, in __call__ obj = super(AdapterMeta, cls).__call__(*args, **kwargs) File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 118, in __init__ if do_connect: self.reconnect() File "/opt/www-data/web2py/gluon/packages/dal/pydal/connection.py", line 125, in reconnect self.connection = f() File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 116, in connector return self.driver.connect(uri,**driver_args) DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

Version

web2py™ Version 2.13.2-stable+timestamp.2015.12.18.11.00.46

Traceback (most recent call last):

  File "/opt/www-data/web2py/gluon/restricted.py", line 227, in restricted

    exec ccode in environment

  File "/opt/www-data/web2py/applications/test/models/db.py", line 20, in <module>

    db = DAL(myconf.take('db.uri'), pool_size=myconf.take('db.pool_size', cast=int), check_reserved=['all'])

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 174, in __call__

    obj = super(MetaDAL, cls).__call__(*args, **kwargs)

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 473, in __init__

    "Failure to connect, tried %d times:\n%s" % (attempts, tb)

RuntimeError: Failure to connect, tried 5 times:

Traceback (most recent call last):

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 446, in __init__

    self._adapter = ADAPTERS[self._dbname](**kwargs)

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 60, in __call__

    obj = super(AdapterMeta, cls).__call__(*args, **kwargs)

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 118, in __init__

    if do_connect: self.reconnect()

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/connection.py", line 125, in reconnect

    self.connection = f()

  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 116, in connector

    return self.driver.connect(uri,**driver_args)

DatabaseError: ORA-12514: TNS:listener does not currently know of service requested in connect descriptor

[Michael Messmer]

Looks like it was permission issues and the RPM's were installing to a directory under /usr/....  Now I am getting

Traceback (most recent call last):
  File "/opt/www-data/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment

  File "/opt/www-data/web2py/applications/test/models/db.py", line 61, in <module>
    auth.define_tables(username=False, signature=False)
  File "/opt/www-data/web2py/gluon/tools.py", line 2059, in define_tables
    format='%(first_name)s %(last_name)s (%(id)s)'))
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 834, in define_table
    table = self.lazy_define_table(tablename,*fields,**args)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/base.py", line 873, in lazy_define_table
    polymodel=polymodel)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 482, in create_table
    self.create_sequence_and_triggers(query, table)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 145, in create_sequence_and_triggers
    self.execute(query)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 138, in execute
    return self.log_execute(command, args)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/base.py", line 1379, in log_execute
    ret = self.get_cursor().execute(command, *a[1:], **b)
DatabaseError: ORA-00955: name is already used by an existing object

So it looks like its working I just have to clean the tables and start fresh.

[Michael Messmer]

So I have run the following to try and purge my current account:

#sign in:
import cx_Oracle
db = cx_Oracle.connect('WEB2PY', 'pw', 'server:1521/sid')
cursor = db.cursor()



#List all tables:
cursor.execute('SELECT table_name FROM all_tables')
for row in cursor:
    print row



#delete Table:
cursor.execute("DROP TABLE blah PURGE")
cursor.execute("DROP TABLE AUTH_USER PURGE")
cursor.execute("DROP TABLE AUTH_GROUP PURGE")
cursor.execute("DROP TABLE AUTH_MEMBERSHIP PURGE")
cursor.execute("DROP TABLE AUTH_PERMISSION PURGE")
cursor.execute("DROP TABLE AUTH_EVENT PURGE")
cursor.execute("DROP TABLE AUTH_CAS PURGE")

-----

But when I try to navigate to the page I get the same error as the last one "<class 'cx_Oracle.DatabaseError'> ORA-00955: name is already used by an existing object"

and it creates "auth_user" and stops.

Is there another table on Oracle that holds what tables it believes has been created?

[Dan Feeney]

You can view tables owned by the current user with:

SELECT TABLE_NAME FROM USER_TABLES ORDER BY TABLE_NAME;

You can view all objects owned by the current user with:

SELECT OBJECT_NAME, OBJECT_TYPE FROM USER_OBJECTS ORDER BY 2, 1;

HTH,
-Dan

[Michael Messmer]

Ahh,  I am ULTRA-Newb to db's:

here is what it returned:

>>> for row in cursor:

...     print row

... 

('SYS_C0012098', 'INDEX')

('SYS_LOB0000092306C00003$$', 'LOB')

('AUTH_CAS_SEQUENCE', 'SEQUENCE')

('AUTH_EVENT_SEQUENCE', 'SEQUENCE')

('AUTH_GROUP_SEQUENCE', 'SEQUENCE')

('AUTH_MEMBERSHIP_SEQUENCE', 'SEQUENCE')

('AUTH_PERMISSION_SEQUENCE', 'SEQUENCE')

('AUTH_USER_SEQUENCE', 'SEQUENCE')

('BLAH_SEQUENCE', 'SEQUENCE')

('AUTH_USER', 'TABLE')

I just googled dropping Objects.  Wish me luck.  ill report back.

[Michael Messmer]

Alright it worked for blah:

cursor.execute("DROP SEQUENCE BLAH_SEQUENCE")

Going for the rest now.

[Michael Messmer]

MAGIC:

cursor.execute("DROP SEQUENCE AUTH_USER_SEQUENCE")
cursor.execute("DROP SEQUENCE AUTH_GROUP_SEQUENCE")
cursor.execute("DROP SEQUENCE AUTH_MEMBERSHIP_SEQUENCE")
cursor.execute("DROP SEQUENCE AUTH_PERMISSION_SEQUENCE")
cursor.execute("DROP SEQUENCE AUTH_EVENT_SEQUENCE")
cursor.execute("DROP SEQUENCE AUTH_CAS_SEQUENCE")

and now I have:

Welcome to web2py!

How did you get here?

-My answer:

--Gray Hairs and not giving up and asking for help until I figured this stuff out!!!!!!

Thank-you Everyone!!!!!

Next life lesson.  Understanding Linux Permissions and global variables.

Ill post my install script after I test on a totally blank non-prod provisioned server in my env.

[Michael Messmer]

ok,  so.  I tried adding a user record and now im getting this:

Traceback (most recent call last):
  File "/opt/www-data/web2py/gluon/restricted.py", line 227, in restricted
    exec ccode in environment

  File "/opt/www-data/web2py/applications/test/controllers/appadmin.py", line 703, in <module>
  File "/opt/www-data/web2py/gluon/globals.py", line 412, in <lambda>
    self._caller = lambda f: f()
  File "/opt/www-data/web2py/applications/test/controllers/appadmin.py", line 151, in insert
    if form.accepts(request.vars, session):
  File "/opt/www-data/web2py/gluon/sqlhtml.py", line 1725, in accepts
    self.vars.id = self.table.insert(**fields)
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/objects.py", line 728, in insert
    ret = self._db._adapter.insert(self, self._listify(fields))
  File "/opt/www-data/web2py/gluon/packages/dal/pydal/adapters/oracle.py", line 233, in insert
    rid = Reference(id)
NameError: global name 'Reference' is not defined

Any Ideas?

[Michael Messmer]

I looked at the other Adapter Modules for that "Reference()"

"web2py/gluon/packages/dal/pydal/adapters/mongo.py"

had it and it also had: 

from "..helpers.classes import SQLCustomType, SQLALL, Reference"

So I added this to the following to "web2py/gluon/packages/dal/pydal/adapters/oracle.py"

"..helpers.classes import Reference"

It works

[Michael Messmer]

@Niphlod @Massimo

Is what i did a correct fix and is this something that needs to be corrected here https://github.com/web2py/pydal/blob/master/pydal/adapters/oracle.py ?

[Niphlod]

if it fixes all issues, open a bug on pydal and it'll get merged.

[Michael Messmer]

Submitted. https://github.com/web2py/pydal/issues/322