Re: [cx-oracle-users] Filtering on char/nchar

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

Hi,

This is expected behaviour. The "CHAR" type fields all expect space-padded
values. cx_Oracle will bind as VARCHAR2 for string values (or NVARCHAR2 for
Python 2.7 unicode values), however. In that case, you need to specify the
right number of spaces for the comparison to be successful. If you use CHAR
(or NCHAR) specifically, Oracle automatically adds the necessary spaces. In
general, you should only use CHAR or NCHAR for fixed width values -- or you
will have to pad with the right number of spaces yourself or specifically
use the CHAR or NCHAR types.

Anthony

On Mon, Sep 30, 2019 at 3:40 AM kotofos <ib...@gm...> wrote:

> Hello,
>
> I have a question about char/nchar and binding parameters type.
>
> We have tables which have char and nchar PK columns. When filtering with
> bound parameters on these columns, it is required to put in exact value
> with padding in where clause. Though, when the same is done as text,
> without params filtering works as expected.
>
> Examples:
> CREATE TABLE DBTESTING.nchartable
> (
>     id NCHAR(4) PRIMARY KEY NOT NULL
> );
> insert into nchartable values ('1');
>
> cur.execute("select * from nchartable where id = '1'")  found
> cur.execute("select * from nchartable where id = :id", ['1   ']) found
> cur.execute("select * from nchartable where id = :id", ['1']) not found
>
> After debugging I found, that where clause fails because the parameter is
> nvarchar2. When I manually set the type to nchar all cases above works. See
> attached code.
> Is this behavior intended or it is a bug?
> How to correctly search on nchar column without adding padding in a query
> and preferably without specifying type manually?
>
> Checked on cx_oracle 7.2.2
> _______________________________________________
> cx-oracle-users mailing list
> cx-...@li...
> https://lists.sourceforge.net/lists/listinfo/cx-oracle-users
>