About Process Of Char type of Oracel in PonyORM

Question

I used the PonyORM with Oracle, I found that it can not support the CHAR type of ORACLE well.

for example, I have a field whose type is CHAR(15), and I fill it up with 1234567890 after it is saved into this field, the oracle will add padding space after the value. like '1234567890 '.

If I use other tools to search with this field as the condition, there is no need to add padding space after the value, but PonyORM can not. for example:

select * from table where field='1234567890     '; => the record can be retrieved

select * from table where field='1234567890';      => the record can not be retrieved. 

Is there any method that solves this issue? Or, Maybe I need something extra set with PonyORM?

Answer 1

If it is Oracle Select statement then you could use TRIM() function to remove leading and trailing spaces.

select * from table where TRIM(field) = '1234567890';

There are also LTRIM() and RTRIM() functions removing leading and trailing spaces respectively...
Oracle uses VARCHAR2 datatype for variable length string values.
Here is a small sample comparing CHAR and VARCHAR2 columns in Oracle:

Create table TEST_TABLE (CHAR_COL CHAR(10), VCHAR_COL VARCHAR2(10));
Insert Into TEST_TABLE VALUES('ABCD', 'EFGH');
Commit;
/*
Table TEST_TABLE created.
1 row inserted.
Commit complete.        */
Select  CHAR_COL, Length(CHAR_COL) "CHAR_LEN", 
        VCHAR_COL, Length(VCHAR_COL) "VCHAR_LEN"
From    TEST_TABLE
/*
CHAR_COL     CHAR_LEN VCHAR_COL   VCHAR_LEN
---------- ---------- ---------- ----------
ABCD               10 EFGH                4   */

Both columns created with space for 10 characters, both populated with 4 letters but the length of CHAR column is 10 while VARCHAR2 is 4.

Answer 2

No choice, I wrote a help function to add the padding space and called it when the column type in Oracle is CHAR, which is defined with sql_type parameter in the Optional or Required constructor in PonyORM

def wrapChar(field: Optional, value:str):
if field.sql_type and field.sql_type.upper().startswith('CHAR'):
    value = value.strip().ljust(field.args[0], ' ')

here is an example of usage.

frootitemno = wrapChar(CUSTOMSTRMH.frootitemno, frootitemno)