Querying oracle clob column

Yes, it's not allowed (this restriction does not affect CLOBs comparison in PL/SQL)to use comparison operators like =, !=, <> and so on in SQL statements, when tryingto compare two CLOB columns or CLOB column and a character literal, like you do. To beable to do such comparison in SQL statements, dbms_lob.compare() function can be used.

  select *     from aTable    where dbms_lob.compare(aClobColumn, 'value') = 0

In the above query, the 'value' literal will be implicitly converted to the CLOB data type. To avoid implicit conversion, the 'value' literal can be explicitly converted to the CLOBdata type using TO_CLOB() function and then pass in to the compare() function:

  select *     from aTable    where dbms_lob.compare(aClobColumn, to_clob('value')) = 0

how about

select * from table_name where to_char(clob_column) ="test_string"

Clob's are large datatypes that can store massive data and hence many operators that support varchar operations will not work on Clob, but in PL/SQL some of them do like mentioned here: http://docs.oracle.com/cd/B19306_01/appdev.102/b14249/adlob_sql_semantics.htm#g1016221

As you can see in the table Like is supported in both Sql and pl/sql for clobs, but = is not supported in SQL, but is in pl/sql

If you really need to you could convert to varchar in sql and compare like Tom Kyte mentions here like this: http://sqlfiddle.com/#!4/1878f6/1

select * from aTable where dbms_lob.substr( aClobColumn , length(aClobColumn), 1 )='value';