2

When I try to do a "Select" statement using a data type "nChar" into the where column I must pad the input value before passing it into the input value of the adapter. This happens mainly because the nchars in oracle have a fixed length. I would like to know if there is a way to bypass this behavior so that i can retrieve, for example, record like this: supposing that name is an nchar(8) column

select surname from people where name='joe'

instead of be obliged to do

select surname from people where name='joe     '

This is my environment: WebMethods 9.7 Adapter 9.0 ojdbc7

Truman
  • 72
  • 8

1 Answers1

1

Either you change your column data type to NVARCHAR(8) or use a LIKE operator as

where name like 'joe%'

(OR) use TRIM() function like

where TRIM(name) = 'joe'

(OR) use RPAD() function like

where name = RPAD('joe',8, ' ');
Rahul
  • 76,197
  • 13
  • 71
  • 125
  • joe% will also pull in joseph as well – kevinskio Jun 25 '15 at 13:44
  • Unfortunately I'm not the administrator of the DB and I can't use trim function. Use of "LIKE" is a workaround and, as you just have demonstrated, it can't replace the "trim" in all possible cases. It would be better if there would be some trick to apply on the connection properties or using some driver different from ojdbc6 or 7 – Truman Jun 25 '15 at 14:01
  • @Truman. you don't need admin privilege to use any of the function like TRIM/RPAD; you can use those function with the query you are issuing. Not sure about any driver properties which can achieve this. – Rahul Jun 25 '15 at 14:03
  • I've written about administrator because you suggested to change column data type to NVARCHAR(8). Furthermore the adapter v9.0 doesn't allow me to edit the "colum" field so i can't use TRIM/RPAD/SUBSTR or anything else different from the column name only – Truman Jun 25 '15 at 14:58