django and oracle

[J. D.]

Can someone help me with cx_Oracle and Django?! :)

I have Oracle DB 11.2 with many PLSQL-procedures in it that return cursor with various output. I want to work with them without django ORM and directly call them.

First of all i ran my python code without django, with the cx_Oracle driver and everything works well.

conn = cx_Oracle.connect('user', 'pass')
ora_cur = conn.cursor()
l_cur = ora_cur.var(cx_Oracle.CURSOR)
l_query = ora_cur.callproc('user.VIEW.procedure_with_cursor', (None, None, None, None, l_cur,))  #None -  "in" parameters and l_cur - out 
l_query[0].fetchall()


but with the django and same code, i got the error:

import cx_Oracle
from django.db import connection

ora_cur = connection.cursor()
ora_cur.execute("SELECT * from v$version") #it works

l_cur = ora_cur.var(cx_Oracle.CURSOR)      #-> Error: Variable_TypeByPythonType(): unhandled data type
l_query = ora_cur.callproc('user.VIEW.procedure_with_cursor', (None, None, None, None, l_cur,))   
l_query[0].fetchall()

Django Version:	1.6.4
Exception Type:	NotSupportedError
Exception Value:	Variable_TypeByPythonType(): unhandled data type
Python Version:	2.7.3

cx_Oracle 5.1.2

[Avraham Serour]

it looks like your error occurs when using django ORM with oracle, but in the beginning of your email you mention that you don't want to use it, so what's the problem here?

--
You received this message because you are subscribed to the Google Groups "Django users" group.
To unsubscribe from this group and stop receiving emails from it, send an email to django-users...@googlegroups.com.
To post to this group, send email to django...@googlegroups.com.
Visit this group at http://groups.google.com/group/django-users.
To view this discussion on the web visit https://groups.google.com/d/msgid/django-users/eb8be6ed-f9c4-4217-ada4-0deebcd3b87c%40googlegroups.com.
For more options, visit https://groups.google.com/d/optout.

[Jani Tiainen]

On Wed, 14 May 2014 07:54:58 -0700 (PDT)
"J. D." <john.arno...@gmail.com> wrote:

> Can someone help me with cx_Oracle and Django?! :)
>
> I have Oracle DB 11.2 with many PLSQL-procedures in it that return cursor
> with various output. I want to work with them without django ORM and
> directly call them.
>
> First of all i ran my python code without django, with the cx_Oracle driver
> and everything works well.
>
> conn = cx_Oracle.connect('user', 'pass')
> ora_cur = conn.cursor()
> l_cur = ora_cur.var(cx_Oracle.CURSOR)
> l_query = ora_cur.callproc('user.VIEW.procedure_with_cursor', (None, None,

> None, None, l_cur,)) #None - "*in*" parameters and l_cur - *out *

> l_query[0].fetchall()
>
>
> but with the django and same code, i got the error:
>
> import cx_Oracle
> from django.db import connection
>
> ora_cur = connection.cursor()
> ora_cur.execute("SELECT * from v$version") #it works
>

> l_cur = ora_cur.var(cx_Oracle.CURSOR) #-> *Error: *Variable_TypeByPythonType():

> unhandled data type
> l_query = ora_cur.callproc('user.VIEW.procedure_with_cursor', (None, None,
> None, None, l_cur,))
> l_query[0].fetchall()
>
>
> Django Version: 1.6.4 Exception Type: NotSupportedError Exception Value:
>
> Variable_TypeByPythonType(): unhandled data type
>
>
>
> Python Version: 2.7.3cx_Oracle 5.1.2
>

You're seeing that error because cursor returned from Django connection is not
actually cx_Oracle cursor but a wrapper that has similar methods. That is done to
overcome different binding variable types and to use %s (oracle uses :name format)

You should be able to get pure cx_Oracle cursor by calling:

connection._cursor()

--

Jani Tiainen

[Jani Tiainen]

Sorry, my bad. You can get pure cx_Oracle connection from:
connection.connection

From there you can do:

connection.cursor().

So to rewrite your example in "django" way:

import cx_Oracle
from django.db import connection

ora_cur = connection.connection.cursor() # See double connection stuff

ora_cur.execute("SELECT * from v$version") #it works

l_cur = ora_cur.var(cx_Oracle.CURSOR)

l_query = ora_cur.callproc('user.VIEW.procedure_with_cursor', (None, None, None, None, l_cur,))
l_query[0].fetchall()

--

Jani Tiainen

[J. D.]

On Wednesday, 14 May 2014 21:08:11 UTC+4, Avraham Serour wrote:

it looks like your error occurs when using django ORM with oracle, but in the beginning of your email you mention that you don't want to use it, so what's the problem here?

 
I want to reuse established connection with the Oracle DB (I thought django connection helps with that out of the box). This is my first experience with Oracle DB.

--

@Jani Tiaine, thanks for the help, but it doesn't work:

"ora_cur = connection.connection.cursor()  # See double connection stuff"

Exception Type:	AttributeError
Exception Value:	'NoneType' object has no attribute 'cursor'

[Jani Tiainen]

Oh sorry about that. It seems that connection attribute is only populated
when you have connection opened for something.

So you have to do initial dummy stuff:

connection.cursor() # Opens connection

curs = connection.connection.cursor() # Get raw cursor

Or, alternatively as a one liner:

curs = connection.cursor().cursor.cursor # Get raw cursor


Hope that helps.

--

Jani Tiainen