Re: [cx-oracle-users] Passing None as values of bind variables in cx_Oracle's cursor.execute() command

[Wong W. Meng-R. <r3...@fr...>]

Hi Amaury,

This is the conclusion from my testing. Yes indeed the None *is* well supported in cx_Oracle. It is a bug that is introduced when I was trying to reorder the position of bind variables in the argument to SQL so that we support backward compatibility with oracledb.

Thanks again for your earlier reply.

Regards,
Wah Meng

From: Wong Wah Meng-R32813
Sent: Thursday, December 08, 2011 10:08 AM
To: cx-...@li...
Subject: RE: [cx-oracle-users] Passing None as values of bind variables in cx_Oracle's cursor.execute() command

Thanks for your reply. Based on your None as bind variable's value *is* supported, I would like to redo my testing to reaffirm the case before updating you my test script. Maybe something was introduced in my inputtypehandler of the cursor object. I will revert with my findings later.

From: Amaury Forgeot d'Arc [mailto:ama...@gm...]
Sent: Wednesday, December 07, 2011 6:28 PM
To: cx-...@li...
Subject: Re: [cx-oracle-users] Passing None as values of bind variables in cx_Oracle's cursor.execute() command

Hi,
2011/12/7 Wong Wah Meng-R32813 <r3...@fr...<mailto:r3...@fr...>>
Hello there,

I am converting my application from python 1.5.2 and oracledb to python 2.7.1 and cx_Oracle..

I noticed one issue here whenever the bind variable's value is None, it exhibits some strange behavior.

First observation: When it is the first time I pass in a bind variable as None, it gives an EOL file. However, subsequent passing of None value to the same SQL seems to be working fine.

This is surprising. But what do you mean by "EOL file"?

Second observation: When there is more than one bind variables contain None value, the value that actually got received at Oracle level seems to be messed up. (We turn on the highest debug level at Oracle level in order to see the bind variable value that are received). Those defined as None are received as some arbitrary value, some are the values from one or a few of the total bind variables.

When None is passed, cx_Oracle sets the "NULL indicator" of the bind variable. But this changes only one bit of the memory, so if you look at the raw memory, it will indeed contain random data except for the "NULL indicator" bit.

I read something about passing None is not supported in cx_Oracle, is it true and is there a workaround? I think the second observation could be due to the buffer is not flushed from a previous usage and still being used in the subsequent call.

Is there anyway we can get rid of both observation? Must we remove any bind variables that we know carrying None as value in the SQL statement? This looks like some extensive change if our application needs to verify the value first before it being used in the SQL.

The None value *is* supported. There used to be an issue when cursor.executemany() determines the type of a variable, when the first value for this variable is a None and following values are numbers.
But this caused an explicit conversion error, and it has been fixed some releases ago anyway.

Could you show us a test script that reproduces your problem?

--
Amaury Forgeot d'Arc