Problem with oracle query

[gliporace]

Hi,

I just updated my web2py installation from 2.11 to 2.18.5, but the apps who make use of cx_Oracle for connecting to a Oracle database show this error 

when ther try to query a table/view:

Exception ORA-00942: table or view does not exist

The old web2py installation run without problems on a Ubuntu server 14.04 and cx_Oracle 5.3, the new installation is on Ubuntu server 16.04 with the same version of cx_Oracle.

Is there any means to check what is the query before is sent to the server?

[gliporace]

This is the query:

query = (db1.ana_paz.COGNOME==form.vars.cognome.upper())
        if form.vars.nome:
            query = query & (db1.ana_paz.NOME==form.vars.nome.upper())
        if form.vars.data_nascita:
            query = query & (db1.ana_paz.DATAJ_NAS==form.vars.data_nascita)
        try:
            rows = db1(query).select()
        except:
            response.flash = "Errore nella ricerca anagrafica, procedere all'inserimento manuale delle informazioni."

A raw query with db1.execute() works ok instead.

Hope someone could me :)

Gianfranco.

[gliporace]

I think I found the problem:

the query is sent with the table name double-quoted:

select "ana_paz"."COGNOME", "ana_paz"."NOME" from "ana_paz" where "ana_paz"."COGNOME"='ROSSI'      <--- doesn't not work ("table or view does not exists")

select ana_paz."COGNOME", ana_paz."NOME" from "ana_paz" where ana_paz."COGNOME"='ROSSI'  <---- works

Setting 

entity_quoting = False

in the database connection solved the problem.

Il giorno venerdì 12 aprile 2019 17:00:14 UTC+2, gliporace ha scritto:

[Massimo Di Pierro]

Damn Oracle! Can you find some documentation that says that quoting of table names is not supported and we will change? Want to make sure there not some other configuration quirk before removing a useful feature.

[Kevin Keller]

When a table is created in Oracle, it is always created in uppercase letters, if not created by using quotes. 

create table test= TEST

create table "test" = test

it is very likely that the table when created, was created without quotes and hence the table name is saved in uppercase letters. 

Now you query using quotes in lowercase letters and it doesnt find the table. 

when you removed the quotes, Oracle defaults to uppercase letters again and found the table. 

--
Resources:
- http://web2py.com
- http://web2py.com/book (Documentation)
- http://github.com/web2py/web2py (Source code)
- https://code.google.com/p/web2py/issues/list (Report Issues)
---
You received this message because you are subscribed to the Google Groups "web2py-users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to web2py+un...@googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[gliporace]

I didn't create the table, I was trying to connect to a read-only view from one of our oracle servers, I'm just a user:)

Thanks for the explanation though, now I changed the name of the table to uppercase in the model definition

and it works without entity_quoting = False.

Gianfranco.

To unsubscribe from this group and stop receiving emails from it, send an email to web...@googlegroups.com.

[tomt]

Thanks for posting your solution.  I ran into the same problem and probably wouldn't have figured it out without your post.

- Tom