Losing indexing table in Oracle

Question

I had a table with char(20) datatype column and i indexed this column.When i do:

select * from ex_table where charColumn = 'abc'

Exception No_Data_Found raised.Because data is 'abc '.How i can search like this without losing indexing?Thanks for helping!

Answer 1

It is very, very rarely a good idea to use a CHAR data type. You almost always want to use a VARCHAR2 instead. A CHAR(20) always stores 20 bytes worth of data. If there isn't enough data, the CHAR column will append an additional 17 space characters to the right-hand side of your string to pad it out to the required 20 bytes.

That being said, if you are literally doing what you say you are doing, you'd get the row(s) back correctly regardless of the data type.

SQL> create table foo( col1 char(20), col2 varchar2(20) );

Table created.

SQL> insert into foo values( 'abc','abc' );

1 row created.

SQL> select * from foo where col1 = 'abc';

COL1                 COL2
-------------------- --------------------
abc                  abc

SQL> select * from foo where col2 = 'abc';

COL1                 COL2
-------------------- --------------------
abc                  abc

I suspect, however, that what you are really doing is trying to compare the data in your CHAR(20) column to the data in a VARCHAR2 local variable. If that is the case, then Oracle uses VARCHAR2 comparison semantics and the extra 17 space characters at the end of the CHAR(20) column cause the match to fail

SQL> declare
  2    l_str varchar2(20) := 'abc';
  3    l_row foo%rowtype;
  4  begin
  5    select *
  6      into l_row
  7      from foo
  8     where col1 = l_str;
  9    dbms_output.put_line( l_row.col1 );
 10  end;
 11  /
declare
*
ERROR at line 1:
ORA-01403: no data found
ORA-06512: at line 5

You could either compare the data in the column to the data in a CHAR(20) local variable (in real code, you'd want to use anchored types like foo.col1%type rather than hard-coding a length, I'm just using a hard-coded type for emphasis)

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_str char(20) := 'abc';
       -- In real code, this would be
       -- l_str foo.col1%type := 'abc';
  3    l_row foo%rowtype;
  4  begin
  5    select *
  6      into l_row
  7      from foo
  8     where col1 = l_str;
  9    dbms_output.put_line( l_row.col1 );
 10* end;
SQL> /
abc

PL/SQL procedure successfully completed.

Or you can RPAD the VARCHAR2 out to the appropriate length

SQL> ed
Wrote file afiedt.buf

  1  declare
  2    l_str varchar2(20) := 'abc';
  3    l_row foo%rowtype;
  4  begin
  5    select *
  6      into l_row
  7      from foo
  8     where col1 = RPAD(l_str,20);
  9    dbms_output.put_line( l_row.col1 );
 10* end;
SQL> /
abc

PL/SQL procedure successfully completed.

The right answer, though, is almost always to avoid using the CHAR data type.