Oracle schema not working

[rahajiyev]

Hello. The user connecting to Oracle is an ordinary user and needs to
prefix all tables with the schema name.
I've tried crafting Meta.db_table like so:
http://cd-docdb.fnal.gov/cgi-bin/RetrieveFile?docid=3156&version=1&filename=DjangoOracle.html

But I get error

DatabaseError at /

schema "foo" does not exist
LINE 1: ...ty", "foo"."table_name"."address_country" FROM "foo"."...


I also tried wrapping request in a TransactionMiddleware and execute
this SQL before the fetching (modifying Meta accordingly):
MyModel.objects.raw('ALTER SESSION SET CURRENT_SCHEMA=foo')

Neither way helped. The user has the needed permissions.

[Jani Tiainen]

Since Oracle doesn't make a difference between user and schema (they're
equivalents)

Simplest thing is to create (private/public) synonyms for tables for
user in question. Otherwise you need to prefix with schema name.

See also ticket https://code.djangoproject.com/ticket/6148

--
Jani Tiainen

- Well planned is half done and a half done has been sufficient before...

[rihad]

On Jun 1, 2:58 pm, Jani Tiainen <rede...@gmail.com> wrote:
> 1.6.2012 10:43, rahajiyev kirjoitti:
>
>
>
>
>
>
>
>
>
> > Hello. The user connecting to Oracle is an ordinary user and needs to
> > prefix all tables with the schema name.
> > I've tried crafting Meta.db_table like so:

> >http://cd-docdb.fnal.gov/cgi-bin/RetrieveFile?docid=3156&version=1&fi...

>
> > But I get error
>
> > DatabaseError at /
>
> > schema "foo" does not exist
> > LINE 1: ...ty", "foo"."table_name"."address_country" FROM "foo"."...
>
> > I also tried wrapping request in a TransactionMiddleware and execute
> > this SQL before the fetching (modifying Meta accordingly):
> > MyModel.objects.raw('ALTER SESSION SET CURRENT_SCHEMA=foo')
>
> > Neither way helped. The user has the needed permissions.
>
> Since Oracle doesn't make a difference between user and schema (they're
> equivalents)
>
> Simplest thing is to create (private/public) synonyms for tables for
> user in question. Otherwise you need to prefix with schema name.
>

Thank you, I think private synonyms is a nice workaround, since I
couldn't get prefixing to work.





> See also tickethttps://code.djangoproject.com/ticket/6148

[akaariai]

On Jun 1, 12:58 pm, Jani Tiainen <rede...@gmail.com> wrote:
> > Hello. The user connecting to Oracle is an ordinary user and needs to
> > prefix all tables with the schema name.
> > I've tried crafting Meta.db_table like so:

> >http://cd-docdb.fnal.gov/cgi-bin/RetrieveFile?docid=3156&version=1&fi...

>
> > But I get error
>
> > DatabaseError at /
>
> > schema "foo" does not exist
> > LINE 1: ...ty", "foo"."table_name"."address_country" FROM "foo"."...
>
> > I also tried wrapping request in a TransactionMiddleware and execute
> > this SQL before the fetching (modifying Meta accordingly):
> > MyModel.objects.raw('ALTER SESSION SET CURRENT_SCHEMA=foo')
>
> > Neither way helped. The user has the needed permissions.
>
> Since Oracle doesn't make a difference between user and schema (they're
> equivalents)
>
> Simplest thing is to create (private/public) synonyms for tables for
> user in question. Otherwise you need to prefix with schema name.
>
> See also tickethttps://code.djangoproject.com/ticket/6148

I have updated the patch in that ticket to current master. It should
now work for all core backends, though GIS is still unsupported.
Please test.

- Anssi

[rahajiyev]

Why is Django strangely quoting column and table names? It gives
Oracle syntax errors.

DatabaseError at /

relation "foo" does not exist
LINE 1: ...ty", "foo"."address_country" FROM "foo"."...

Of course it exists as foo, not as "foo".

I already did the CREATE SYNONYM trick to avoid messing with schemas.

[rahajiyev]

Exact error:

[Jani Tiainen]

By default Oracle makes following assumption: if given name (column,
table, schema etc.) is not quoted it's converted implicitly to uppercase
and used as that.

Thus clause: select * from SoMeTaBle becomes to select * from "SOMETABLE".

If you provide quotes Oracle uses table name as is and thus making it
case-sensitive.

In theory Django should make all names uppercase regradless how you
write it. I recall someone to complain strange behavior in cases with
Oracle backend.

[Ian]

What does the db_table in your model definition look like?  It should just be:

    db_table = 'foo'  # Django will take foo and change it to "FOO", which is how Oracle interprets foo

not:

    db_table = '"foo"'  # Since this is already quoted, I think Django won't change it, and then Oracle won't be able to find the table because it is explicitly the wrong case.

Cheers,
Ian

[rahajiyev]

My bad, folks, late in the day I forgot to add the using() clause as
I'm using tiny local postgres and huge remote oracle (read mostly) and
oracle won't be default.

Foo.objects.using('svfe').all().order_by('-udate', '-time')[:5];

can I somehow configure it using the model? Something like in the
Meta: using = 'svfe' so it sticks to future queries?
I couldn't find such a possibility, only writing complicated decisions
made by a router, or do it manually as above. There's really no reason
for "using" to be dynamic in my case.

[rahajiyev]

BTW, simple db_table = 'foo' worked fine.