Hello

I am busy migrating a C-application from RdB (OpenVMS, usinf SQL module language) to Oracle (Linux, using embedded SQL). I now found a probleme which I have not been able to solve.

In RdB, we had a table

create table mytab(id integer, name char(10)) ;
insert into mytab values(1, 'smith') ;
insert into mytab values(2, 'smith ') ;

As datatype char does not regard trailing spaces, values 'smith' and 'smith ' in the database where regarded as equal.

As our customer wishes, we had to use VARCHAR2 instead of CHAR in the new Oracle database:

create table mytab(id integer, name char(10)) ;

Now, the values 'smith' and 'smith ' are regarded as different, but I can live with this.

What I do not understand is that in an embedded SQL statement when selecting the names, both are filled with padding spaces to the length of the host variable:

void mytab_select_name(long id, char name[11])
{
EXEC SQL
SELECT mytab.name INTO :name FROM mytab WHERE id = :id ;
}

Both calls mytab_select_name(1, name) and mytab_select_name(2, name) give me the value 'smith ' (six trailing spaces and \0) in the variable <name>. When I now write them back into the database, I have the names filled up with spaces, and when I select the entries by name I get
select * from mytab where name = 'smith' ; -> no rows found
select * from mytab where name = 'smith ' ; -> no rows found
select * from mytab where name = 'smith ' ; -> no rows found

Which would be the most efficient way to change this behaviour? I do not need trailing spaces in my values.

Ways I found:

select * from mytab where cast(name as char(10)) = 'smith' ;
select * from mytab where trim(name) = trim('smith ') ;

As I got more than 4000 embedded SQL statements, best for me where a way I could change the behaviour 'globally'.

In advance, thank you very much for your help

Best regards
Jörg

create table mytab(id integer, name varchar2(10)) ;
insert into mytab values(1, 'smith') ;
insert into mytab values(2, 'smith ') ;
commit;

select *
from   mytab
where  name = 'smith    ';

no rows selected

rename mytab to mytab_t;
create or replace view mytab as
  select id, cast(name as char(10)) name from mytab_t;

select *
from   mytab
where  name = 'smith    ';

        ID NAME     
---------- ----------
         1 smith     
         2 smith     

insert into mytab (id, name) values(3, 'smith ') ;

SQL Error: ORA-01733: virtual column not allowed here

create or replace trigger trig 
instead of insert on mytab 
for each row
begin
  insert into mytab_t 
  values (:new.id, cast(:new.name as varchar2(10)));
end;
/

insert into mytab (id, name) values(3, 'smith ') ;

select * from mytab;

        ID NAME     
---------- ----------
         1 smith     
         2 smith     
         3 smith