0

I am currently migrating a C-application from RdB (OpenVMS) to Oracle 11 (Linux). I now stumbled over a difference between the two database systems.

Example:

create table MYTAB(id number(13), name varchar2(10)) ;

Contents

Id   Name
1    Smith
2    Smith<blank>    // trailing blank after Smith

When I select the names in Embedded SQL, Oracle adds trailing spaces up to the length of my host variable, i.e., the two names cannot be distinguished. Even if I TRIM the name, Oracle adds spaces.

But with a Select-Statement by column name I must give the exact number of spaces:

select id from MYTAB where name ='Smith'  -> gives Id 1
select id from MYTAB where name ='Smith ' -> gives Id 2

This makes it very difficult for me to handle the data because I cannot differ the number of trailing spaces from my select statement.

Is there any way to stop Oracle from blank-padding?

Thanks a lot in advance Jörg

CW Holeman II
  • 4,661
  • 7
  • 41
  • 72
Jörg Mohren
  • 87
  • 5
  • It is impossible that Oracle would pad blank spaces in a **VARCHAR2** type column. It happens only if you have a **CHAR** type. If you have such values, then the only reason is that the data itself is stored as blank padded. – Lalit Kumar B Dec 03 '15 at 11:27

2 Answers2

1

I think I found a solution myself. If I add the option CHAR_MAP=STRING to the precompiler call, the embedded SQL calls to select a VARCHAR2-column do not fill my strings with blanks. With this I can lhandle my problem.

Best regards Jörg

Jörg Mohren
  • 87
  • 5
0

You have the field has VARCHAR2() which will not cause any blank padding, if you have CHAR() that does.

On the insert statement you need to TRIM() the value before inserting. It sounds to me your conversion into the table is not correct.

On your select you should be able to do...

select id from MYTAB where trim(name) ='Smith'

Also when you start using functions like TRIM() on the where clause it can cause Oracle to do a table scan and ignore indexes.

Elim Garak
  • 1,728
  • 1
  • 16
  • 21
  • 1
    The problem is not that the value is blank-padded in the database (it is not). My problem is that when selecting the value via Embedded SQL, the value is returned blank-padded (filled with trailing blanks up to the length of the host variable) – Jörg Mohren Dec 04 '15 at 09:55