cx-oracle-users — Primary discussion list for users (and developers) of cx_Oracle

[cx-oracle-users] setinputsizes (Was: Inserting on table where is CLOB column)

[Jani T. <re...@lu...>]

Jani Tiainen kirjoitti:

> In application code insert clause is in form:
> 
> sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
> params = (1, 'foobar', 'verylongfoobar')
> 
> This is converted to format:
> 
> sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2, 
> :val3)"
> params = {'val1' : 1,  'val2' : 'foobar', 'val3' : 'verylongfoobar'}
> 
> Now this works as long as string I'm trying to insert doesn't exceed 
> 4000 chars. If that happens it tries insert LONG to CLOB column.
> 
> I've tried following piece of code:
> 
> # Adjust inputsizes..
> inputsizes = {}
> for k, v in params.items():
>     if type(v) is str and len(v) > 4000:
>             inputsizes[k] = cx_Oracle.CLOB
>     else:
>             inputsizes[k] = None
> 
> cursor.setinputsizes(inputsizes)
> 
> And I always endup having "cx_Oracle.NotSupportedError: 
> Variable_TypeByPythonType(): unhandled data type" error. Even I remove 
> else-branch final result is still same.
> 
> Even I used cx_Oracle.STRING instead of None result was same.
> 

Only occassion when abowe works is when named parameters are passed.

Even following doesn't seem to work:

cursor.setinputvalues([None, cx_Oracle.CLOB])
cursor.execute("INSERT INTO FOOBAR(ID, CLOB_VAR) VALUES (:1, :2)",
	[{'1' : 1002}, {'2' : 'longfoobartext'}])

Any ideas why it always gives error except in case of named params?

-- 

Jani Tiainen

Re: [cx-oracle-users] Session pool

[Richard M. <ri...@we...>]

Anthony Tuininga wrote:
> Its not much of an example but there really isn't much that is
> different from using connections the normal way.
> 
> import cx_Oracle
> 
> pool = cx_Oracle.SessionPool(user, password, tnsentry, minConnections,
>         maxConnections, increment)
> connection = pool.acquire()

That's a big help, thanks.

Rich.
-- 
Richard Moore, Principal Software Engineer,
Westpoint Ltd,
Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
Tel: +44 161 237 1028
Fax: +44 161 237 1031

Re: [cx-oracle-users] Help with fetchraw

[Chris D. <cdu...@ya...>]

--- Leith Parkin <lei...@gm...> wrote:

> Hi Anthony,
> 
> Chris mentioned in an earlier post that the select results are being
> used to create and insert into another database, not Oracle, otherwise
> you wouldnt even use Python, just insert into select syntax.

Sorry but I didn't make myself totally clear. I am inserting into another database but it is still
Oracle. However I can't make use of insert into select syntax because as I'd said earlier I can't
use database links for lots of reasons I can't go into here. (I'd really like to use insert into
select as it would make life much easier and faster, but I can't, never mind.)

> 
> The problem as I understand it is that Oracle empty varchar2's are
> returned as NULL rather than the python ''. Either way i think Chris
> has enough information to do what he has been looking for now.

Yes I have enough info now, so thanks very much.

Chris

> 
> Regards,
> 
> Leith
> 
> On 5/18/05, Anthony Tuininga <ant...@gm...> wrote:
> > On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> > >
> > > --- Anthony Tuininga <ant...@gm...> wrote:
> > > > 1) You should be able to do something like this without the need to
> > > > descend into fetchraw().
> > > >
> > > > import cx_Oracle
> > > >
> > > > fromConnection = cx_Oracle.Connection("user/pw@tns")
> > > > toConnection = cx_Oracle.Connection("user/pw@tns")
> > > > fromCursor = fromConnection.cursor()
> > > > fromCursor.arraysize = 250 # set as desired
> > > > toCursor = toConnection.cursor()
> > > > toCursor.arraysize = 250 # set as desired, same as fromCursor
> > > >
> > > > fromCursor.execute("some query")
> > > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > > while True:
> > > >     data = fromCursor.fetchmany()
> > > >     if not data:
> > > >         break
> > > >     toCursor.executemany("some insert statement", data)
> > > >     toConnection.commit() # if desired
> > > >
> > > > That should do it and should perform quite well. The fetchraw(),
> > > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > > avoid the overhead of creating Python objects for the data. Its more
> > > > complicated than the above code but I can give that as an example as
> > > > well if you really want it.
> > > >
> > >
> > > I've done essentially the same as you've got above and yes it works fine with pretty good
> > > performance. I'm just trying to improve it further as there are cases when the app will be
> running
> > > a number of parallel streams each selecting and inserting millions of rows in a fixed window
> so
> > > saving 5% - 15% of the Python CPU time within this loop is definately worth doing. I'd like
> a
> > > fetchraw example so I can benchmark it against my current approach. I'll then post the
> results so
> > > everyone can see the general sort of performance differences between the two.
> > 
> > The following is the code for using fetchraw(). My own preliminary
> > results show that in fact it is slower than the original method above
> > that I showed you and is more complicated to boot. The fact that the
> > data can be passed from the one cursor to the other directly and that
> > the bindvar.copy() command must be called for each bind variable for
> > each row makes the overhead of fetching Python objects less than the
> > overhead of calling the bindvar.copy() methods. The fetchraw() method
> > was considerably faster when lists of dictionaries needed to be passed
> > through to executemany().
> > 
> > import cx_Oracle
> > 
> > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > sourceCursor = sourceConnection.cursor()
> > sourceCursor.arraysize = 250
> > sourceVars = sourceCursor.execute("select * from testdata_source")
> > 
> > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > targetCursor = targetConnection.cursor()
> > targetCursor.execute("truncate table testdata_copy")
> > targetCursor.arraysize = 250
> > targetVars = targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DATETIME,
> >         cx_Oracle.NUMBER, cx_Oracle.STRING)
> > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> > 
> > vars = zip(sourceVars, targetVars)
> > indexes = range(sourceCursor.arraysize)
> > while True:
> >     rowsFetched = sourceCursor.fetchraw()
> >     print "fetched", rowsFetched, "rows"
> >     if not rowsFetched:
> >         break
> >     if rowsFetched != sourceCursor.arraysize:
> >         indexes = range(rowsFetched)
> >     for index in indexes:
> >         for sourceVar, targetVar in vars:
> >             targetVar.copy(sourceVar, index, index)
> >     targetCursor.executemanyprepared(rowsFetched)
> >     targetConnection.commit()
> > 
> > Another method which also works but only when the exact data is being
> > replicated from one cursor to another is the following. It is faster
> > than both methods but limited in usefulness of course.
> > 
> > import cx_Oracle
> > 
> > sourceConnection = cx_Oracle.Connection("user/pw@tns")
> > sourceCursor = sourceConnection.cursor()
> > sourceCursor.arraysize = 250
> > sourceVars = sourceCursor.execute("select * from testdata_source")
> > 
> > targetConnection = cx_Oracle.Connection("user/pw@tns")
> > targetCursor = targetConnection.cursor()
> > targetCursor.execute("truncate table testdata_copy")
> > targetCursor.arraysize = 250
> > targetVars = targetCursor.setinputsizes(*sourceVars)
> > targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
> > 
> > while True:
> >     rowsFetched = sourceCursor.fetchraw()
> >     print "fetched", rowsFetched, "rows"
> >     if not rowsFetched:
> >         break
> >     targetCursor.executemanyprepared(rowsFetched)
> >     targetConnection.commit()
> > 
> > Hope this helps,
> > 
> > Anthony
> > 
> > 
> > -------------------------------------------------------
> > This SF.Net email is sponsored by Oracle Space Sweepstakes
> > Want to be the first software developer in space?
> > Enter now for the Oracle Space Sweepstakes!
> > http://ads.osdn.com/?ad_idt12&alloc_id344&opclick
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> 
> 
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id344&op=click
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> 



		
__________________________________ 
Yahoo! Mail Mobile 
Take Yahoo! Mail with you! Check email on your mobile phone. 
http://mobile.yahoo.com/learn/mail 

Re: [cx-oracle-users] Help with fetchraw

[Leith P. <lei...@gm...>]

Hi Anthony,

Chris mentioned in an earlier post that the select results are being
used to create and insert into another database, not Oracle, otherwise
you wouldnt even use Python, just insert into select syntax.

The problem as I understand it is that Oracle empty varchar2's are
returned as NULL rather than the python ''. Either way i think Chris
has enough information to do what he has been looking for now.

Regards,

Leith

On 5/18/05, Anthony Tuininga <ant...@gm...> wrote:
> On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
> >
> > --- Anthony Tuininga <ant...@gm...> wrote:
> > > 1) You should be able to do something like this without the need to
> > > descend into fetchraw().
> > >
> > > import cx_Oracle
> > >
> > > fromConnection =3D cx_Oracle.Connection("user/pw@tns")
> > > toConnection =3D cx_Oracle.Connection("user/pw@tns")
> > > fromCursor =3D fromConnection.cursor()
> > > fromCursor.arraysize =3D 250 # set as desired
> > > toCursor =3D toConnection.cursor()
> > > toCursor.arraysize =3D 250 # set as desired, same as fromCursor
> > >
> > > fromCursor.execute("some query")
> > > toCursor.setinputsizes() # set things up if nulls are allowed
> > > while True:
> > >     data =3D fromCursor.fetchmany()
> > >     if not data:
> > >         break
> > >     toCursor.executemany("some insert statement", data)
> > >     toConnection.commit() # if desired
> > >
> > > That should do it and should perform quite well. The fetchraw(),
> > > bindvar.copy() and executemanyprepared() trio were created solely to
> > > avoid the overhead of creating Python objects for the data. Its more
> > > complicated than the above code but I can give that as an example as
> > > well if you really want it.
> > >
> >
> > I've done essentially the same as you've got above and yes it works fin=
e with pretty good
> > performance. I'm just trying to improve it further as there are cases w=
hen the app will be running
> > a number of parallel streams each selecting and inserting millions of r=
ows in a fixed window so
> > saving 5% - 15% of the Python CPU time within this loop is definately w=
orth doing. I'd like a
> > fetchraw example so I can benchmark it against my current approach. I'l=
l then post the results so
> > everyone can see the general sort of performance differences between th=
e two.
>=20
> The following is the code for using fetchraw(). My own preliminary
> results show that in fact it is slower than the original method above
> that I showed you and is more complicated to boot. The fact that the
> data can be passed from the one cursor to the other directly and that
> the bindvar.copy() command must be called for each bind variable for
> each row makes the overhead of fetching Python objects less than the
> overhead of calling the bindvar.copy() methods. The fetchraw() method
> was considerably faster when lists of dictionaries needed to be passed
> through to executemany().
>=20
> import cx_Oracle
>=20
> sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
> sourceCursor =3D sourceConnection.cursor()
> sourceCursor.arraysize =3D 250
> sourceVars =3D sourceCursor.execute("select * from testdata_source")
>=20
> targetConnection =3D cx_Oracle.Connection("user/pw@tns")
> targetCursor =3D targetConnection.cursor()
> targetCursor.execute("truncate table testdata_copy")
> targetCursor.arraysize =3D 250
> targetVars =3D targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DAT=
ETIME,
>         cx_Oracle.NUMBER, cx_Oracle.STRING)
> targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
>=20
> vars =3D zip(sourceVars, targetVars)
> indexes =3D range(sourceCursor.arraysize)
> while True:
>     rowsFetched =3D sourceCursor.fetchraw()
>     print "fetched", rowsFetched, "rows"
>     if not rowsFetched:
>         break
>     if rowsFetched !=3D sourceCursor.arraysize:
>         indexes =3D range(rowsFetched)
>     for index in indexes:
>         for sourceVar, targetVar in vars:
>             targetVar.copy(sourceVar, index, index)
>     targetCursor.executemanyprepared(rowsFetched)
>     targetConnection.commit()
>=20
> Another method which also works but only when the exact data is being
> replicated from one cursor to another is the following. It is faster
> than both methods but limited in usefulness of course.
>=20
> import cx_Oracle
>=20
> sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
> sourceCursor =3D sourceConnection.cursor()
> sourceCursor.arraysize =3D 250
> sourceVars =3D sourceCursor.execute("select * from testdata_source")
>=20
> targetConnection =3D cx_Oracle.Connection("user/pw@tns")
> targetCursor =3D targetConnection.cursor()
> targetCursor.execute("truncate table testdata_copy")
> targetCursor.arraysize =3D 250
> targetVars =3D targetCursor.setinputsizes(*sourceVars)
> targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")
>=20
> while True:
>     rowsFetched =3D sourceCursor.fetchraw()
>     print "fetched", rowsFetched, "rows"
>     if not rowsFetched:
>         break
>     targetCursor.executemanyprepared(rowsFetched)
>     targetConnection.commit()
>=20
> Hope this helps,
>=20
> Anthony
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Help with fetchraw

[Anthony T. <ant...@gm...>]

On 5/17/05, Chris Dunscombe <cdu...@ya...> wrote:
>=20
> --- Anthony Tuininga <ant...@gm...> wrote:
> > 1) You should be able to do something like this without the need to
> > descend into fetchraw().
> >
> > import cx_Oracle
> >
> > fromConnection =3D cx_Oracle.Connection("user/pw@tns")
> > toConnection =3D cx_Oracle.Connection("user/pw@tns")
> > fromCursor =3D fromConnection.cursor()
> > fromCursor.arraysize =3D 250 # set as desired
> > toCursor =3D toConnection.cursor()
> > toCursor.arraysize =3D 250 # set as desired, same as fromCursor
> >
> > fromCursor.execute("some query")
> > toCursor.setinputsizes() # set things up if nulls are allowed
> > while True:
> >     data =3D fromCursor.fetchmany()
> >     if not data:
> >         break
> >     toCursor.executemany("some insert statement", data)
> >     toConnection.commit() # if desired
> >
> > That should do it and should perform quite well. The fetchraw(),
> > bindvar.copy() and executemanyprepared() trio were created solely to
> > avoid the overhead of creating Python objects for the data. Its more
> > complicated than the above code but I can give that as an example as
> > well if you really want it.
> >
>=20
> I've done essentially the same as you've got above and yes it works fine =
with pretty good
> performance. I'm just trying to improve it further as there are cases whe=
n the app will be running
> a number of parallel streams each selecting and inserting millions of row=
s in a fixed window so
> saving 5% - 15% of the Python CPU time within this loop is definately wor=
th doing. I'd like a
> fetchraw example so I can benchmark it against my current approach. I'll =
then post the results so
> everyone can see the general sort of performance differences between the =
two.

The following is the code for using fetchraw(). My own preliminary
results show that in fact it is slower than the original method above
that I showed you and is more complicated to boot. The fact that the
data can be passed from the one cursor to the other directly and that
the bindvar.copy() command must be called for each bind variable for
each row makes the overhead of fetching Python objects less than the
overhead of calling the bindvar.copy() methods. The fetchraw() method
was considerably faster when lists of dictionaries needed to be passed
through to executemany().

import cx_Oracle

sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
sourceCursor =3D sourceConnection.cursor()
sourceCursor.arraysize =3D 250
sourceVars =3D sourceCursor.execute("select * from testdata_source")

targetConnection =3D cx_Oracle.Connection("user/pw@tns")
targetCursor =3D targetConnection.cursor()
targetCursor.execute("truncate table testdata_copy")
targetCursor.arraysize =3D 250
targetVars =3D targetCursor.setinputsizes(cx_Oracle.NUMBER, cx_Oracle.DATET=
IME,
        cx_Oracle.NUMBER, cx_Oracle.STRING)
targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")

vars =3D zip(sourceVars, targetVars)
indexes =3D range(sourceCursor.arraysize)
while True:
    rowsFetched =3D sourceCursor.fetchraw()
    print "fetched", rowsFetched, "rows"
    if not rowsFetched:
        break
    if rowsFetched !=3D sourceCursor.arraysize:
        indexes =3D range(rowsFetched)
    for index in indexes:
        for sourceVar, targetVar in vars:
            targetVar.copy(sourceVar, index, index)
    targetCursor.executemanyprepared(rowsFetched)
    targetConnection.commit()

Another method which also works but only when the exact data is being
replicated from one cursor to another is the following. It is faster
than both methods but limited in usefulness of course.

import cx_Oracle

sourceConnection =3D cx_Oracle.Connection("user/pw@tns")
sourceCursor =3D sourceConnection.cursor()
sourceCursor.arraysize =3D 250
sourceVars =3D sourceCursor.execute("select * from testdata_source")

targetConnection =3D cx_Oracle.Connection("user/pw@tns")
targetCursor =3D targetConnection.cursor()
targetCursor.execute("truncate table testdata_copy")
targetCursor.arraysize =3D 250
targetVars =3D targetCursor.setinputsizes(*sourceVars)
targetCursor.prepare("insert into testdata_copy values (:1, :2, :3, :4)")

while True:
    rowsFetched =3D sourceCursor.fetchraw()
    print "fetched", rowsFetched, "rows"
    if not rowsFetched:
        break
    targetCursor.executemanyprepared(rowsFetched)
    targetConnection.commit()

Hope this helps,

Anthony

[cx-oracle-users] Re: Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> 
>>Anthony Tuininga kirjoitti:
>>
>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>
>>>
>>>>>BTW, if you __really__ have no control, you can always subclass
>>>>>Connection and Cursor and do whatever you need to do. Subclassing has
>>>>>been quite convenient and I use it myself for a number of situations
>>>>>where the code is used by multiple database adapters.
>>>>
>>>>Any pointers in web (example would be nice) since I'm just becoming
>>>>friend of Python and I might be trying to do things wrong here...
>>>
>>>
>>>Recent e-mails to this list have had some examples. Subclassing
>>>cx_Oracle.Connection and cx_Oracle.Cursor are the same as sublcassing
>>>any other class in Python -- not very helpful if you haven't done much
>>>of it yourself yet, I guess. :-)
>>
>>Well I have something like following:
>>
>>query = "insert into foobar (id, name) values (:1, :2)"
>>values = {'1' : 123, '2' : 'Mr. Dumb'
>>cursor.setinputsizes({'2' : cx_Oracle.CLOB})
>>cursor.execute(query, values)
>>
>>But I'm getting error:
>>Variable_TypeByPythonType(): unhandled data type
>>
>>What I'm doing wrong..?
> 
> 
> Oracle uses :1, :2 for __positional__ arguments and :fred and :george
> for __named__ arguments. cx_Oracle expects a dictionary for named
> arguments and a sequence for positional arguments. So instead of
> passing a dictionary as the arguments, pass a list instead and that
> should solve your problems. In addition, setinputsizes() uses the
> *args and **kwargs notation so you should specify the following:
> 
> cursor.setinputsizes([None, cx_Oracle.CLOB]) OR

I got this never working... It just says "cx_Oracle.ProgrammingError: 
number of elements must be an integer"

> cursor.setinputsizes(value = cx_Oracle.CLOB)
> 
> and reference :value in your statement. Make sense?

Yes, but... I think I pasted too short description of my problem.

In application code insert clause is in form:

sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (%s, %s, %s)"
params = (1, 'foobar', 'verylongfoobar')

This is converted to format:

sql = "INSERT INTO FOOBAR (ID, VALUE, CLOB_VALUE) VALUES (:val1, :val2, 
:val3)"
params = {'val1' : 1,  'val2' : 'foobar', 'val3' : 'verylongfoobar'}

Now this works as long as string I'm trying to insert doesn't exceed 
4000 chars. If that happens it tries insert LONG to CLOB column.

I've tried following piece of code:

# Adjust inputsizes..
inputsizes = {}
for k, v in params.items():
	if type(v) is str and len(v) > 4000:
         	inputsizes[k] = cx_Oracle.CLOB
	else:
         	inputsizes[k] = None

cursor.setinputsizes(inputsizes)

And I always endup having "cx_Oracle.NotSupportedError: 
Variable_TypeByPythonType(): unhandled data type" error. Even I remove 
else-branch final result is still same.

Even I used cx_Oracle.STRING instead of None result was same.

-- 

Jani Tiainen

Re: [cx-oracle-users] Re: Inserting on table where is CLOB column

[Anthony T. <ant...@gm...>]

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Anthony Tuininga kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>>BTW, if you __really__ have no control, you can always subclass
> >>>Connection and Cursor and do whatever you need to do. Subclassing has
> >>>been quite convenient and I use it myself for a number of situations
> >>>where the code is used by multiple database adapters.
> >>
> >>Any pointers in web (example would be nice) since I'm just becoming
> >>friend of Python and I might be trying to do things wrong here...
> >
> >
> > Recent e-mails to this list have had some examples. Subclassing
> > cx_Oracle.Connection and cx_Oracle.Cursor are the same as sublcassing
> > any other class in Python -- not very helpful if you haven't done much
> > of it yourself yet, I guess. :-)
>=20
> Well I have something like following:
>=20
> query =3D "insert into foobar (id, name) values (:1, :2)"
> values =3D {'1' : 123, '2' : 'Mr. Dumb'
> cursor.setinputsizes({'2' : cx_Oracle.CLOB})
> cursor.execute(query, values)
>=20
> But I'm getting error:
> Variable_TypeByPythonType(): unhandled data type
>=20
> What I'm doing wrong..?

Oracle uses :1, :2 for __positional__ arguments and :fred and :george
for __named__ arguments. cx_Oracle expects a dictionary for named
arguments and a sequence for positional arguments. So instead of
passing a dictionary as the arguments, pass a list instead and that
should solve your problems. In addition, setinputsizes() uses the
*args and **kwargs notation so you should specify the following:

cursor.setinputsizes([None, cx_Oracle.CLOB]) OR

cursor.setinputsizes(value =3D cx_Oracle.CLOB)

and reference :value in your statement. Make sense?

> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> 
>>>BTW, if you __really__ have no control, you can always subclass
>>>Connection and Cursor and do whatever you need to do. Subclassing has
>>>been quite convenient and I use it myself for a number of situations
>>>where the code is used by multiple database adapters.
>>
>>Any pointers in web (example would be nice) since I'm just becoming
>>friend of Python and I might be trying to do things wrong here...
> 
> 
> Recent e-mails to this list have had some examples. Subclassing
> cx_Oracle.Connection and cx_Oracle.Cursor are the same as sublcassing
> any other class in Python -- not very helpful if you haven't done much
> of it yourself yet, I guess. :-)

Well I have something like following:

query = "insert into foobar (id, name) values (:1, :2)"
values = {'1' : 123, '2' : 'Mr. Dumb'
cursor.setinputsizes({'2' : cx_Oracle.CLOB})
cursor.execute(query, values)

But I'm getting error:
Variable_TypeByPythonType(): unhandled data type

What I'm doing wrong..?

Re: [cx-oracle-users] Re: Inserting on table where is CLOB column

[Anthony T. <ant...@gm...>]

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> > BTW, if you __really__ have no control, you can always subclass
> > Connection and Cursor and do whatever you need to do. Subclassing has
> > been quite convenient and I use it myself for a number of situations
> > where the code is used by multiple database adapters.
>=20
> Any pointers in web (example would be nice) since I'm just becoming
> friend of Python and I might be trying to do things wrong here...

Recent e-mails to this list have had some examples. Subclassing
cx_Oracle.Connection and cx_Oracle.Cursor are the same as sublcassing
any other class in Python -- not very helpful if you haven't done much
of it yourself yet, I guess. :-)

> But this has helped me a bit...

Glad to hear it.

> --
>=20
> Jani Tiainen
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] set{input,output}sizes() (changed subject)

[Anthony T. <ant...@gm...>]

On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> > Null in database speak still has a type. In other words, there is a
> > difference between a null number and a null string and a null date.
> > Python does not have such a distinction so cx_Oracle simply assumes
> > string. In many cases this won't be a problem even if the underlying
> > data type is number since Oracle implicitly converts data types but in
> > other cases it really matters so you need to specify.
>=20
> Can you give an example of where it matters?  (We've got a lot of
> cx_Oracle code in-house here and it has never been an issue AFAIK.)

It matters when you are performing executemany() if one of the first
values are null and one of the remaining values are not null and of a
different data type than string. It also matters when binding arrays.
In other cases, cx_Oracle will rebind a new variable of the correct
type and size which slows things down but otherwise nothing untoward
takes place. Does that explain it?

> > Interfaces are always interesting since the viewpoints are often
> > considerably different. Most times the interface is seamless but there
> > are a few instances where the database viewpoint pokes out. :-) Any
> > suggestions on how to improve this are welcome, of course!
>=20
> So true.
>=20
> > > example, when inserting a 100K-long string into a table column whose
> > > type is CLOB, why would I have to say that it's a CLOB again?
> >
> > Any string longer than 4000 characters cannot be represented as
> > varchar2 but there are several options: long, long raw, CLOB, BLOB so
> > which one gets chosen? At the moment cx_Oracle does not guess at all
> > and simply allows the error to be propagated. Again, suggestions are
> > welcome!
>=20
> It would depend on what happens if you guessed wrong.  For example, if
> the conversion to CLOB were lossless and Oracle would automatically
> convert a CLOB to the required type, you could guess CLOB and the worst
> that could happen is that things slow down.  But if the conversion is
> lossy or Oracle bites you instead of converting, the status quo seems as
> good as it gets.  I guess one of the problems is that you have to pick
> one of the Oracle types without knowing the type that's actually
> required.

Guessing wrong would cause Oracle to blow up so I think the status quo
is the best of a bad situation.

> BTW, this reminds me.  What would be the disadvantage of defaulting
> arraysize to something large (e.g. 1000, which we've found to work well
> for our code) other than some wasted memory?  How can I calculate the
> memory needed when I set arraysize to a certain value?

Memory is the reason for limiting this and there is some overhead in
preparing the bind variable buffers for use, in particular with BLOB
and CLOB variables where a descriptor must be allocated for each row.
As for calculating the memory you'd have to look at the OCI (or
cx_Oracle) to determine the sizes. It depends on the data type and
size selected -- either implicitly when the variable is bound or
explicitly when setinputsizes() is called.

BTW, in terms of performance, the difference between the different
array sizes is the amount of round trips performed. In general on a
reasonably fast network there is not much difference between an array
size of 50 and an array size of 1000. So it all depends on how much
RAM you have to burn and how important performance is -- the typical
tradeoff. :-)

> --Guido van Rossum (home page: http://www.python.org/~guido)
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: set{input,output}sizes() (changed subject)

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> 
>>example, when inserting a 100K-long string into a table column whose
>>type is CLOB, why would I have to say that it's a CLOB again?
> 
> 
> Any string longer than 4000 characters cannot be represented as
> varchar2 but there are several options: long, long raw, CLOB, BLOB so
> which one gets chosen? At the moment cx_Oracle does not guess at all
> and simply allows the error to be propagated. Again, suggestions are
> welcome!

Actually I found out that it really tries > 4k chars to insert as LONG . 
Why , I've no idea (happens at least with cx_Oracle 4.1).

Well problem is that LONG is deprecated type, and you can have only one 
LONG column in table. So I think if string is > 4k it would be safe to 
assume it as CLOB. Of course this could be connection wide setting 
(outside DB API) to set default type for guess..

-- 

Jani Tiainen

[cx-oracle-users] Re: Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> 
>>Anthony Tuininga kirjoitti:
>>
>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>
>>>
>>>>Charl P. Botha kirjoitti:
>>>>
>>>>
>>>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>>>
>>>>>
>>>>>
>>>>>>When I'm trying to insert long text in CLOB column I get error
>>>>>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>>>>>
>>>>>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>>>>>
>>>>>>When value is less than 4000 chars inserts goes well, but after breaking
>>>>>>barrier problem occurs.
>>>>>>
>>>>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
>>>>>
>>>>>
>>>>>It looks like you have to use bind variables.  This is from an example
>>>>>Anthony posted on this list to answer one of my questions:
>>>>>
>>>>>connection = cx_Oracle.Connection("user/pw@tns")
>>>>>cursor = connection.cursor()
>>>>>cursor.setinputsizes(value = cx_Oracle.CLOB)
>>>>>cursor.execute("insert into table values (:value)",
>>>>>      value = "A very long string")
>>>>
>>>>Didn't saw that in list. It would solve my problem if I knew queries
>>>>before, but...
>>>>
>>>>Problem is that I haven't got control over bindings since code is
>>>>basically just code below:
>>>>
>>>>def runquery(cursor, query, args):
>>>>       return cursor.execute(query, args)
>>>
>>>
>>>The args __are__ the bind variables so you definitely have control.
>>>You simply have to make sure you call cursor.setinputsizes() before
>>>calling runquery().
>>
>>Well, they are, but I don't have control over them.. :)
>>
>>Since same call in code is used for different database backends (like
>>Postgres)
> 
> 
> Ah, I see. That does make life a little more difficult.
> 

And now you tell me that..? :)

>>This is somewhat possible to fix in current situation since I know that
>>strings that are > 4k must be clobs...
> 
> 
> Not necessarily in general but that could be true in your application.
> You also have long, long raw and BLOB to deal with. If your
> application doesn't use them you can make this assumption.

Well fortunately (and currently) this is situation...

> BTW, if you __really__ have no control, you can always subclass
> Connection and Cursor and do whatever you need to do. Subclassing has
> been quite convenient and I use it myself for a number of situations
> where the code is used by multiple database adapters.

Any pointers in web (example would be nice) since I'm just becoming 
friend of Python and I might be trying to do things wrong here...

But this has helped me a bit...

-- 

Jani Tiainen

RE: [cx-oracle-users] set{input,output}sizes() (changed subject)

[Guido v. R. <gu...@el...>]

> Null in database speak still has a type. In other words, there is a
> difference between a null number and a null string and a null date.
> Python does not have such a distinction so cx_Oracle simply assumes
> string. In many cases this won't be a problem even if the underlying
> data type is number since Oracle implicitly converts data types but in
> other cases it really matters so you need to specify.

Can you give an example of where it matters?  (We've got a lot of
cx_Oracle code in-house here and it has never been an issue AFAIK.)

> Interfaces are always interesting since the viewpoints are often
> considerably different. Most times the interface is seamless but there
> are a few instances where the database viewpoint pokes out. :-) Any
> suggestions on how to improve this are welcome, of course!

So true.

> > example, when inserting a 100K-long string into a table column whose
> > type is CLOB, why would I have to say that it's a CLOB again?
>=20
> Any string longer than 4000 characters cannot be represented as
> varchar2 but there are several options: long, long raw, CLOB, BLOB so
> which one gets chosen? At the moment cx_Oracle does not guess at all
> and simply allows the error to be propagated. Again, suggestions are
> welcome!

It would depend on what happens if you guessed wrong.  For example, if
the conversion to CLOB were lossless and Oracle would automatically
convert a CLOB to the required type, you could guess CLOB and the worst
that could happen is that things slow down.  But if the conversion is
lossy or Oracle bites you instead of converting, the status quo seems as
good as it gets.  I guess one of the problems is that you have to pick
one of the Oracle types without knowing the type that's actually
required.

BTW, this reminds me.  What would be the disadvantage of defaulting
arraysize to something large (e.g. 1000, which we've found to work well
for our code) other than some wasted memory?  How can I calculate the
memory needed when I set arraysize to a certain value?

--Guido van Rossum (home page: http://www.python.org/~guido)

Re: [cx-oracle-users] Re: Inserting on table where is CLOB column

[Anthony T. <ant...@gm...>]

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Anthony Tuininga kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>Charl P. Botha kirjoitti:
> >>
> >>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >>>
> >>>
> >>>>When I'm trying to insert long text in CLOB column I get error
> >>>>ORA-01461. Something like LONG value can be only to be bind in LONG c=
olumn.
> >>>>
> >>>>It should be CLOB instead of LONG. Is there way to get around this pr=
oblem?
> >>>>
> >>>>When value is less than 4000 chars inserts goes well, but after break=
ing
> >>>>barrier problem occurs.
> >>>>
> >>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
> >>>
> >>>
> >>>It looks like you have to use bind variables.  This is from an example
> >>>Anthony posted on this list to answer one of my questions:
> >>>
> >>>connection =3D cx_Oracle.Connection("user/pw@tns")
> >>>cursor =3D connection.cursor()
> >>>cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> >>>cursor.execute("insert into table values (:value)",
> >>>       value =3D "A very long string")
> >>
> >>Didn't saw that in list. It would solve my problem if I knew queries
> >>before, but...
> >>
> >>Problem is that I haven't got control over bindings since code is
> >>basically just code below:
> >>
> >>def runquery(cursor, query, args):
> >>        return cursor.execute(query, args)
> >
> >
> > The args __are__ the bind variables so you definitely have control.
> > You simply have to make sure you call cursor.setinputsizes() before
> > calling runquery().
>=20
> Well, they are, but I don't have control over them.. :)
>=20
> Since same call in code is used for different database backends (like
> Postgres)

Ah, I see. That does make life a little more difficult.

> This is somewhat possible to fix in current situation since I know that
> strings that are > 4k must be clobs...

Not necessarily in general but that could be true in your application.
You also have long, long raw and BLOB to deal with. If your
application doesn't use them you can make this assumption.

BTW, if you __really__ have no control, you can always subclass
Connection and Cursor and do whatever you need to do. Subclassing has
been quite convenient and I use it myself for a number of situations
where the code is used by multiple database adapters.

> --
>=20
> Jani Tiainen
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] set{input,output}sizes() (changed subject)

[Anthony T. <ant...@gm...>]

On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> > setinputsizes() is used to define what type (and size) of data is
> > being bound to the cursor. In most cases cx_Oracle (and other database
> > access modules) can guess the type from the type of the Python value
> > that is bound but the Python None value (representing null in database
> > terms) doesn't have any type that corresponds to any database type.
> > Thus, if you want to bind a NULL or anything else (like CLOB/BLOB)
> > where the database type and size cannot be implied from the Python
> > type and size you want to use setinputsizes().
> >
> > setoutputsizes() is for defining the size of data that is being
> > fetched from the database. In particular it is used for variable
> > length data (like long and long raw in Oracle) where the size cannot
> > be known by retrieving the information from the database. If you never
> > use long or long raw (and Oracle recommends that you use CLOB and BLOB
> > instead) then you never need to know about this function.
> >
> > Does that help?? How many bonus points do I get?? :-)
>=20
> Sortof.  I'm surprised that NULL (which is supported in SQL) is unknown
> at this level, which makes me wonder if I understand any of the rest!

Null in database speak still has a type. In other words, there is a
difference between a null number and a null string and a null date.
Python does not have such a distinction so cx_Oracle simply assumes
string. In many cases this won't be a problem even if the underlying
data type is number since Oracle implicitly converts data types but in
other cases it really matters so you need to specify.

> I guess I'm so comfortable with the abstractions that SQL and Python
> provide that I have a hard time understanding the precise nature of the
> hoops that you have to jump through to tie the two together; for

Interfaces are always interesting since the viewpoints are often
considerably different. Most times the interface is seamless but there
are a few instances where the database viewpoint pokes out. :-) Any
suggestions on how to improve this are welcome, of course!

> example, when inserting a 100K-long string into a table column whose
> type is CLOB, why would I have to say that it's a CLOB again?

Any string longer than 4000 characters cannot be represented as
varchar2 but there are several options: long, long raw, CLOB, BLOB so
which one gets chosen? At the moment cx_Oracle does not guess at all
and simply allows the error to be propagated. Again, suggestions are
welcome!

> --Guido van Rossum (home page: http://www.python.org/~guido)
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

Anthony Tuininga kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> 
>>Charl P. Botha kirjoitti:
>>
>>>On 5/17/05, Jani Tiainen <re...@lu...> wrote:
>>>
>>>
>>>>When I'm trying to insert long text in CLOB column I get error
>>>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>>>
>>>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>>>
>>>>When value is less than 4000 chars inserts goes well, but after breaking
>>>>barrier problem occurs.
>>>>
>>>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
>>>
>>>
>>>It looks like you have to use bind variables.  This is from an example
>>>Anthony posted on this list to answer one of my questions:
>>>
>>>connection = cx_Oracle.Connection("user/pw@tns")
>>>cursor = connection.cursor()
>>>cursor.setinputsizes(value = cx_Oracle.CLOB)
>>>cursor.execute("insert into table values (:value)",
>>>       value = "A very long string")
>>
>>Didn't saw that in list. It would solve my problem if I knew queries
>>before, but...
>>
>>Problem is that I haven't got control over bindings since code is
>>basically just code below:
>>
>>def runquery(cursor, query, args):
>>        return cursor.execute(query, args)
> 
> 
> The args __are__ the bind variables so you definitely have control.
> You simply have to make sure you call cursor.setinputsizes() before
> calling runquery().

Well, they are, but I don't have control over them.. :)

Since same call in code is used for different database backends (like 
Postgres)

This is somewhat possible to fix in current situation since I know that 
strings that are > 4k must be clobs...

-- 

Jani Tiainen

[cx-oracle-users] set{input,output}sizes() (changed subject)

[Guido v. R. <gu...@el...>]

> setinputsizes() is used to define what type (and size) of data is
> being bound to the cursor. In most cases cx_Oracle (and other database
> access modules) can guess the type from the type of the Python value
> that is bound but the Python None value (representing null in database
> terms) doesn't have any type that corresponds to any database type.
> Thus, if you want to bind a NULL or anything else (like CLOB/BLOB)
> where the database type and size cannot be implied from the Python
> type and size you want to use setinputsizes().
>=20
> setoutputsizes() is for defining the size of data that is being
> fetched from the database. In particular it is used for variable
> length data (like long and long raw in Oracle) where the size cannot
> be known by retrieving the information from the database. If you never
> use long or long raw (and Oracle recommends that you use CLOB and BLOB
> instead) then you never need to know about this function.
>=20
> Does that help?? How many bonus points do I get?? :-)

Sortof.  I'm surprised that NULL (which is supported in SQL) is unknown
at this level, which makes me wonder if I understand any of the rest!

I guess I'm so comfortable with the abstractions that SQL and Python
provide that I have a hard time understanding the precise nature of the
hoops that you have to jump through to tie the two together; for
example, when inserting a 100K-long string into a table column whose
type is CLOB, why would I have to say that it's a CLOB again?

--Guido van Rossum (home page: http://www.python.org/~guido)

Re: [cx-oracle-users] python 2.3.4 + cx_oracle 4.1 + utf-8 error

[Anthony T. <ant...@gm...>]

I'm not surprised. With CLOB values, this problem can be avoided but
with varchar2 columns the size is in __bytes__ not characters so you
need to increase the size of your columns appropriately -- at least
that's my understanding from reading Oracle's manuals. If you have
something that suggests otherwise I'd be happy to read it. :-) I've
made the necessary adjustment for CLOB values with UTF-8 (and other
character sets) in cx_Oracle 4.1 and I haven't heard any complaints so
I guess I got it right. I didn't put a similar thing on varchar2
columns because of the constraint noted above. Hope that helps.

On 5/12/05, Maxim Kuleshov <kul...@cs...> wrote:
> Hello!
>=20
> Trying to fetch long varchar2 column and get the following error:
>=20
> cx_Oracle.DatabaseError: column at array pos 0 fetched with error: 1406
>=20
> i.e. string buffer is not much enough to fetch the string.
>=20
> # fragment of code...
> myCon =3D cx_Oracle.connect(user, psw, dsn)
> myCur =3D myCon.cursor()
> myCur.execute("""
> select COLUMN from TABLE where ID=3D1
> """)
> for record in myCur.fetchall():
> # ...
>=20
> Error is reproduced only if actual string value longer than half of
> declared column size. For short strings all is ok.
>=20
> If I print myCur.description, I get:
>=20
> [('COLUMN', <type 'cx_Oracle.STRING'>, 250, 250, 0, 0, 1)]
>=20
> 250 - declared column max size, but I guess cx_Oracle allocates only 250
> bytes(!), so if my string longer than 125 chars (utf-8 national char
> occupies > 1 byte) - I get the error.
>=20
> Is it bug or what? Any suggestions?
>=20
> Extra info:
>      OS - SuSE Linux 9.2
>      Client - Oracle Client 9.2.0.1.0
>      NLS_LANG=3D"RUSSIAN_CIS.UTF8"
>=20
> --
> Best regards,
> Maxim Kuleshov
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7393&alloc_id=3D16281&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Session pool

[Anthony T. <ant...@gm...>]

Its not much of an example but there really isn't much that is
different from using connections the normal way.

import cx_Oracle

pool =3D cx_Oracle.SessionPool(user, password, tnsentry, minConnections,
        maxConnections, increment)
connection =3D pool.acquire()

where minConnections is the minimum number of connections to retain in
the pool, maxConnections is the maximum number of connections in the
the pool and increment is the number of connections to acquire when
there are no free connections in the pool.

After that you can treat the connection as you usually do. Be aware,
however, that package state __is__ retained when the connection is
released back to the pool (which happens when the connection object
goes out of scope or pool.release(connection) is called) so if your
application makes use of that, deal with it appropriately.

On 5/17/05, Richard Moore <ri...@we...> wrote:
> Hi all,
>=20
> Does anyone have an example of how to use the cx_Oracle session
> pool?
>=20
> Cheers
>=20
> Rich.
> --
> Richard Moore, Principal Software Engineer,
> Westpoint Ltd,
> Albion Wharf, 19 Albion Street, Manchester, M1 5LN, England
> Tel: +44 161 237 1028
> Fax: +44 161 237 1031
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Re: Inserting on table where is CLOB column

[Anthony T. <ant...@gm...>]

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> Charl P. Botha kirjoitti:
> > On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> >
> >>When I'm trying to insert long text in CLOB column I get error
> >>ORA-01461. Something like LONG value can be only to be bind in LONG col=
umn.
> >>
> >>It should be CLOB instead of LONG. Is there way to get around this prob=
lem?
> >>
> >>When value is less than 4000 chars inserts goes well, but after breakin=
g
> >>barrier problem occurs.
> >>
> >>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
> >
> >
> > It looks like you have to use bind variables.  This is from an example
> > Anthony posted on this list to answer one of my questions:
> >
> > connection =3D cx_Oracle.Connection("user/pw@tns")
> > cursor =3D connection.cursor()
> > cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> > cursor.execute("insert into table values (:value)",
> >        value =3D "A very long string")
>=20
> Didn't saw that in list. It would solve my problem if I knew queries
> before, but...
>=20
> Problem is that I haven't got control over bindings since code is
> basically just code below:
>=20
> def runquery(cursor, query, args):
>         return cursor.execute(query, args)

The args __are__ the bind variables so you definitely have control.
You simply have to make sure you call cursor.setinputsizes() before
calling runquery().

> Any ideas?
>=20
> --
>=20
> Jani Tiainen
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_id=3D7412&alloc_id=3D16344&op=3Dclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] Inserting on table where is CLOB column

[Anthony T. <ant...@gm...>]

On 5/17/05, Guido van Rossum <gu...@el...> wrote:
> > connection =3D cx_Oracle.Connection("user/pw@tns")
> > cursor =3D connection.cursor()
> > cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> > cursor.execute("insert into table values (:value)",
> >        value =3D "A very long string")
>=20
> setinputsizes() isn't documented very clearly if you don't already know
> what it does.  Could someone here explain its use (and for bonus points,
> the use of setoutputsizes() as well)?

setinputsizes() is used to define what type (and size) of data is
being bound to the cursor. In most cases cx_Oracle (and other database
access modules) can guess the type from the type of the Python value
that is bound but the Python None value (representing null in database
terms) doesn't have any type that corresponds to any database type.
Thus, if you want to bind a NULL or anything else (like CLOB/BLOB)
where the database type and size cannot be implied from the Python
type and size you want to use setinputsizes().

setoutputsizes() is for defining the size of data that is being
fetched from the database. In particular it is used for variable
length data (like long and long raw in Oracle) where the size cannot
be known by retrieving the information from the database. If you never
use long or long raw (and Oracle recommends that you use CLOB and BLOB
instead) then you never need to know about this function.

Does that help?? How many bonus points do I get?? :-)

> --Guido van Rossum (home page: http://www.python.org/~guido)
>=20
>=20
> -------------------------------------------------------
> This SF.Net email is sponsored by Oracle Space Sweepstakes
> Want to be the first software developer in space?
> Enter now for the Oracle Space Sweepstakes!
> http://ads.osdn.com/?ad_idt12&alloc_id=16344&opclick
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] Re: Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

Charl P. Botha kirjoitti:
> On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> 
>>When I'm trying to insert long text in CLOB column I get error
>>ORA-01461. Something like LONG value can be only to be bind in LONG column.
>>
>>It should be CLOB instead of LONG. Is there way to get around this problem?
>>
>>When value is less than 4000 chars inserts goes well, but after breaking
>>barrier problem occurs.
>>
>>Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g
> 
> 
> It looks like you have to use bind variables.  This is from an example
> Anthony posted on this list to answer one of my questions:
> 
> connection = cx_Oracle.Connection("user/pw@tns")
> cursor = connection.cursor()
> cursor.setinputsizes(value = cx_Oracle.CLOB)
> cursor.execute("insert into table values (:value)",
>        value = "A very long string")

Didn't saw that in list. It would solve my problem if I knew queries 
before, but...

Problem is that I haven't got control over bindings since code is 
basically just code below:

def runquery(cursor, query, args):
	return cursor.execute(query, args)


Any ideas?

-- 

Jani Tiainen

RE: [cx-oracle-users] Inserting on table where is CLOB column

[Guido v. R. <gu...@el...>]

> connection =3D cx_Oracle.Connection("user/pw@tns")
> cursor =3D connection.cursor()
> cursor.setinputsizes(value =3D cx_Oracle.CLOB)
> cursor.execute("insert into table values (:value)",
>        value =3D "A very long string")

setinputsizes() isn't documented very clearly if you don't already know
what it does.  Could someone here explain its use (and for bonus points,
the use of setoutputsizes() as well)?

--Guido van Rossum (home page: http://www.python.org/~guido)

Re: [cx-oracle-users] Inserting on table where is CLOB column

[Charl P. B. <cp...@gm...>]

On 5/17/05, Jani Tiainen <re...@lu...> wrote:
> When I'm trying to insert long text in CLOB column I get error
> ORA-01461. Something like LONG value can be only to be bind in LONG colum=
n.
>=20
> It should be CLOB instead of LONG. Is there way to get around this proble=
m?
>=20
> When value is less than 4000 chars inserts goes well, but after breaking
> barrier problem occurs.
>=20
> Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g

It looks like you have to use bind variables.  This is from an example
Anthony posted on this list to answer one of my questions:

connection =3D cx_Oracle.Connection("user/pw@tns")
cursor =3D connection.cursor()
cursor.setinputsizes(value =3D cx_Oracle.CLOB)
cursor.execute("insert into table values (:value)",
       value =3D "A very long string")

HTH,
Charl

--=20
charl p. botha http://cpbotha.net/ http://visualisation.tudelft.nl/

[cx-oracle-users] Inserting on table where is CLOB column

[Jani T. <re...@lu...>]

When I'm trying to insert long text in CLOB column I get error 
ORA-01461. Something like LONG value can be only to be bind in LONG column.

It should be CLOB instead of LONG. Is there way to get around this problem?

When value is less than 4000 chars inserts goes well, but after breaking 
barrier problem occurs.

Using cx_Oracle 4.1 with Python 2.3, Windows XP, Oracle 10g

-- 

Jani Tiainen