CASE INSENSITIVE ON ORACLE 10G

sorry, you can't

Oracle Text would be as case insensitive as you can get.  That allows case insensitive searching, but does require some setup and maintenance.

yes,agreed, but even that is a full "database" level change.  Oracle Text would only apply to data stored within the context of that feature.

Yes.  I just wasn't sure what exactly they are looking for and wanted to put it out there as an option.  A lot of times people are looking for case insenstive searching.

As far as sql statements go, oracle is case insensitive, as long as you did not create the column names in mixed or lower case on purpose, which most people do not.

select test_col from test_tab;

yields the same results as

SELECT TEST_COL FROM TEST_TAB;

yes,  fpinheiro, johnsone brings up a good point.  Case sensitivity is relative to what you are doing.

Or maybe that's the point of your question.

Can you elaborate?

I need case-insensitive for the data in tables, not in name objects.

like this
in the select above
select name from table where name = 'Robert'

possible results are:
ROBERT
robert
Robert
RoBeRt

Then you would need Oracle Text.  A Oracle Text index would need to be created on each column that you wanted case insensitive searching on.

The only other way would be to create function based indexes on UPPER(<col>) and then in all you queries specify WHERE UPPER(<col>) = UPPER('Robert').

Its not exactly what I wanted, but is a good alternated solution.