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

Re: [cx-oracle-users] ValueError: string data too large

[<yoa...@or...>]

 
Hello, 

Could you drop me of the email list please.

Thanks.

Best Regards, 

Yoann Royer


-----Message d'origine-----
De : Mark Harrison [mailto:mh...@pi...] 
Envoyé : jeudi 8 juillet 2010 00:45
À : cx-...@li...
Objet : Re: [cx-oracle-users] ValueError: string data too large

On 7/7/10 3:39 PM, Anthony Tuininga wrote:
> Hi Mark,
>
> This error can occur if you bind a string shorter than 4000 characters 
> in one iteration and then bind a string larger than 4000 characters in 
> another iteration. Or if you use setinputsizes() to specify a 
> character string and then try to bind more than 4000 characters. The 
> 4000 characters (bytes really) can be different if you use a multibyte 
> character set on the client/server, of course. If you pass a string 
> longer than 4000 characters on the first iteration then cx_Oracle will 
> use a "long" variable -- and Oracle has a number of restrictions on 
> those so I would use CLOB personally.
>
> You can use cursor.var() if you like, or you can simply use
> setinputsizes() to specify that you want to use a CLOB in a particular 
> position and then simply pass the string directly.
>
> HTH,
> Anthony

Thanks Anthony, it does!




>
> On Wed, Jul 7, 2010 at 2:43 PM, Mark Harrison<mh...@pi...>  wrote:
>> A coworker has asked me this...  he's occasionally geting a
>> "ValueError: string data too large" when calling cursor.execute().
>>
>>
>> we're going to try something like this:
>>
>>      clob = cursor.var(cx_Oracle.CLOB)
>>      clob.setvalue(0, contents)
>>
>> and see if that works.  But I'm a bit confused, since the code below 
>> works the majority of the  time.
>>
>> 1.  are we on the right track to change to use clob.setvalue?
>>
>> 2.  should the simple string code work at all?
>>
>> Many TIA!!!
>>
>> Mark
>>
>> ----------------------------
>>
>>
>>      Mark,
>>        This is the "ValueError: string data too large" error message
>>        that I brought up in our last meeting.  It only happens
>>        sometimes, but causes grief when it does.  In this instance
>>        the value for the CLOB column called "contents" in our
>>        foo table is around 130,000 characters.
>>        There are other rows in that table with longer values up to
>>        179507, so clearly the limit (if there is a limit at all) is
>>        not being exceeded.
>>
>>      This one happened a little before 3:29 today.  Could you ask
>>      the DBAs to look into this?
>>
>>
>> 370             contents = simplejson.dumps(newworkspace).encode('ascii')
>> 371             sql = "UPDATE foo SET name=:1, owner=:2, viewers=:3, contents=:4 WHERE workspaceid=:5"
>> 372             values = [name, owner, viewers, contents, workspaceid]
>> 373             self._cursor.execute(sql, values)
>>
>>
>>
>>   >    File "/data/foo.py", line 373, in saveWorkspace
>>   >      self._cursor.execute(sql, values)
>>   >
>>   >  ValueError: string data too large
>>
>> ---------------------------------------------------------------------
>> --------- This SF.net email is sponsored by Sprint What will you do 
>> first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> ----------------------------------------------------------------------
> -------- This SF.net email is sponsored by Sprint What will you do 
> first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users


------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint What will you do first with EVO, the first 4G phone?
Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
cx-oracle-users mailing list
cx-...@li...
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

*********************************
This message and any attachments (the "message") are confidential and intended solely for the addressees. 
Any unauthorised use or dissemination is prohibited.
Messages are susceptible to alteration. 
France Telecom Group shall not be liable for the message if altered, changed or falsified.
If you are not the intended addressee of this message, please cancel it immediately and inform the sender.
********************************

Re: [cx-oracle-users] ValueError: string data too large

[Mark H. <mh...@pi...>]

On 7/7/10 3:39 PM, Anthony Tuininga wrote:
> Hi Mark,
>
> This error can occur if you bind a string shorter than 4000 characters
> in one iteration and then bind a string larger than 4000 characters in
> another iteration. Or if you use setinputsizes() to specify a
> character string and then try to bind more than 4000 characters. The
> 4000 characters (bytes really) can be different if you use a multibyte
> character set on the client/server, of course. If you pass a string
> longer than 4000 characters on the first iteration then cx_Oracle will
> use a "long" variable -- and Oracle has a number of restrictions on
> those so I would use CLOB personally.
>
> You can use cursor.var() if you like, or you can simply use
> setinputsizes() to specify that you want to use a CLOB in a particular
> position and then simply pass the string directly.
>
> HTH,
> Anthony

Thanks Anthony, it does!




>
> On Wed, Jul 7, 2010 at 2:43 PM, Mark Harrison<mh...@pi...>  wrote:
>> A coworker has asked me this...  he's occasionally geting a
>> "ValueError: string data too large" when calling cursor.execute().
>>
>>
>> we're going to try something like this:
>>
>>      clob = cursor.var(cx_Oracle.CLOB)
>>      clob.setvalue(0, contents)
>>
>> and see if that works.  But I'm a bit confused, since the code
>> below works the majority of the  time.
>>
>> 1.  are we on the right track to change to use clob.setvalue?
>>
>> 2.  should the simple string code work at all?
>>
>> Many TIA!!!
>>
>> Mark
>>
>> ----------------------------
>>
>>
>>      Mark,
>>        This is the "ValueError: string data too large" error message
>>        that I brought up in our last meeting.  It only happens
>>        sometimes, but causes grief when it does.  In this instance
>>        the value for the CLOB column called "contents" in our
>>        foo table is around 130,000 characters.
>>        There are other rows in that table with longer values up to
>>        179507, so clearly the limit (if there is a limit at all) is
>>        not being exceeded.
>>
>>      This one happened a little before 3:29 today.  Could you ask
>>      the DBAs to look into this?
>>
>>
>> 370             contents = simplejson.dumps(newworkspace).encode('ascii')
>> 371             sql = "UPDATE foo SET name=:1, owner=:2, viewers=:3, contents=:4 WHERE workspaceid=:5"
>> 372             values = [name, owner, viewers, contents, workspaceid]
>> 373             self._cursor.execute(sql, values)
>>
>>
>>
>>   >    File "/data/foo.py", line 373, in saveWorkspace
>>   >      self._cursor.execute(sql, values)
>>   >
>>   >  ValueError: string data too large
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

Re: [cx-oracle-users] ValueError: string data too large

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

Hi Mark,

This error can occur if you bind a string shorter than 4000 characters
in one iteration and then bind a string larger than 4000 characters in
another iteration. Or if you use setinputsizes() to specify a
character string and then try to bind more than 4000 characters. The
4000 characters (bytes really) can be different if you use a multibyte
character set on the client/server, of course. If you pass a string
longer than 4000 characters on the first iteration then cx_Oracle will
use a "long" variable -- and Oracle has a number of restrictions on
those so I would use CLOB personally.

You can use cursor.var() if you like, or you can simply use
setinputsizes() to specify that you want to use a CLOB in a particular
position and then simply pass the string directly.

HTH,
Anthony

On Wed, Jul 7, 2010 at 2:43 PM, Mark Harrison <mh...@pi...> wrote:
> A coworker has asked me this...  he's occasionally geting a
> "ValueError: string data too large" when calling cursor.execute().
>
>
> we're going to try something like this:
>
>     clob = cursor.var(cx_Oracle.CLOB)
>     clob.setvalue(0, contents)
>
> and see if that works.  But I'm a bit confused, since the code
> below works the majority of the  time.
>
> 1.  are we on the right track to change to use clob.setvalue?
>
> 2.  should the simple string code work at all?
>
> Many TIA!!!
>
> Mark
>
> ----------------------------
>
>
>     Mark,
>       This is the "ValueError: string data too large" error message
>       that I brought up in our last meeting.  It only happens
>       sometimes, but causes grief when it does.  In this instance
>       the value for the CLOB column called "contents" in our
>       foo table is around 130,000 characters.
>       There are other rows in that table with longer values up to
>       179507, so clearly the limit (if there is a limit at all) is
>       not being exceeded.
>
>     This one happened a little before 3:29 today.  Could you ask
>     the DBAs to look into this?
>
>
> 370             contents = simplejson.dumps(newworkspace).encode('ascii')
> 371             sql = "UPDATE foo SET name=:1, owner=:2, viewers=:3, contents=:4 WHERE workspaceid=:5"
> 372             values = [name, owner, viewers, contents, workspaceid]
> 373             self._cursor.execute(sql, values)
>
>
>
>  >  File "/data/foo.py", line 373, in saveWorkspace
>  >    self._cursor.execute(sql, values)
>  >
>  > ValueError: string data too large
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

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

On Wed, Jul 7, 2010 at 1:23 PM, Jason Boorn <jb...@gm...> wrote:
> Geoff -
>
> Those are already part of the SQL statement - they were never in the
> parameters list.
>
> I got this working (thanks Anthony) using a dictionary for the parameters

You're welcome.

> without a call to setinputsizes.  I'm guessing this is not optimal, but I
> could not get the call to setinputsizes to work as a dictionary, and I
> wasn't sure if you could mix named parameters with array-style
> setinputsizes.

I wouldn't suggest it even if technically you could get away with it.
:-) For setinputsizes(), you would need to do the following:

keywordArgs = {}
keywordArgs["value1"] = cx_Oracle.NUMBER
keywordArgs["value2"] = cx_Oracle.CLOB
cursor.setinputsizes(**keywordArgs)

OR

cursor.setinputsizes(value1 = cx_Oracle.NUMBER, value2 = cx_Oracle.CLOB)

> I'm guessing that not using the setinputsizes function is not optimal, but I
> can get it to work without it so that's what I'm going to do.

BTW, I use setinputsizes() as rarely as possible. The only time its
worth using is when you plan to execute a statement more than one time
in a row and the format of the data changes between executions -- or a
value is null (None) in one iteration but has a value in subsequent
iterations. In all other situations its probably faster to avoid the
setinputsizes() call.

Anthony

[cx-oracle-users] ValueError: string data too large

[Mark H. <mh...@pi...>]

A coworker has asked me this...  he's occasionally geting a
"ValueError: string data too large" when calling cursor.execute().


we're going to try something like this:

     clob = cursor.var(cx_Oracle.CLOB)
     clob.setvalue(0, contents)

and see if that works.  But I'm a bit confused, since the code
below works the majority of the  time.

1.  are we on the right track to change to use clob.setvalue?

2.  should the simple string code work at all?

Many TIA!!!

Mark

----------------------------


     Mark,
       This is the "ValueError: string data too large" error message
       that I brought up in our last meeting.  It only happens
       sometimes, but causes grief when it does.  In this instance
       the value for the CLOB column called "contents" in our
       foo table is around 130,000 characters.
       There are other rows in that table with longer values up to
       179507, so clearly the limit (if there is a limit at all) is
       not being exceeded.

     This one happened a little before 3:29 today.  Could you ask
     the DBAs to look into this?


370             contents = simplejson.dumps(newworkspace).encode('ascii')
371             sql = "UPDATE foo SET name=:1, owner=:2, viewers=:3, contents=:4 WHERE workspaceid=:5"
372             values = [name, owner, viewers, contents, workspaceid]
373             self._cursor.execute(sql, values)



 >  File "/data/foo.py", line 373, in saveWorkspace
 >    self._cursor.execute(sql, values)
 >
 > ValueError: string data too large

Re: [cx-oracle-users] installation proplems

[Mark H. <mh...@pi...>]

On 7/7/10 6:44 AM, Christian Klinger wrote:
> Hi,
>
> i try to install cx_oracle on my new mac-book-pro.

what version of OSX?

>
> I have installed instantclient_10_2/   basic + sdk

I was able to get everything built quite easily if I
installed the instantclient into /usr/{include,lib,bin}

I documented the steps here:

http://stackoverflow.com/questions/684352/installing-oracle-instantclient-on-mac-os-x-without-setting-environment-variables

HTH!

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

Geoff -

Those are already part of the SQL statement - they were never in the
parameters list.

I got this working (thanks Anthony) using a dictionary for the parameters
without a call to setinputsizes.  I'm guessing this is not optimal, but I
could not get the call to setinputsizes to work as a dictionary, and I
wasn't sure if you could mix named parameters with array-style
setinputsizes.

I'm guessing that not using the setinputsizes function is not optimal, but I
can get it to work without it so that's what I'm going to do.

Thanks

On Wed, Jul 7, 2010 at 8:23 AM, Weber, Geoffrey
<Geo...@pa...>wrote:

> OK - your problem here is that you can't put "to_date()" or
> "to_timestamp()" functions in as part of the parameter list.  What you need
> instead is to put those functions as part of the SQL statement itself:
>
> sql = 'INSERT INTO import_camtarget
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES (:1, :2, :3, :4, :5... ,TO_DATE(:n, 'YYYY-MM-DD HH24:MI:SS'), :(n+1),
> ...)'
>
> and pass in your date strings just like any other bind value.  Any value
> you pass in as parameters is interpreted literally and not as a function
> call by Oracle.
>
> so then you'd have:
> cursor.execute(sql, params)
>
> ...just like Anthony has been describing to you before (either tuple/list
> or dictionary forms).
>
> Hope this makes sense to you, and good luck!
>
>    - Geoff
>
>
> ________________________________________
> From: Jason Boorn [jb...@gm...]
> Sent: Tuesday, July 06, 2010 4:43 PM
> To: cx-...@li...
> Subject: Re: [cx-oracle-users] setinputsizes problem?
>
> I just noticed something:
>
> > The final SQL string that gets built is:
> >
> > INSERT INTO import_CAMTARGET
> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> > VALUES
> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> 13:37:17','YYYY-MM-DD
> > HH24:MI:SS'),'','APIMaxifier',null,'N')
>
> I have time values which include colons in my SQL string.  Is it possible
> these are confusing the parser?  If so, is there a way to escape them?
>
> On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga <
> ant...@gm...<mailto:ant...@gm...>> wrote:
> No, do not quote the position parameter. Something like this is want you
> want:
>
> cursor.execute("insert into test values (:1, null, null)", ("This is
> the input string",))
>
> BTW, the test scripts can be a source of examples for how things can be
> written.
>
> Anthony
>
> On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...<mailto:
> jb...@gm...>> wrote:
> > Do I need to quote the position parameter?
> >
> > like:
> >
> > INSERT INTO TEST  VALUES (':1', null null)
> >
> > as opposed to:
> >
> > INSERT INTO TEST  VALUES (:1, null null)
> >
> > On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
> > <ant...@gm...<mailto:ant...@gm...>> wrote:
>  >>
> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...<mailto:
> jb...@gm...>> wrote:
> >> > Ya, I already tried it without the * - same error.
> >>
> >> Hmm, interesting. The problem with the illegal variable name/number
> >> problem is that its almost impossible to diagnose the problem. You
> >> have to carefully examine the SQL looking for any colons that may have
> >> strayed outside of quotes or where an extra character is found after
> >> the colon without a space as you were intending, etc. Eventually you
> >> will find it but its always irritating having to search for it -- it
> >> would be nice if Oracle went to the trouble of telling you __what__
> >> variable name/number was illegal!
> >>
> >> To clarify again, the method signature is
> >>
> >> cursor.execute(sql, parameters)
> >>
> >> Where parameters is either a dictionary (named parameters) or sequence
> >> (positional parameters). So if you have a tuple
> >>
> >> clobs = ('This is the clob value',)
> >>
> >> or sequence
> >>
> >> clobs = ['This is the clob value']
> >>
> >> You would call
> >>
> >> cursor.execute(sql, clobs)
> >>
> >> Likewise, if you have keywords you would do the following:
> >>
> >> clobs = { "value1" : "This is the clob value" }
> >> cursor.execute(sql, clobs)
> >>
> >> I have added the ability to do the following as well since it is often
> >> convenient
> >>
> >> cursor.execute(sql, value1 = "This is the clob value")
> >>
> >> The first form is useful if you are building up the parameters
> >> dynamically and the second for known queries with known numbers of
> >> parameters.
> >>
> >> > The final SQL string that gets built is:
> >> >
> >> > INSERT INTO import_CAMTARGET
> >> >
> >> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> >> > VALUES
> >> >
> >> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> >> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> >> > 13:37:17','YYYY-MM-DD
> >> > HH24:MI:SS'),'matthew.kay_guardian.co.uk<
> http://matthew.kay_guardian.co.uk/>','APIMaxifier',null,'N')
>  >> >
> >> > My clobs array contains one string element (note the string contains
> >> > newline
> >> > characters could this be an issue?):
> >>
> >> No, the newlines are not a problem. Using bind variables means that
> >> you don't have to worry about special characters of any sort. The data
> >> is passed directly to Oracle without interpretation.
> >>
> >> >  'GB:NEW MILTON
> >> > GB:WALTHAM FOREST
> >> > GB:CAMDEN
> >> > GB:CHELMSFORD
> >> > GB:SOMPTING
> >> > GB:LEATHERHEAD
> >> > GB:WINSFORD
> >> > GB:GAINSBOROUGH
> >> > GB:DINGWALL
> >> > GB:WEST KILBRIDE
> >> > GB:CHESTERFIELD
> >> > GB:BLETCHLEY
> >> > GB:HALESWORTH
> >> > GB:GUILDFORD
> >> > GB:RAGLAN
> >> > GB:HOLLINGWORTH
> >> > GB:ROSLIN
> >> > GB:WOOTTON
> >> > GB:NORMANDY
> >> > GB:EDENBRIDGE
> >> > GB:HEATH
> >> > GB:NEWTOWNABBEY
> >> > GB:TAMWORTH
> >> > GB:TOWNHILL
> >> > GB:FAILSWORTH
> >> > GB:KETTERING
> >> > GB:PELTON
> >> > GB:RYDE
> >> > GB:LYNDHURST
> >> > GB:NORTH BERWICK
> >> > GB:BIRKENHEAD
> >> > GB:LOCHEARNHEAD
> >> > GB:DARVEL
> >> > GB:HOLMES CHAPEL
> >> > GB:STORRINGTON
> >> > GB:SPRINGFIELD
> >> > GB:CARLUKE
> >> > GB:BURY ST EDMUNDS
> >> > GB:DORKING
> >> > GB:CLOGHER
> >> > GB:CROMER
> >> > GB:LANCING
> >> > GB:LONGFIELD
> >> > GB:BROADFORD
> >> > GB:ASHTON-UNDER-LYNE
> >> > GB:CROWLAS
> >> > GB:HOLYWELL
> >> > GB:LOOE
> >> > GB:STAPLEFORD
> >> > GB:PAISLEY
> >> > GB:PORTRUSH
> >> > GB:PLYMPTON
> >> > GB:BRACKLEY
> >> > GB:FARNHAM
> >> > GB:GOLBORNE
> >> > GB:HASLINGDEN
> >> > GB:GRANTOWN-ON-SPEY
> >> > GB:GERRARDS CROSS
> >> > GB:MARKET HARBOROUGH
> >> > GB:CLYDACH
> >> > GB:LYDIATE
> >> > GB:DEVIZES
> >> > GB:BIRTLEY
> >> > GB:STRATHPEFFER
> >> > GB:SHOREHAM
> >> > GB:HANLEY
> >> > GB:WALSINGHAM
> >> > GB:WOODLEY
> >> > GB:BLAIRGOWRIE AND RATTRAY
> >> > GB:BARNET
> >> > GB:ASHLEY
> >> > GB:NOTTINGHAM
> >> > GB:MAUCHLINE
> >> > GB:PINXTON
> >> > GB:WOKINGHAM'
> >> >
> >> > As far as using named parameters (which I'm happy to do to get this
> >> > working): I understand how to establish the named parameters, but not
> >> > how to
> >> > get them into the structure that is passed to execute.  As I
> understand
> >> > it,
> >> > we need to pass in:
> >> >
> >> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
> >> > value2:'sadfasdfasdfasdf')
> >>
> >> See above. Either do this:
> >>
> >> parameters = { "value1" : "asdfadfadfadsfa", "value2" :
> "asfadsfadsfadsf"
> >> }
> >> cursor.execute(sql, parameters)
> >>
> >> or do this
> >>
> >> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
> >>
> >> > I can generate the string "value1" and I can generate the string
> >> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me
> -
> >> > I'm
> >> > not passing a string, I'm passing a variable/string tuple correct?
>  How
> >> > do I
> >> > dynamically generate that?
> >>
> >> You can do this to build the parameters up dynamically.
> >> parameters = {}
> >> parameters["value1"] = "adfadsfadf"
> >> parameters["value2"] = "asdfsadfafasdfdsaf"
> >> cursor.execute(sql, parameters)
> >>
> >> > Apologies is this is a very naive question.
> >>
> >> No problem. :-)
> >>
> >> Anthony
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> This SF.net email is sponsored by Sprint
> >> What will you do first with EVO, the first 4G phone?
> >> Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...<mailto:
> cx-...@li...>
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> ------------------------------------------------------------------------------
> > This SF.net email is sponsored by Sprint
> > What will you do first with EVO, the first 4G phone?
> > Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...<mailto:
> cx-...@li...>
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first<http://sprint.com/first> --
> http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...<mailto:
> cx-...@li...>
>  https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

[cx-oracle-users] installation proplems

[Christian K. <ckl...@no...>]

Hi,

i try to install cx_oracle on my new mac-book-pro.

I have installed instantclient_10_2/   basic + sdk

If i start easy_install cx_Oracle i get this error:

yeti-ii:cx_Oracle-5.0.3 christian$ ../bin/python setup.py build
running build
running build_ext
building 'cx_Oracle' extension
creating build
creating build/temp.macosx-10.4-x86_64-2.6-10g
gcc -fno-strict-aliasing -DNDEBUG -g -fwrapv -O3 -Wall 
-Wstrict-prototypes 
-I/Users/christian/oracle/instantclient_10_2/sdk/include 
-I/Users/christian/local/include/python2.6 -c cx_Oracle.c -o 
build/temp.macosx-10.4-x86_64-2.6-10g/cx_Oracle.o -DBUILD_VERSION=5.0.3
In file included from 
/Users/christian/oracle/instantclient_10_2/sdk/include/oci.h:2681,
                  from cx_Oracle.c:10:
/Users/christian/oracle/instantclient_10_2/sdk/include/oci1.h:148: 
warning: function declaration isn’t a prototype
In file included from 
/Users/christian/oracle/instantclient_10_2/sdk/include/ociap.h:230,
                  from 
/Users/christian/oracle/instantclient_10_2/sdk/include/oci.h:2705,
                  from cx_Oracle.c:10:
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:676: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2667: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2676: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2686: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2695: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2704: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2713: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2721: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2731: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2738: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/nzt.h:2746: 
warning: function declaration isn’t a prototype
In file included from 
/Users/christian/oracle/instantclient_10_2/sdk/include/oci.h:2705,
                  from cx_Oracle.c:10:
/Users/christian/oracle/instantclient_10_2/sdk/include/ociap.h:10077: 
warning: function declaration isn’t a prototype
/Users/christian/oracle/instantclient_10_2/sdk/include/ociap.h:10083: 
warning: function declaration isn’t a prototype
creating build/lib.macosx-10.4-x86_64-2.6-10g
gcc -bundle -undefined dynamic_lookup 
build/temp.macosx-10.4-x86_64-2.6-10g/cx_Oracle.o 
-L/Users/christian/oracle/instantclient_10_2/lib 
-L/Users/christian/oracle/instantclient_10_2/ -lclntsh -o 
build/lib.macosx-10.4-x86_64-2.6-10g/cx_Oracle.so -shared-libgcc
ld: warning: directory '/Users/christian/oracle/instantclient_10_2/lib' 
following -L not found
ld: library not found for -lclntsh
collect2: ld returned 1 exit status
error: command 'gcc' failed with exit status 1

ideas?
thanks christian

Re: [cx-oracle-users] Problems with UTF8 and unicode version of cx_Oracle

[Amaury F. d'A. <ama...@gm...>]

Hi,

2010/7/7 Stefan Dietrich <ste...@de...>:
> Hello,
>
> I´m using the cx_Oracle module for importing data from another database
> (ZODB), but I have problems with the character encoding.
> The data in ZODB is entered through a web form, so the character
> encoding in the ZODB is UTF-8.
>
> Everything is fine until special characters appear or characters from
> foreign languages, e.g. a dash, cyrillic characters or the German letter
> "ß". However, German umlauts (üäö) are not affected.
> If I use "print" to display them, everything is fine.
>
> But if I try to insert them into the Oracle DB, they appear as an
> inverted question mark in the Oracle SQL Developer or SQLPlus.
> If I query my Oracle DB again with cx_Oracle, they appear as inverted
> questionmarks.
>
> Example (trying to insert data with a dash):
>
>     title = conference.getTitle().decode('utf-8')
>     print title
> 2nd Workshop: GISAXS – an advanced scattering method
>     title
> u'2nd Workshop: GISAXS \u2013 an advanced scattering method'
>     cursor.execute(u"INSERT INTO conf (CID, title) VALUES (4, '"
> +title+ "')")
>     conn.commit()
>     cursor.execute(u"SELECT * FROM conf WHERE CID = 4")
>     fetch = cursor.fetchall()
>     fetch[0][1]
> u'2nd Workshop: GISAXS \xbf an advanced scattering method'
>     print fetch[0][1]
> 2nd Workshop: GISAXS ¿ an advanced scattering method
>
>
> If I copy&paste the output of print with the dash and insert the data
> with Oracle SQLDeveloper, everthing is correct. Even cx_Oracle returns
> the dash after a select.
>
> I´m using cx_Oracle 5.0.3 Unicode Version with Python 2.4.3 on
> Scientific Linux 5.3. The field is NVARCHAR2 (happens also with
> VARCHAR), NLS_LANG is set to GERMAN_GERMANY.UTF8 and
> NLS_NCHAR_CHARACTERSET is UTF8.
>
> I´m out of ideas, so I would appreciate any help and thanks in advance!

Even if the column is defined as NVARCHAR2, you pass it in the SQL
statement as a string literal.

Oracle always considers SQL statements as strings (= VARCHAR),
so the special character will be lost if it cannot be encoded in the
NLS_CHAR_CHARACTERSET.

You should not pass the values as string literals in the query, but
use bind variables instead:
    cursor.execute("INSERT INTO conf (CID, title) VALUES (4, :1)", [title])
Now the query only contains plain ascii characters; the unicode value is passed
through a bind variable which will respect all characters.

-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] Problems with UTF8 and unicode version of cx_Oracle

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

Hi,

First of all, you should be able to use the standard version of
cx_Oracle to bind data for insert as unicode strings. I do that myself
all of the time. If the field is nvarchar2 or nclob you will also get
the data returned as unicode strings. Its only normal strings
(varchar2 and char) that are returned as encoded strings and not
unicode strings. I would suggest you try this first to see if there is
some problem with the unicode mode version of cx_Oracle. In the past
year or so I have discovered a number of problems that suggest that
mode is not particularly well supported by Oracle. I am considering
removing it and simply enabling better unicode support in the normal
version -- just got to find the time to test that and see if it will
work reasonably well.

Beyond that, you need to check the database encodings as Oracle will
feel free to substitute characters if the database encoding does not
support what is coming in.

Anthony

On Wed, Jul 7, 2010 at 6:01 AM, Stefan Dietrich <ste...@de...> wrote:
> Hello,
>
> I´m using the cx_Oracle module for importing data from another database
> (ZODB), but I have problems with the character encoding.
> The data in ZODB is entered through a web form, so the character
> encoding in the ZODB is UTF-8.
>
> Everything is fine until special characters appear or characters from
> foreign languages, e.g. a dash, cyrillic characters or the German letter
> "ß". However, German umlauts (üäö) are not affected.
> If I use "print" to display them, everything is fine.
>
> But if I try to insert them into the Oracle DB, they appear as an
> inverted question mark in the Oracle SQL Developer or SQLPlus.
> If I query my Oracle DB again with cx_Oracle, they appear as inverted
> questionmarks.
>
> Example (trying to insert data with a dash):
>
>     title = conference.getTitle().decode('utf-8')
>     print title
> 2nd Workshop: GISAXS – an advanced scattering method
>     title
> u'2nd Workshop: GISAXS \u2013 an advanced scattering method'
>     cursor.execute(u"INSERT INTO conf (CID, title) VALUES (4, '"
> +title+ "')")
>     conn.commit()
>     cursor.execute(u"SELECT * FROM conf WHERE CID = 4")
>     fetch = cursor.fetchall()
>     fetch[0][1]
> u'2nd Workshop: GISAXS \xbf an advanced scattering method'
>     print fetch[0][1]
> 2nd Workshop: GISAXS ¿ an advanced scattering method
>
>
> If I copy&paste the output of print with the dash and insert the data
> with Oracle SQLDeveloper, everthing is correct. Even cx_Oracle returns
> the dash after a select.
>
> I´m using cx_Oracle 5.0.3 Unicode Version with Python 2.4.3 on
> Scientific Linux 5.3. The field is NVARCHAR2 (happens also with
> VARCHAR), NLS_LANG is set to GERMAN_GERMANY.UTF8 and
> NLS_NCHAR_CHARACTERSET is UTF8.
>
> I´m out of ideas, so I would appreciate any help and thanks in advance!
>
> Regards,
> Stefan
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

[Weber, G. <Geo...@PA...>]

OK - your problem here is that you can't put "to_date()" or "to_timestamp()" functions in as part of the parameter list.  What you need instead is to put those functions as part of the SQL statement itself:

sql = 'INSERT INTO import_camtarget (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE) VALUES (:1, :2, :3, :4, :5... ,TO_DATE(:n, 'YYYY-MM-DD HH24:MI:SS'), :(n+1), ...)'

and pass in your date strings just like any other bind value.  Any value you pass in as parameters is interpreted literally and not as a function call by Oracle.

so then you'd have:
cursor.execute(sql, params)

...just like Anthony has been describing to you before (either tuple/list or dictionary forms).

Hope this makes sense to you, and good luck!

    - Geoff


________________________________________
From: Jason Boorn [jb...@gm...]
Sent: Tuesday, July 06, 2010 4:43 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] setinputsizes problem?

I just noticed something:

> The final SQL string that gets built is:
>
> INSERT INTO import_CAMTARGET
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
> HH24:MI:SS'),'','APIMaxifier',null,'N')

I have time values which include colons in my SQL string.  Is it possible these are confusing the parser?  If so, is there a way to escape them?

On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga <ant...@gm...<mailto:ant...@gm...>> wrote:
No, do not quote the position parameter. Something like this is want you want:

cursor.execute("insert into test values (:1, null, null)", ("This is
the input string",))

BTW, the test scripts can be a source of examples for how things can be written.

Anthony

On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...<mailto:jb...@gm...>> wrote:
> Do I need to quote the position parameter?
>
> like:
>
> INSERT INTO TEST  VALUES (':1', null null)
>
> as opposed to:
>
> INSERT INTO TEST  VALUES (:1, null null)
>
> On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
> <ant...@gm...<mailto:ant...@gm...>> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...<mailto:jb...@gm...>> wrote:
>> > Ya, I already tried it without the * - same error.
>>
>> Hmm, interesting. The problem with the illegal variable name/number
>> problem is that its almost impossible to diagnose the problem. You
>> have to carefully examine the SQL looking for any colons that may have
>> strayed outside of quotes or where an extra character is found after
>> the colon without a space as you were intending, etc. Eventually you
>> will find it but its always irritating having to search for it -- it
>> would be nice if Oracle went to the trouble of telling you __what__
>> variable name/number was illegal!
>>
>> To clarify again, the method signature is
>>
>> cursor.execute(sql, parameters)
>>
>> Where parameters is either a dictionary (named parameters) or sequence
>> (positional parameters). So if you have a tuple
>>
>> clobs = ('This is the clob value',)
>>
>> or sequence
>>
>> clobs = ['This is the clob value']
>>
>> You would call
>>
>> cursor.execute(sql, clobs)
>>
>> Likewise, if you have keywords you would do the following:
>>
>> clobs = { "value1" : "This is the clob value" }
>> cursor.execute(sql, clobs)
>>
>> I have added the ability to do the following as well since it is often
>> convenient
>>
>> cursor.execute(sql, value1 = "This is the clob value")
>>
>> The first form is useful if you are building up the parameters
>> dynamically and the second for known queries with known numbers of
>> parameters.
>>
>> > The final SQL string that gets built is:
>> >
>> > INSERT INTO import_CAMTARGET
>> >
>> > (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
>> > VALUES
>> >
>> > (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
>> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
>> > 13:37:17','YYYY-MM-DD
>> > HH24:MI:SS'),'matthew.kay_guardian.co.uk<http://matthew.kay_guardian.co.uk/>','APIMaxifier',null,'N')
>> >
>> > My clobs array contains one string element (note the string contains
>> > newline
>> > characters could this be an issue?):
>>
>> No, the newlines are not a problem. Using bind variables means that
>> you don't have to worry about special characters of any sort. The data
>> is passed directly to Oracle without interpretation.
>>
>> >  'GB:NEW MILTON
>> > GB:WALTHAM FOREST
>> > GB:CAMDEN
>> > GB:CHELMSFORD
>> > GB:SOMPTING
>> > GB:LEATHERHEAD
>> > GB:WINSFORD
>> > GB:GAINSBOROUGH
>> > GB:DINGWALL
>> > GB:WEST KILBRIDE
>> > GB:CHESTERFIELD
>> > GB:BLETCHLEY
>> > GB:HALESWORTH
>> > GB:GUILDFORD
>> > GB:RAGLAN
>> > GB:HOLLINGWORTH
>> > GB:ROSLIN
>> > GB:WOOTTON
>> > GB:NORMANDY
>> > GB:EDENBRIDGE
>> > GB:HEATH
>> > GB:NEWTOWNABBEY
>> > GB:TAMWORTH
>> > GB:TOWNHILL
>> > GB:FAILSWORTH
>> > GB:KETTERING
>> > GB:PELTON
>> > GB:RYDE
>> > GB:LYNDHURST
>> > GB:NORTH BERWICK
>> > GB:BIRKENHEAD
>> > GB:LOCHEARNHEAD
>> > GB:DARVEL
>> > GB:HOLMES CHAPEL
>> > GB:STORRINGTON
>> > GB:SPRINGFIELD
>> > GB:CARLUKE
>> > GB:BURY ST EDMUNDS
>> > GB:DORKING
>> > GB:CLOGHER
>> > GB:CROMER
>> > GB:LANCING
>> > GB:LONGFIELD
>> > GB:BROADFORD
>> > GB:ASHTON-UNDER-LYNE
>> > GB:CROWLAS
>> > GB:HOLYWELL
>> > GB:LOOE
>> > GB:STAPLEFORD
>> > GB:PAISLEY
>> > GB:PORTRUSH
>> > GB:PLYMPTON
>> > GB:BRACKLEY
>> > GB:FARNHAM
>> > GB:GOLBORNE
>> > GB:HASLINGDEN
>> > GB:GRANTOWN-ON-SPEY
>> > GB:GERRARDS CROSS
>> > GB:MARKET HARBOROUGH
>> > GB:CLYDACH
>> > GB:LYDIATE
>> > GB:DEVIZES
>> > GB:BIRTLEY
>> > GB:STRATHPEFFER
>> > GB:SHOREHAM
>> > GB:HANLEY
>> > GB:WALSINGHAM
>> > GB:WOODLEY
>> > GB:BLAIRGOWRIE AND RATTRAY
>> > GB:BARNET
>> > GB:ASHLEY
>> > GB:NOTTINGHAM
>> > GB:MAUCHLINE
>> > GB:PINXTON
>> > GB:WOKINGHAM'
>> >
>> > As far as using named parameters (which I'm happy to do to get this
>> > working): I understand how to establish the named parameters, but not
>> > how to
>> > get them into the structure that is passed to execute.  As I understand
>> > it,
>> > we need to pass in:
>> >
>> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
>> > value2:'sadfasdfasdfasdf')
>>
>> See above. Either do this:
>>
>> parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf"
>> }
>> cursor.execute(sql, parameters)
>>
>> or do this
>>
>> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
>>
>> > I can generate the string "value1" and I can generate the string
>> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me -
>> > I'm
>> > not passing a string, I'm passing a variable/string tuple correct?  How
>> > do I
>> > dynamically generate that?
>>
>> You can do this to build the parameters up dynamically.
>> parameters = {}
>> parameters["value1"] = "adfadsfadf"
>> parameters["value2"] = "asdfsadfafasdfdsaf"
>> cursor.execute(sql, parameters)
>>
>> > Apologies is this is a very naive question.
>>
>> No problem. :-)
>>
>> Anthony
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first<http://sprint.com/first> -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...<mailto:cx-...@li...>
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first<http://sprint.com/first> -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...<mailto:cx-...@li...>
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

------------------------------------------------------------------------------
This SF.net email is sponsored by Sprint
What will you do first with EVO, the first 4G phone?
Visit sprint.com/first<http://sprint.com/first> -- http://p.sf.net/sfu/sprint-com-first
_______________________________________________
cx-oracle-users mailing list
cx-...@li...<mailto:cx-...@li...>
https://lists.sourceforge.net/lists/listinfo/cx-oracle-users

[cx-oracle-users] Problems with UTF8 and unicode version of cx_Oracle

[Stefan D. <ste...@de...>]

Hello,

I´m using the cx_Oracle module for importing data from another database 
(ZODB), but I have problems with the character encoding.
The data in ZODB is entered through a web form, so the character 
encoding in the ZODB is UTF-8.

Everything is fine until special characters appear or characters from 
foreign languages, e.g. a dash, cyrillic characters or the German letter 
"ß". However, German umlauts (üäö) are not affected.
If I use "print" to display them, everything is fine.

But if I try to insert them into the Oracle DB, they appear as an 
inverted question mark in the Oracle SQL Developer or SQLPlus.
If I query my Oracle DB again with cx_Oracle, they appear as inverted 
questionmarks.

Example (trying to insert data with a dash):

     title = conference.getTitle().decode('utf-8')
     print title
2nd Workshop: GISAXS – an advanced scattering method
     title
u'2nd Workshop: GISAXS \u2013 an advanced scattering method'
     cursor.execute(u"INSERT INTO conf (CID, title) VALUES (4, '" 
+title+ "')")
     conn.commit()
     cursor.execute(u"SELECT * FROM conf WHERE CID = 4")
     fetch = cursor.fetchall()
     fetch[0][1]
u'2nd Workshop: GISAXS \xbf an advanced scattering method'
     print fetch[0][1]
2nd Workshop: GISAXS ¿ an advanced scattering method


If I copy&paste the output of print with the dash and insert the data 
with Oracle SQLDeveloper, everthing is correct. Even cx_Oracle returns 
the dash after a select.

I´m using cx_Oracle 5.0.3 Unicode Version with Python 2.4.3 on 
Scientific Linux 5.3. The field is NVARCHAR2 (happens also with 
VARCHAR), NLS_LANG is set to GERMAN_GERMANY.UTF8 and 
NLS_NCHAR_CHARACTERSET is UTF8.

I´m out of ideas, so I would appreciate any help and thanks in advance!

Regards,
Stefan

Re: [cx-oracle-users] setinputsizes problem?

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

Not likely or I'm sure there would be a lot of screams coming from
Oracle using people! This script does much the same thing using the
test data used in the test suite for cx_Oracle.

cursor.execute("""
        select * from TestDates
        where DateCol = to_date('2002-12-10 02:24:00', 'YYYY-MM-DD HH24:MI:SS')
          and IntCol between :1 and :2""",
        (1, 5))
for row in cursor:
    print row

Of course, if you want to remove the colons, go ahead. The to_date()
method doesn't require them but you are including them in the format
you are using. They are not required -- its just much easier to read.

Anthony

On Tue, Jul 6, 2010 at 3:43 PM, Jason Boorn <jb...@gm...> wrote:
> I just noticed something:
>
>> The final SQL string that gets built is:
>>
>> INSERT INTO import_CAMTARGET
>>
>> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
>> VALUES
>>
>> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
>> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
>> 13:37:17','YYYY-MM-DD
>> HH24:MI:SS'),'','APIMaxifier',null,'N')
>
> I have time values which include colons in my SQL string.  Is it possible
> these are confusing the parser?  If so, is there a way to escape them?
>
> On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga
> <ant...@gm...> wrote:
>>
>> No, do not quote the position parameter. Something like this is want you
>> want:
>>
>> cursor.execute("insert into test values (:1, null, null)", ("This is
>> the input string",))
>>
>> BTW, the test scripts can be a source of examples for how things can be
>> written.
>>
>> Anthony
>>
>> On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...> wrote:
>> > Do I need to quote the position parameter?
>> >
>> > like:
>> >
>> > INSERT INTO TEST  VALUES (':1', null null)
>> >
>> > as opposed to:
>> >
>> > INSERT INTO TEST  VALUES (:1, null null)
>> >
>> > On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
>> > <ant...@gm...> wrote:
>> >>
>> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
>> >> > Ya, I already tried it without the * - same error.
>> >>
>> >> Hmm, interesting. The problem with the illegal variable name/number
>> >> problem is that its almost impossible to diagnose the problem. You
>> >> have to carefully examine the SQL looking for any colons that may have
>> >> strayed outside of quotes or where an extra character is found after
>> >> the colon without a space as you were intending, etc. Eventually you
>> >> will find it but its always irritating having to search for it -- it
>> >> would be nice if Oracle went to the trouble of telling you __what__
>> >> variable name/number was illegal!
>> >>
>> >> To clarify again, the method signature is
>> >>
>> >> cursor.execute(sql, parameters)
>> >>
>> >> Where parameters is either a dictionary (named parameters) or sequence
>> >> (positional parameters). So if you have a tuple
>> >>
>> >> clobs = ('This is the clob value',)
>> >>
>> >> or sequence
>> >>
>> >> clobs = ['This is the clob value']
>> >>
>> >> You would call
>> >>
>> >> cursor.execute(sql, clobs)
>> >>
>> >> Likewise, if you have keywords you would do the following:
>> >>
>> >> clobs = { "value1" : "This is the clob value" }
>> >> cursor.execute(sql, clobs)
>> >>
>> >> I have added the ability to do the following as well since it is often
>> >> convenient
>> >>
>> >> cursor.execute(sql, value1 = "This is the clob value")
>> >>
>> >> The first form is useful if you are building up the parameters
>> >> dynamically and the second for known queries with known numbers of
>> >> parameters.
>> >>
>> >> > The final SQL string that gets built is:
>> >> >
>> >> > INSERT INTO import_CAMTARGET
>> >> >
>> >> >
>> >> > (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
>> >> > VALUES
>> >> >
>> >> >
>> >> > (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
>> >> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
>> >> > 13:37:17','YYYY-MM-DD
>> >> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
>> >> >
>> >> > My clobs array contains one string element (note the string contains
>> >> > newline
>> >> > characters could this be an issue?):
>> >>
>> >> No, the newlines are not a problem. Using bind variables means that
>> >> you don't have to worry about special characters of any sort. The data
>> >> is passed directly to Oracle without interpretation.
>> >>
>> >> >  'GB:NEW MILTON
>> >> > GB:WALTHAM FOREST
>> >> > GB:CAMDEN
>> >> > GB:CHELMSFORD
>> >> > GB:SOMPTING
>> >> > GB:LEATHERHEAD
>> >> > GB:WINSFORD
>> >> > GB:GAINSBOROUGH
>> >> > GB:DINGWALL
>> >> > GB:WEST KILBRIDE
>> >> > GB:CHESTERFIELD
>> >> > GB:BLETCHLEY
>> >> > GB:HALESWORTH
>> >> > GB:GUILDFORD
>> >> > GB:RAGLAN
>> >> > GB:HOLLINGWORTH
>> >> > GB:ROSLIN
>> >> > GB:WOOTTON
>> >> > GB:NORMANDY
>> >> > GB:EDENBRIDGE
>> >> > GB:HEATH
>> >> > GB:NEWTOWNABBEY
>> >> > GB:TAMWORTH
>> >> > GB:TOWNHILL
>> >> > GB:FAILSWORTH
>> >> > GB:KETTERING
>> >> > GB:PELTON
>> >> > GB:RYDE
>> >> > GB:LYNDHURST
>> >> > GB:NORTH BERWICK
>> >> > GB:BIRKENHEAD
>> >> > GB:LOCHEARNHEAD
>> >> > GB:DARVEL
>> >> > GB:HOLMES CHAPEL
>> >> > GB:STORRINGTON
>> >> > GB:SPRINGFIELD
>> >> > GB:CARLUKE
>> >> > GB:BURY ST EDMUNDS
>> >> > GB:DORKING
>> >> > GB:CLOGHER
>> >> > GB:CROMER
>> >> > GB:LANCING
>> >> > GB:LONGFIELD
>> >> > GB:BROADFORD
>> >> > GB:ASHTON-UNDER-LYNE
>> >> > GB:CROWLAS
>> >> > GB:HOLYWELL
>> >> > GB:LOOE
>> >> > GB:STAPLEFORD
>> >> > GB:PAISLEY
>> >> > GB:PORTRUSH
>> >> > GB:PLYMPTON
>> >> > GB:BRACKLEY
>> >> > GB:FARNHAM
>> >> > GB:GOLBORNE
>> >> > GB:HASLINGDEN
>> >> > GB:GRANTOWN-ON-SPEY
>> >> > GB:GERRARDS CROSS
>> >> > GB:MARKET HARBOROUGH
>> >> > GB:CLYDACH
>> >> > GB:LYDIATE
>> >> > GB:DEVIZES
>> >> > GB:BIRTLEY
>> >> > GB:STRATHPEFFER
>> >> > GB:SHOREHAM
>> >> > GB:HANLEY
>> >> > GB:WALSINGHAM
>> >> > GB:WOODLEY
>> >> > GB:BLAIRGOWRIE AND RATTRAY
>> >> > GB:BARNET
>> >> > GB:ASHLEY
>> >> > GB:NOTTINGHAM
>> >> > GB:MAUCHLINE
>> >> > GB:PINXTON
>> >> > GB:WOKINGHAM'
>> >> >
>> >> > As far as using named parameters (which I'm happy to do to get this
>> >> > working): I understand how to establish the named parameters, but not
>> >> > how to
>> >> > get them into the structure that is passed to execute.  As I
>> >> > understand
>> >> > it,
>> >> > we need to pass in:
>> >> >
>> >> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
>> >> > value2:'sadfasdfasdfasdf')
>> >>
>> >> See above. Either do this:
>> >>
>> >> parameters = { "value1" : "asdfadfadfadsfa", "value2" :
>> >> "asfadsfadsfadsf"
>> >> }
>> >> cursor.execute(sql, parameters)
>> >>
>> >> or do this
>> >>
>> >> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
>> >>
>> >> > I can generate the string "value1" and I can generate the string
>> >> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me
>> >> > -
>> >> > I'm
>> >> > not passing a string, I'm passing a variable/string tuple correct?
>> >> > How
>> >> > do I
>> >> > dynamically generate that?
>> >>
>> >> You can do this to build the parameters up dynamically.
>> >> parameters = {}
>> >> parameters["value1"] = "adfadsfadf"
>> >> parameters["value2"] = "asdfsadfafasdfdsaf"
>> >> cursor.execute(sql, parameters)
>> >>
>> >> > Apologies is this is a very naive question.
>> >>
>> >> No problem. :-)
>> >>
>> >> Anthony
>> >>
>> >>
>> >>
>> >> ------------------------------------------------------------------------------
>> >> This SF.net email is sponsored by Sprint
>> >> What will you do first with EVO, the first 4G phone?
>> >> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> >> _______________________________________________
>> >> cx-oracle-users mailing list
>> >> cx-...@li...
>> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> >
>> >
>> >
>> > ------------------------------------------------------------------------------
>> > This SF.net email is sponsored by Sprint
>> > What will you do first with EVO, the first 4G phone?
>> > Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> > _______________________________________________
>> > cx-oracle-users mailing list
>> > cx-...@li...
>> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>> >
>> >
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

I just noticed something:

> The final SQL string that gets built is:
>
> INSERT INTO import_CAMTARGET
>
(ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES
>
(1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
13:37:17','YYYY-MM-DD
> HH24:MI:SS'),'','APIMaxifier',null,'N')

I have time values which include colons in my SQL string.  Is it possible
these are confusing the parser?  If so, is there a way to escape them?

On Tue, Jul 6, 2010 at 5:35 PM, Anthony Tuininga <ant...@gm...
> wrote:

> No, do not quote the position parameter. Something like this is want you
> want:
>
> cursor.execute("insert into test values (:1, null, null)", ("This is
> the input string",))
>
> BTW, the test scripts can be a source of examples for how things can be
> written.
>
> Anthony
>
> On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...> wrote:
> > Do I need to quote the position parameter?
> >
> > like:
> >
> > INSERT INTO TEST  VALUES (':1', null null)
> >
> > as opposed to:
> >
> > INSERT INTO TEST  VALUES (:1, null null)
> >
> > On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
> > <ant...@gm...> wrote:
> >>
> >> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
> >> > Ya, I already tried it without the * - same error.
> >>
> >> Hmm, interesting. The problem with the illegal variable name/number
> >> problem is that its almost impossible to diagnose the problem. You
> >> have to carefully examine the SQL looking for any colons that may have
> >> strayed outside of quotes or where an extra character is found after
> >> the colon without a space as you were intending, etc. Eventually you
> >> will find it but its always irritating having to search for it -- it
> >> would be nice if Oracle went to the trouble of telling you __what__
> >> variable name/number was illegal!
> >>
> >> To clarify again, the method signature is
> >>
> >> cursor.execute(sql, parameters)
> >>
> >> Where parameters is either a dictionary (named parameters) or sequence
> >> (positional parameters). So if you have a tuple
> >>
> >> clobs = ('This is the clob value',)
> >>
> >> or sequence
> >>
> >> clobs = ['This is the clob value']
> >>
> >> You would call
> >>
> >> cursor.execute(sql, clobs)
> >>
> >> Likewise, if you have keywords you would do the following:
> >>
> >> clobs = { "value1" : "This is the clob value" }
> >> cursor.execute(sql, clobs)
> >>
> >> I have added the ability to do the following as well since it is often
> >> convenient
> >>
> >> cursor.execute(sql, value1 = "This is the clob value")
> >>
> >> The first form is useful if you are building up the parameters
> >> dynamically and the second for known queries with known numbers of
> >> parameters.
> >>
> >> > The final SQL string that gets built is:
> >> >
> >> > INSERT INTO import_CAMTARGET
> >> >
> >> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> >> > VALUES
> >> >
> >> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> >> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> >> > 13:37:17','YYYY-MM-DD
> >> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
> >> >
> >> > My clobs array contains one string element (note the string contains
> >> > newline
> >> > characters could this be an issue?):
> >>
> >> No, the newlines are not a problem. Using bind variables means that
> >> you don't have to worry about special characters of any sort. The data
> >> is passed directly to Oracle without interpretation.
> >>
> >> >  'GB:NEW MILTON
> >> > GB:WALTHAM FOREST
> >> > GB:CAMDEN
> >> > GB:CHELMSFORD
> >> > GB:SOMPTING
> >> > GB:LEATHERHEAD
> >> > GB:WINSFORD
> >> > GB:GAINSBOROUGH
> >> > GB:DINGWALL
> >> > GB:WEST KILBRIDE
> >> > GB:CHESTERFIELD
> >> > GB:BLETCHLEY
> >> > GB:HALESWORTH
> >> > GB:GUILDFORD
> >> > GB:RAGLAN
> >> > GB:HOLLINGWORTH
> >> > GB:ROSLIN
> >> > GB:WOOTTON
> >> > GB:NORMANDY
> >> > GB:EDENBRIDGE
> >> > GB:HEATH
> >> > GB:NEWTOWNABBEY
> >> > GB:TAMWORTH
> >> > GB:TOWNHILL
> >> > GB:FAILSWORTH
> >> > GB:KETTERING
> >> > GB:PELTON
> >> > GB:RYDE
> >> > GB:LYNDHURST
> >> > GB:NORTH BERWICK
> >> > GB:BIRKENHEAD
> >> > GB:LOCHEARNHEAD
> >> > GB:DARVEL
> >> > GB:HOLMES CHAPEL
> >> > GB:STORRINGTON
> >> > GB:SPRINGFIELD
> >> > GB:CARLUKE
> >> > GB:BURY ST EDMUNDS
> >> > GB:DORKING
> >> > GB:CLOGHER
> >> > GB:CROMER
> >> > GB:LANCING
> >> > GB:LONGFIELD
> >> > GB:BROADFORD
> >> > GB:ASHTON-UNDER-LYNE
> >> > GB:CROWLAS
> >> > GB:HOLYWELL
> >> > GB:LOOE
> >> > GB:STAPLEFORD
> >> > GB:PAISLEY
> >> > GB:PORTRUSH
> >> > GB:PLYMPTON
> >> > GB:BRACKLEY
> >> > GB:FARNHAM
> >> > GB:GOLBORNE
> >> > GB:HASLINGDEN
> >> > GB:GRANTOWN-ON-SPEY
> >> > GB:GERRARDS CROSS
> >> > GB:MARKET HARBOROUGH
> >> > GB:CLYDACH
> >> > GB:LYDIATE
> >> > GB:DEVIZES
> >> > GB:BIRTLEY
> >> > GB:STRATHPEFFER
> >> > GB:SHOREHAM
> >> > GB:HANLEY
> >> > GB:WALSINGHAM
> >> > GB:WOODLEY
> >> > GB:BLAIRGOWRIE AND RATTRAY
> >> > GB:BARNET
> >> > GB:ASHLEY
> >> > GB:NOTTINGHAM
> >> > GB:MAUCHLINE
> >> > GB:PINXTON
> >> > GB:WOKINGHAM'
> >> >
> >> > As far as using named parameters (which I'm happy to do to get this
> >> > working): I understand how to establish the named parameters, but not
> >> > how to
> >> > get them into the structure that is passed to execute.  As I
> understand
> >> > it,
> >> > we need to pass in:
> >> >
> >> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
> >> > value2:'sadfasdfasdfasdf')
> >>
> >> See above. Either do this:
> >>
> >> parameters = { "value1" : "asdfadfadfadsfa", "value2" :
> "asfadsfadsfadsf"
> >> }
> >> cursor.execute(sql, parameters)
> >>
> >> or do this
> >>
> >> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
> >>
> >> > I can generate the string "value1" and I can generate the string
> >> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me
> -
> >> > I'm
> >> > not passing a string, I'm passing a variable/string tuple correct?
> How
> >> > do I
> >> > dynamically generate that?
> >>
> >> You can do this to build the parameters up dynamically.
> >> parameters = {}
> >> parameters["value1"] = "adfadsfadf"
> >> parameters["value2"] = "asdfsadfafasdfdsaf"
> >> cursor.execute(sql, parameters)
> >>
> >> > Apologies is this is a very naive question.
> >>
> >> No problem. :-)
> >>
> >> Anthony
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> This SF.net email is sponsored by Sprint
> >> What will you do first with EVO, the first 4G phone?
> >> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> ------------------------------------------------------------------------------
> > This SF.net email is sponsored by Sprint
> > What will you do first with EVO, the first 4G phone?
> > Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

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

No, do not quote the position parameter. Something like this is want you want:

cursor.execute("insert into test values (:1, null, null)", ("This is
the input string",))

BTW, the test scripts can be a source of examples for how things can be written.

Anthony

On Tue, Jul 6, 2010 at 2:42 PM, Jason Boorn <jb...@gm...> wrote:
> Do I need to quote the position parameter?
>
> like:
>
> INSERT INTO TEST  VALUES (':1', null null)
>
> as opposed to:
>
> INSERT INTO TEST  VALUES (:1, null null)
>
> On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga
> <ant...@gm...> wrote:
>>
>> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
>> > Ya, I already tried it without the * - same error.
>>
>> Hmm, interesting. The problem with the illegal variable name/number
>> problem is that its almost impossible to diagnose the problem. You
>> have to carefully examine the SQL looking for any colons that may have
>> strayed outside of quotes or where an extra character is found after
>> the colon without a space as you were intending, etc. Eventually you
>> will find it but its always irritating having to search for it -- it
>> would be nice if Oracle went to the trouble of telling you __what__
>> variable name/number was illegal!
>>
>> To clarify again, the method signature is
>>
>> cursor.execute(sql, parameters)
>>
>> Where parameters is either a dictionary (named parameters) or sequence
>> (positional parameters). So if you have a tuple
>>
>> clobs = ('This is the clob value',)
>>
>> or sequence
>>
>> clobs = ['This is the clob value']
>>
>> You would call
>>
>> cursor.execute(sql, clobs)
>>
>> Likewise, if you have keywords you would do the following:
>>
>> clobs = { "value1" : "This is the clob value" }
>> cursor.execute(sql, clobs)
>>
>> I have added the ability to do the following as well since it is often
>> convenient
>>
>> cursor.execute(sql, value1 = "This is the clob value")
>>
>> The first form is useful if you are building up the parameters
>> dynamically and the second for known queries with known numbers of
>> parameters.
>>
>> > The final SQL string that gets built is:
>> >
>> > INSERT INTO import_CAMTARGET
>> >
>> > (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
>> > VALUES
>> >
>> > (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
>> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
>> > 13:37:17','YYYY-MM-DD
>> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
>> >
>> > My clobs array contains one string element (note the string contains
>> > newline
>> > characters could this be an issue?):
>>
>> No, the newlines are not a problem. Using bind variables means that
>> you don't have to worry about special characters of any sort. The data
>> is passed directly to Oracle without interpretation.
>>
>> >  'GB:NEW MILTON
>> > GB:WALTHAM FOREST
>> > GB:CAMDEN
>> > GB:CHELMSFORD
>> > GB:SOMPTING
>> > GB:LEATHERHEAD
>> > GB:WINSFORD
>> > GB:GAINSBOROUGH
>> > GB:DINGWALL
>> > GB:WEST KILBRIDE
>> > GB:CHESTERFIELD
>> > GB:BLETCHLEY
>> > GB:HALESWORTH
>> > GB:GUILDFORD
>> > GB:RAGLAN
>> > GB:HOLLINGWORTH
>> > GB:ROSLIN
>> > GB:WOOTTON
>> > GB:NORMANDY
>> > GB:EDENBRIDGE
>> > GB:HEATH
>> > GB:NEWTOWNABBEY
>> > GB:TAMWORTH
>> > GB:TOWNHILL
>> > GB:FAILSWORTH
>> > GB:KETTERING
>> > GB:PELTON
>> > GB:RYDE
>> > GB:LYNDHURST
>> > GB:NORTH BERWICK
>> > GB:BIRKENHEAD
>> > GB:LOCHEARNHEAD
>> > GB:DARVEL
>> > GB:HOLMES CHAPEL
>> > GB:STORRINGTON
>> > GB:SPRINGFIELD
>> > GB:CARLUKE
>> > GB:BURY ST EDMUNDS
>> > GB:DORKING
>> > GB:CLOGHER
>> > GB:CROMER
>> > GB:LANCING
>> > GB:LONGFIELD
>> > GB:BROADFORD
>> > GB:ASHTON-UNDER-LYNE
>> > GB:CROWLAS
>> > GB:HOLYWELL
>> > GB:LOOE
>> > GB:STAPLEFORD
>> > GB:PAISLEY
>> > GB:PORTRUSH
>> > GB:PLYMPTON
>> > GB:BRACKLEY
>> > GB:FARNHAM
>> > GB:GOLBORNE
>> > GB:HASLINGDEN
>> > GB:GRANTOWN-ON-SPEY
>> > GB:GERRARDS CROSS
>> > GB:MARKET HARBOROUGH
>> > GB:CLYDACH
>> > GB:LYDIATE
>> > GB:DEVIZES
>> > GB:BIRTLEY
>> > GB:STRATHPEFFER
>> > GB:SHOREHAM
>> > GB:HANLEY
>> > GB:WALSINGHAM
>> > GB:WOODLEY
>> > GB:BLAIRGOWRIE AND RATTRAY
>> > GB:BARNET
>> > GB:ASHLEY
>> > GB:NOTTINGHAM
>> > GB:MAUCHLINE
>> > GB:PINXTON
>> > GB:WOKINGHAM'
>> >
>> > As far as using named parameters (which I'm happy to do to get this
>> > working): I understand how to establish the named parameters, but not
>> > how to
>> > get them into the structure that is passed to execute.  As I understand
>> > it,
>> > we need to pass in:
>> >
>> > cursor.execute(sql, value1:'asdfasdfasdfasdf',
>> > value2:'sadfasdfasdfasdf')
>>
>> See above. Either do this:
>>
>> parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf"
>> }
>> cursor.execute(sql, parameters)
>>
>> or do this
>>
>> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
>>
>> > I can generate the string "value1" and I can generate the string
>> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me -
>> > I'm
>> > not passing a string, I'm passing a variable/string tuple correct?  How
>> > do I
>> > dynamically generate that?
>>
>> You can do this to build the parameters up dynamically.
>> parameters = {}
>> parameters["value1"] = "adfadsfadf"
>> parameters["value2"] = "asdfsadfafasdfdsaf"
>> cursor.execute(sql, parameters)
>>
>> > Apologies is this is a very naive question.
>>
>> No problem. :-)
>>
>> Anthony
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

Do I need to quote the position parameter?

like:

INSERT INTO TEST  VALUES (':1', null null)

as opposed to:

 INSERT INTO TEST  VALUES (:1, null null)


On Tue, Jul 6, 2010 at 11:43 AM, Anthony Tuininga <
ant...@gm...> wrote:

> On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
> > Ya, I already tried it without the * - same error.
>
> Hmm, interesting. The problem with the illegal variable name/number
> problem is that its almost impossible to diagnose the problem. You
> have to carefully examine the SQL looking for any colons that may have
> strayed outside of quotes or where an extra character is found after
> the colon without a space as you were intending, etc. Eventually you
> will find it but its always irritating having to search for it -- it
> would be nice if Oracle went to the trouble of telling you __what__
> variable name/number was illegal!
>
> To clarify again, the method signature is
>
> cursor.execute(sql, parameters)
>
> Where parameters is either a dictionary (named parameters) or sequence
> (positional parameters). So if you have a tuple
>
> clobs = ('This is the clob value',)
>
> or sequence
>
> clobs = ['This is the clob value']
>
> You would call
>
> cursor.execute(sql, clobs)
>
> Likewise, if you have keywords you would do the following:
>
> clobs = { "value1" : "This is the clob value" }
> cursor.execute(sql, clobs)
>
> I have added the ability to do the following as well since it is often
> convenient
>
> cursor.execute(sql, value1 = "This is the clob value")
>
> The first form is useful if you are building up the parameters
> dynamically and the second for known queries with known numbers of
> parameters.
>
> > The final SQL string that gets built is:
> >
> > INSERT INTO import_CAMTARGET
> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> > VALUES
> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> 13:37:17','YYYY-MM-DD
> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
> >
> > My clobs array contains one string element (note the string contains
> newline
> > characters could this be an issue?):
>
> No, the newlines are not a problem. Using bind variables means that
> you don't have to worry about special characters of any sort. The data
> is passed directly to Oracle without interpretation.
>
> >  'GB:NEW MILTON
> > GB:WALTHAM FOREST
> > GB:CAMDEN
> > GB:CHELMSFORD
> > GB:SOMPTING
> > GB:LEATHERHEAD
> > GB:WINSFORD
> > GB:GAINSBOROUGH
> > GB:DINGWALL
> > GB:WEST KILBRIDE
> > GB:CHESTERFIELD
> > GB:BLETCHLEY
> > GB:HALESWORTH
> > GB:GUILDFORD
> > GB:RAGLAN
> > GB:HOLLINGWORTH
> > GB:ROSLIN
> > GB:WOOTTON
> > GB:NORMANDY
> > GB:EDENBRIDGE
> > GB:HEATH
> > GB:NEWTOWNABBEY
> > GB:TAMWORTH
> > GB:TOWNHILL
> > GB:FAILSWORTH
> > GB:KETTERING
> > GB:PELTON
> > GB:RYDE
> > GB:LYNDHURST
> > GB:NORTH BERWICK
> > GB:BIRKENHEAD
> > GB:LOCHEARNHEAD
> > GB:DARVEL
> > GB:HOLMES CHAPEL
> > GB:STORRINGTON
> > GB:SPRINGFIELD
> > GB:CARLUKE
> > GB:BURY ST EDMUNDS
> > GB:DORKING
> > GB:CLOGHER
> > GB:CROMER
> > GB:LANCING
> > GB:LONGFIELD
> > GB:BROADFORD
> > GB:ASHTON-UNDER-LYNE
> > GB:CROWLAS
> > GB:HOLYWELL
> > GB:LOOE
> > GB:STAPLEFORD
> > GB:PAISLEY
> > GB:PORTRUSH
> > GB:PLYMPTON
> > GB:BRACKLEY
> > GB:FARNHAM
> > GB:GOLBORNE
> > GB:HASLINGDEN
> > GB:GRANTOWN-ON-SPEY
> > GB:GERRARDS CROSS
> > GB:MARKET HARBOROUGH
> > GB:CLYDACH
> > GB:LYDIATE
> > GB:DEVIZES
> > GB:BIRTLEY
> > GB:STRATHPEFFER
> > GB:SHOREHAM
> > GB:HANLEY
> > GB:WALSINGHAM
> > GB:WOODLEY
> > GB:BLAIRGOWRIE AND RATTRAY
> > GB:BARNET
> > GB:ASHLEY
> > GB:NOTTINGHAM
> > GB:MAUCHLINE
> > GB:PINXTON
> > GB:WOKINGHAM'
> >
> > As far as using named parameters (which I'm happy to do to get this
> > working): I understand how to establish the named parameters, but not how
> to
> > get them into the structure that is passed to execute.  As I understand
> it,
> > we need to pass in:
> >
> > cursor.execute(sql, value1:'asdfasdfasdfasdf', value2:'sadfasdfasdfasdf')
>
> See above. Either do this:
>
> parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf" }
> cursor.execute(sql, parameters)
>
> or do this
>
> cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")
>
> > I can generate the string "value1" and I can generate the string
> > 'value1=asdfasdfasdfasdf' but I don't see how either of those help me -
> I'm
> > not passing a string, I'm passing a variable/string tuple correct?  How
> do I
> > dynamically generate that?
>
> You can do this to build the parameters up dynamically.
> parameters = {}
> parameters["value1"] = "adfadsfadf"
> parameters["value2"] = "asdfsadfafasdfdsaf"
> cursor.execute(sql, parameters)
>
> > Apologies is this is a very naive question.
>
> No problem. :-)
>
> Anthony
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

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

On Tue, Jul 6, 2010 at 8:52 AM, Jason Boorn <jb...@gm...> wrote:
> Ya, I already tried it without the * - same error.

Hmm, interesting. The problem with the illegal variable name/number
problem is that its almost impossible to diagnose the problem. You
have to carefully examine the SQL looking for any colons that may have
strayed outside of quotes or where an extra character is found after
the colon without a space as you were intending, etc. Eventually you
will find it but its always irritating having to search for it -- it
would be nice if Oracle went to the trouble of telling you __what__
variable name/number was illegal!

To clarify again, the method signature is

cursor.execute(sql, parameters)

Where parameters is either a dictionary (named parameters) or sequence
(positional parameters). So if you have a tuple

clobs = ('This is the clob value',)

or sequence

clobs = ['This is the clob value']

You would call

cursor.execute(sql, clobs)

Likewise, if you have keywords you would do the following:

clobs = { "value1" : "This is the clob value" }
cursor.execute(sql, clobs)

I have added the ability to do the following as well since it is often
convenient

cursor.execute(sql, value1 = "This is the clob value")

The first form is useful if you are building up the parameters
dynamically and the second for known queries with known numbers of
parameters.

> The final SQL string that gets built is:
>
> INSERT INTO import_CAMTARGET
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
> HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
>
> My clobs array contains one string element (note the string contains newline
> characters could this be an issue?):

No, the newlines are not a problem. Using bind variables means that
you don't have to worry about special characters of any sort. The data
is passed directly to Oracle without interpretation.

>  'GB:NEW MILTON
> GB:WALTHAM FOREST
> GB:CAMDEN
> GB:CHELMSFORD
> GB:SOMPTING
> GB:LEATHERHEAD
> GB:WINSFORD
> GB:GAINSBOROUGH
> GB:DINGWALL
> GB:WEST KILBRIDE
> GB:CHESTERFIELD
> GB:BLETCHLEY
> GB:HALESWORTH
> GB:GUILDFORD
> GB:RAGLAN
> GB:HOLLINGWORTH
> GB:ROSLIN
> GB:WOOTTON
> GB:NORMANDY
> GB:EDENBRIDGE
> GB:HEATH
> GB:NEWTOWNABBEY
> GB:TAMWORTH
> GB:TOWNHILL
> GB:FAILSWORTH
> GB:KETTERING
> GB:PELTON
> GB:RYDE
> GB:LYNDHURST
> GB:NORTH BERWICK
> GB:BIRKENHEAD
> GB:LOCHEARNHEAD
> GB:DARVEL
> GB:HOLMES CHAPEL
> GB:STORRINGTON
> GB:SPRINGFIELD
> GB:CARLUKE
> GB:BURY ST EDMUNDS
> GB:DORKING
> GB:CLOGHER
> GB:CROMER
> GB:LANCING
> GB:LONGFIELD
> GB:BROADFORD
> GB:ASHTON-UNDER-LYNE
> GB:CROWLAS
> GB:HOLYWELL
> GB:LOOE
> GB:STAPLEFORD
> GB:PAISLEY
> GB:PORTRUSH
> GB:PLYMPTON
> GB:BRACKLEY
> GB:FARNHAM
> GB:GOLBORNE
> GB:HASLINGDEN
> GB:GRANTOWN-ON-SPEY
> GB:GERRARDS CROSS
> GB:MARKET HARBOROUGH
> GB:CLYDACH
> GB:LYDIATE
> GB:DEVIZES
> GB:BIRTLEY
> GB:STRATHPEFFER
> GB:SHOREHAM
> GB:HANLEY
> GB:WALSINGHAM
> GB:WOODLEY
> GB:BLAIRGOWRIE AND RATTRAY
> GB:BARNET
> GB:ASHLEY
> GB:NOTTINGHAM
> GB:MAUCHLINE
> GB:PINXTON
> GB:WOKINGHAM'
>
> As far as using named parameters (which I'm happy to do to get this
> working): I understand how to establish the named parameters, but not how to
> get them into the structure that is passed to execute.  As I understand it,
> we need to pass in:
>
> cursor.execute(sql, value1:'asdfasdfasdfasdf', value2:'sadfasdfasdfasdf')

See above. Either do this:

parameters = { "value1" : "asdfadfadfadsfa", "value2" : "asfadsfadsfadsf" }
cursor.execute(sql, parameters)

or do this

cursor.execute(sql, value1 = "asadfadsfsadf", value2 = "adfadfadsfad")

> I can generate the string "value1" and I can generate the string
> 'value1=asdfasdfasdfasdf' but I don't see how either of those help me - I'm
> not passing a string, I'm passing a variable/string tuple correct?  How do I
> dynamically generate that?

You can do this to build the parameters up dynamically.
parameters = {}
parameters["value1"] = "adfadsfadf"
parameters["value2"] = "asdfsadfafasdfdsaf"
cursor.execute(sql, parameters)

> Apologies is this is a very naive question.

No problem. :-)

Anthony

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

Ya, I already tried it without the * - same error.

The final SQL string that gets built is:

INSERT INTO import_CAMTARGET
(ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
VALUES
(1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')

My clobs array contains one string element (note the string contains newline
characters could this be an issue?):

 'GB:NEW MILTON
GB:WALTHAM FOREST
GB:CAMDEN
GB:CHELMSFORD
GB:SOMPTING
GB:LEATHERHEAD
GB:WINSFORD
GB:GAINSBOROUGH
GB:DINGWALL
GB:WEST KILBRIDE
GB:CHESTERFIELD
GB:BLETCHLEY
GB:HALESWORTH
GB:GUILDFORD
GB:RAGLAN
GB:HOLLINGWORTH
GB:ROSLIN
GB:WOOTTON
GB:NORMANDY
GB:EDENBRIDGE
GB:HEATH
GB:NEWTOWNABBEY
GB:TAMWORTH
GB:TOWNHILL
GB:FAILSWORTH
GB:KETTERING
GB:PELTON
GB:RYDE
GB:LYNDHURST
GB:NORTH BERWICK
GB:BIRKENHEAD
GB:LOCHEARNHEAD
GB:DARVEL
GB:HOLMES CHAPEL
GB:STORRINGTON
GB:SPRINGFIELD
GB:CARLUKE
GB:BURY ST EDMUNDS
GB:DORKING
GB:CLOGHER
GB:CROMER
GB:LANCING
GB:LONGFIELD
GB:BROADFORD
GB:ASHTON-UNDER-LYNE
GB:CROWLAS
GB:HOLYWELL
GB:LOOE
GB:STAPLEFORD
GB:PAISLEY
GB:PORTRUSH
GB:PLYMPTON
GB:BRACKLEY
GB:FARNHAM
GB:GOLBORNE
GB:HASLINGDEN
GB:GRANTOWN-ON-SPEY
GB:GERRARDS CROSS
GB:MARKET HARBOROUGH
GB:CLYDACH
GB:LYDIATE
GB:DEVIZES
GB:BIRTLEY
GB:STRATHPEFFER
GB:SHOREHAM
GB:HANLEY
GB:WALSINGHAM
GB:WOODLEY
GB:BLAIRGOWRIE AND RATTRAY
GB:BARNET
GB:ASHLEY
GB:NOTTINGHAM
GB:MAUCHLINE
GB:PINXTON
GB:WOKINGHAM'
As far as using named parameters (which I'm happy to do to get this
working): I understand how to establish the named parameters, but not how to
get them into the structure that is passed to execute.  As I understand it,
we need to pass in:

cursor.execute(sql, value1:'asdfasdfasdfasdf', value2:'sadfasdfasdfasdf')

I can generate the string "value1" and I can generate the string
'value1=asdfasdfasdfasdf' but I don't see how either of those help me - I'm
not passing a string, I'm passing a variable/string tuple correct?  How do I
dynamically generate that?

Apologies is this is a very naive question.





On Tue, Jul 6, 2010 at 9:57 AM, Anthony Tuininga <ant...@gm...
> wrote:

> On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jb...@gm...> wrote:
> > The named parameter option is not really going to work as well for me
> > because my sql is dynamically generated.  So I'm trying to get the
> position
> > parameter piece working.
>
> Sure. You can always do what I did before positional parameters were
> supported: generate names like this
>
> "v%d" % i
>
> That's not ideal but its a fallback position if all else fails. :-)
>
> > In the code below, I set up an array containing 20 objects (there are 20
> > columns in this table) named inputsizes.  This goes through.  The code
> > breaks on trying to execute.  I get the error:
> >
> >
> > Could not insert temp: ORA-01036: illegal variable name/number
>
> That means that the SQL string you generated and the parameters you
> are passing don't match. If my suggestion below doesn't help, please
> send the SQL string and a (small) representation of what you are
> trying to pass.
>
> > The code below generates a sql execution string by creating an insert
> clause
> > (with columns) and a values clause (with values to insert):
> >
> >              while staging_row != None:
> >                 sqlstring = "INSERT INTO %s (" %
> > sectionconfig["TEMPORARYTABLE"]
> >                 valuesstring = " VALUES ("
> >                 clobs = []
> >                 inputsizes = []
> >                 firstelem = True
> >                 for colname in colnames:
> >                     curval = staging_row[colname]
> >                     if (firstelem == True):
> >                         firstelem = False
> >                     else:
> >                         sqlstring += ","
> >                         valuesstring += ","
> >                     sqlstring += colname
> >                     if (len(curval) > 0):
> >                         if (len(curval) > 4000):
> >                             clobs.append(curval)
> >                             valuesstring += ":" + str(len(clobs))
> >                             inputsizes.append(cx_Oracle.CLOB)
> >                         else:
> >                             inputsizes.append(None)
> >                             valuesstring += curval
> >                     else:
> >                         inputsizes.append(None)
> >                         valuesstring += "null"
> >                 executestring = sqlstring + ")" + valuesstring + ")"
> >                 try:
> >                     if (len(clobs) == 0):
> >                         destination_cursor.execute(executestring)
> >                     else:
> >                         destination_cursor.setinputsizes(*inputsizes)
>
> *** LOOK HERE ***
>
> >                         destination_cursor.execute(executestring, *clobs)
>
> You should do this instead:
>
> destination_cursor.execute(executestring, clobs)
>
> Note the removal of the "*". That "*" expands the parameters. Since
> the parameter you are passing is a single element sequence, what
> actually happens is the string itself is expanded and as many
> parameters as there are characters in the string is passed! I'm sure
> that's not what you wanted!
>
> As for why execute() and setinputsizes() are different, consult the DB API.
> :-)
>
> >                 except Exception:
> >                     e = sys.exc_info()[1]
> >                     log.error("Could not insert temp: %s" % e)
> >                     log.error(executestring)
> >
> >                 staging_row = rowgenerator.next()
> >                 if (rownum % 1000 == 0):
> >                     destinationConnection.commit()
> >
> >
> > My execute string looks like:
> > INSERT INTO import_CAMTARGET
> >
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> > VALUES
> >
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> > 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18
> 13:37:17','YYYY-MM-DD
> > HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
> >
> > which appears to be correct, and my clobs array contains one string which
> > matches the value I want to include in position 1.
> > Thanks for taking the time to look at this - it's been a problem for me
> for
> > a few days now.
>
> You're welcome. Hopefully this works for you.
>
> Anthony
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

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

On Tue, Jul 6, 2010 at 7:43 AM, Jason Boorn <jb...@gm...> wrote:
> The named parameter option is not really going to work as well for me
> because my sql is dynamically generated.  So I'm trying to get the position
> parameter piece working.

Sure. You can always do what I did before positional parameters were
supported: generate names like this

"v%d" % i

That's not ideal but its a fallback position if all else fails. :-)

> In the code below, I set up an array containing 20 objects (there are 20
> columns in this table) named inputsizes.  This goes through.  The code
> breaks on trying to execute.  I get the error:
>
>
> Could not insert temp: ORA-01036: illegal variable name/number

That means that the SQL string you generated and the parameters you
are passing don't match. If my suggestion below doesn't help, please
send the SQL string and a (small) representation of what you are
trying to pass.

> The code below generates a sql execution string by creating an insert clause
> (with columns) and a values clause (with values to insert):
>
>              while staging_row != None:
>                 sqlstring = "INSERT INTO %s (" %
> sectionconfig["TEMPORARYTABLE"]
>                 valuesstring = " VALUES ("
>                 clobs = []
>                 inputsizes = []
>                 firstelem = True
>                 for colname in colnames:
>                     curval = staging_row[colname]
>                     if (firstelem == True):
>                         firstelem = False
>                     else:
>                         sqlstring += ","
>                         valuesstring += ","
>                     sqlstring += colname
>                     if (len(curval) > 0):
>                         if (len(curval) > 4000):
>                             clobs.append(curval)
>                             valuesstring += ":" + str(len(clobs))
>                             inputsizes.append(cx_Oracle.CLOB)
>                         else:
>                             inputsizes.append(None)
>                             valuesstring += curval
>                     else:
>                         inputsizes.append(None)
>                         valuesstring += "null"
>                 executestring = sqlstring + ")" + valuesstring + ")"
>                 try:
>                     if (len(clobs) == 0):
>                         destination_cursor.execute(executestring)
>                     else:
>                         destination_cursor.setinputsizes(*inputsizes)

*** LOOK HERE ***

>                         destination_cursor.execute(executestring, *clobs)

You should do this instead:

destination_cursor.execute(executestring, clobs)

Note the removal of the "*". That "*" expands the parameters. Since
the parameter you are passing is a single element sequence, what
actually happens is the string itself is expanded and as many
parameters as there are characters in the string is passed! I'm sure
that's not what you wanted!

As for why execute() and setinputsizes() are different, consult the DB API. :-)

>                 except Exception:
>                     e = sys.exc_info()[1]
>                     log.error("Could not insert temp: %s" % e)
>                     log.error(executestring)
>
>                 staging_row = rowgenerator.next()
>                 if (rownum % 1000 == 0):
>                     destinationConnection.commit()
>
>
> My execute string looks like:
> INSERT INTO import_CAMTARGET
> (ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
> VALUES
> (1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
> 16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
> HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')
>
> which appears to be correct, and my clobs array contains one string which
> matches the value I want to include in position 1.
> Thanks for taking the time to look at this - it's been a problem for me for
> a few days now.

You're welcome. Hopefully this works for you.

Anthony

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

The named parameter option is not really going to work as well for me
because my sql is dynamically generated.  So I'm trying to get the position
parameter piece working.

In the code below, I set up an array containing 20 objects (there are 20
columns in this table) named inputsizes.  This goes through.  The code
breaks on trying to execute.  I get the error:


Could not insert temp: ORA-01036: illegal variable name/number
The code below generates a sql execution string by creating an insert clause
(with columns) and a values clause (with values to insert):

             while staging_row != None:
                sqlstring = "INSERT INTO %s (" %
sectionconfig["TEMPORARYTABLE"]
                valuesstring = " VALUES ("
                clobs = []
                inputsizes = []
                firstelem = True
                for colname in colnames:
                    curval = staging_row[colname]
                    if (firstelem == True):
                        firstelem = False
                    else:
                        sqlstring += ","
                        valuesstring += ","
                    sqlstring += colname
                    if (len(curval) > 0):
                        if (len(curval) > 4000):
                            clobs.append(curval)
                            valuesstring += ":" + str(len(clobs))
                            inputsizes.append(cx_Oracle.CLOB)
                        else:
                            inputsizes.append(None)
                            valuesstring += curval
                    else:
                        inputsizes.append(None)
                        valuesstring += "null"
                executestring = sqlstring + ")" + valuesstring + ")"
                try:
                    if (len(clobs) == 0):
                        destination_cursor.execute(executestring)
                    else:
                        destination_cursor.setinputsizes(*inputsizes)
                        destination_cursor.execute(executestring, *clobs)

                except Exception:
                    e = sys.exc_info()[1]
                    log.error("Could not insert temp: %s" % e)
                    log.error(executestring)

                staging_row = rowgenerator.next()
                if (rownum % 1000 == 0):
                    destinationConnection.commit()


 My execute string looks like:
INSERT INTO import_CAMTARGET
(ACCOUNTKEY,CAMPAIGNKEY,CAMTARGETKEY,ZIP,DOMAIN,SEARCHTERM,SEARCHANYALL,COOKIE,RDBPROFILE,NEGATETARGET,CITY,AREACODE,SEGMENTANYALL,RECORDSTATE,WHENCREATED,WHENMODIFIED,WHOCREATED,WHOMODIFIED,TEMPLATEID,ALLNONMOBILEDEVICE)
VALUES
(1789,3946029,1765718,null,null,null,'A',null,null,'N',:1,null,'A','L',to_date('2010-06-03
16:28:59','YYYY-MM-DD HH24:MI:SS'),to_date('2010-06-18 13:37:17','YYYY-MM-DD
HH24:MI:SS'),'matthew.kay_guardian.co.uk','APIMaxifier',null,'N')

which appears to be correct, and my clobs array contains one string which
matches the value I want to include in position 1.

Thanks for taking the time to look at this - it's been a problem for me for
a few days now.

On Mon, Jul 5, 2010 at 4:10 PM, Anthony Tuininga <ant...@gm...
> wrote:

> Ok, let me see if I can clear things up. If you want to use positional
> parameters, you need to do the following:
>
> cursor.setinputsizes(cx_Oracle.CLOB)
> cursor.execute("insert.... values (:1)", (clobValue,))
>
> cursor.setinputsizes(None, None, cx_Oracle.CLOB)
> cursor.execute("insert.... values (:1, :2, :3), (1, "String", clobValue))
>
> If you want to use named parameters, you need to do the following:
>
> cursor.setinputsizes(value1 = cx_Oracle.CLOB)
> cursor.execute("insert.... values (:value1)", value1 = clobValue)
>
> cursor.setinputsizes(value3 = cx_Oracle.CLOB)
> cursor.execute("insert.... values (:value1, :value2, :value3)", value1
> = 1, value2 = "String", value3 = clobValue)
>
> Where clobValue refers to the actual string contents.
>
> Hopefully that explains things well enough? If not, show me your exact
> code and I'll attempt to show you where you are going wrong.
>
> Anthony
>
> On Mon, Jul 5, 2010 at 12:56 PM, Jason Boorn <jb...@gm...> wrote:
> >
> > I set up clobs as {}, inputsizes as {} and populate a single value in the
> > SQL statement:
> >
> > INSERT INTO . . . VALUES (:value1, . . .)
> >
> > clobs['value1'] = <some string>
> > inputsizes['value1']=cx.Oracle
> >
> > I call:
> >
> > destination_cursor.setinputsizes(inputsizes)
> >
> > destination_cursor.execute(executestring, clobs)
> >
> > I get an error:
> >
> > Could not insert temp: Variable_TypeByPythonType(): unhandled data type
> >
> > This was the original error which led me to use an array for clobs in the
> > first place.  Ideas?
> >
> >
> >
> > On Sun, Jul 4, 2010 at 5:27 PM, Amaury Forgeot d'Arc <ama...@gm...
> >
> > wrote:
> >>
> >> 2010/7/4 Jason Boorn <jb...@gm...>:
> >> > now I get the following:
> >> >
> >> >
> >> > ORA-01036: illegal variable name/number
> >> >
> >> > I tried defining the input by both position (:1) and by named argument
> >> > (:value1)
> >> >
> >> > By position,
> >> > My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
> >> > And my execution is (cursor.execute(sql, *clobs))
> >>
> >> Here it's the other way round :-)
> >> The documentation of cursor.execute:
> >> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute
> >> states that the parameters must be given as one sequence.
> >> Try with
> >>   cursor.execute(sql, *clobs)
> >>
> >>
> >> --
> >> Amaury Forgeot d'Arc
> >>
> >>
> >>
> ------------------------------------------------------------------------------
> >> This SF.net email is sponsored by Sprint
> >> What will you do first with EVO, the first 4G phone?
> >> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> >> _______________________________________________
> >> cx-oracle-users mailing list
> >> cx-...@li...
> >> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
> >
> ------------------------------------------------------------------------------
> > This SF.net email is sponsored by Sprint
> > What will you do first with EVO, the first 4G phone?
> > Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> > _______________________________________________
> > cx-oracle-users mailing list
> > cx-...@li...
> > https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
> >
> >
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

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

Ok, let me see if I can clear things up. If you want to use positional
parameters, you need to do the following:

cursor.setinputsizes(cx_Oracle.CLOB)
cursor.execute("insert.... values (:1)", (clobValue,))

cursor.setinputsizes(None, None, cx_Oracle.CLOB)
cursor.execute("insert.... values (:1, :2, :3), (1, "String", clobValue))

If you want to use named parameters, you need to do the following:

cursor.setinputsizes(value1 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1)", value1 = clobValue)

cursor.setinputsizes(value3 = cx_Oracle.CLOB)
cursor.execute("insert.... values (:value1, :value2, :value3)", value1
= 1, value2 = "String", value3 = clobValue)

Where clobValue refers to the actual string contents.

Hopefully that explains things well enough? If not, show me your exact
code and I'll attempt to show you where you are going wrong.

Anthony

On Mon, Jul 5, 2010 at 12:56 PM, Jason Boorn <jb...@gm...> wrote:
>
> I set up clobs as {}, inputsizes as {} and populate a single value in the
> SQL statement:
>
> INSERT INTO . . . VALUES (:value1, . . .)
>
> clobs['value1'] = <some string>
> inputsizes['value1']=cx.Oracle
>
> I call:
>
> destination_cursor.setinputsizes(inputsizes)
>
> destination_cursor.execute(executestring, clobs)
>
> I get an error:
>
> Could not insert temp: Variable_TypeByPythonType(): unhandled data type
>
> This was the original error which led me to use an array for clobs in the
> first place.  Ideas?
>
>
>
> On Sun, Jul 4, 2010 at 5:27 PM, Amaury Forgeot d'Arc <ama...@gm...>
> wrote:
>>
>> 2010/7/4 Jason Boorn <jb...@gm...>:
>> > now I get the following:
>> >
>> >
>> > ORA-01036: illegal variable name/number
>> >
>> > I tried defining the input by both position (:1) and by named argument
>> > (:value1)
>> >
>> > By position,
>> > My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
>> > And my execution is (cursor.execute(sql, *clobs))
>>
>> Here it's the other way round :-)
>> The documentation of cursor.execute:
>> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute
>> states that the parameters must be given as one sequence.
>> Try with
>>   cursor.execute(sql, *clobs)
>>
>>
>> --
>> Amaury Forgeot d'Arc
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>
>

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

I set up clobs as {}, inputsizes as {} and populate a single value in the
SQL statement:

INSERT INTO . . . VALUES (:value1, . . .)

clobs['value1'] = <some string>
inputsizes['value1']=cx.Oracle

I call:

destination_cursor.setinputsizes(inputsizes)

destination_cursor.execute(executestring, clobs)
I get an error:

Could not insert temp: Variable_TypeByPythonType(): unhandled data type

This was the original error which led me to use an array for clobs in the
first place.  Ideas?




On Sun, Jul 4, 2010 at 5:27 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> 2010/7/4 Jason Boorn <jb...@gm...>:
> > now I get the following:
> >
> >
> > ORA-01036: illegal variable name/number
> >
> > I tried defining the input by both position (:1) and by named argument
> > (:value1)
> >
> > By position,
> > My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
> > And my execution is (cursor.execute(sql, *clobs))
>
> Here it's the other way round :-)
> The documentation of cursor.execute:
> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute
> states that the parameters must be given as one sequence.
> Try with
>   cursor.execute(sql, *clobs)
>
>
> --
>  Amaury Forgeot d'Arc
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>

Re: [cx-oracle-users] setinputsizes problem?

[Amaury F. d'A. <ama...@gm...>]

2010/7/4 Jason Boorn <jb...@gm...>:
> now I get the following:
>
>
> ORA-01036: illegal variable name/number
>
> I tried defining the input by both position (:1) and by named argument
> (:value1)
>
> By position,
> My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
> And my execution is (cursor.execute(sql, *clobs))

Here it's the other way round :-)
The documentation of cursor.execute:
http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.execute
states that the parameters must be given as one sequence.
Try with
   cursor.execute(sql, *clobs)


-- 
Amaury Forgeot d'Arc

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

now I get the following:


ORA-01036: illegal variable name/number
I tried defining the input by both position (:1) and by named argument
(:value1)

By position,
My SQL expression reads : INSERT INTO . . . values (:1, null . . .)
And my execution is (cursor.execute(sql, *clobs))
Where clobs is an array of one long string: 'asdfasdfasdfasd  . . '

By name,
 My SQL expression reads : INSERT INTO . . . values (:value1, null . . .)
And my execution is (cursor.execute(sql, *clobs))
Where clobs is an array of one long string: value1='asdfasdfasdfasd  . . '

Both of these give the same error
On Sat, Jul 3, 2010 at 6:11 PM, Jason Boorn <jb...@gm...> wrote:

> Thanks again - sorry for the confusion with the * operator - I hadn't used
> it before.
>
>
> On Sat, Jul 3, 2010 at 5:11 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:
>
>> 2010/7/3 Jason Boorn <jb...@gm...>:
>> > Thank you, but I'm using python - could you tell me what kind of a
>> structure
>> > the argument should be?  And how to populate it?
>> >
>> > My original function call was called with an array argument, an array
>> > containing all of the fields being inserted.
>>
>> In the docs:
>> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
>> you should not call setinputsizes() with one argument, but with one
>> argument per variable.
>> since you already have a list containing the various sizes,
>>    cursor.setinputsizes(*inputsizes)
>> (note the * symbol) will "explode" the list and pass each list items
>> as separate arguments to the function.
>>
>> --
>>  Amaury Forgeot d'Arc
>>
>>
>> ------------------------------------------------------------------------------
>> This SF.net email is sponsored by Sprint
>> What will you do first with EVO, the first 4G phone?
>> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
>> _______________________________________________
>> cx-oracle-users mailing list
>> cx-...@li...
>> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>>
>
>

Re: [cx-oracle-users] setinputsizes problem?

[Jason B. <jb...@gm...>]

Thanks again - sorry for the confusion with the * operator - I hadn't used
it before.

On Sat, Jul 3, 2010 at 5:11 PM, Amaury Forgeot d'Arc <ama...@gm...>wrote:

> 2010/7/3 Jason Boorn <jb...@gm...>:
> > Thank you, but I'm using python - could you tell me what kind of a
> structure
> > the argument should be?  And how to populate it?
> >
> > My original function call was called with an array argument, an array
> > containing all of the fields being inserted.
>
> In the docs:
> http://cx-oracle.sourceforge.net/html/cursor.html#Cursor.setinputsizes
> you should not call setinputsizes() with one argument, but with one
> argument per variable.
> since you already have a list containing the various sizes,
>    cursor.setinputsizes(*inputsizes)
> (note the * symbol) will "explode" the list and pass each list items
> as separate arguments to the function.
>
> --
>  Amaury Forgeot d'Arc
>
>
> ------------------------------------------------------------------------------
> This SF.net email is sponsored by Sprint
> What will you do first with EVO, the first 4G phone?
> Visit sprint.com/first -- http://p.sf.net/sfu/sprint-com-first
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>