SELECT REPLACE('ORACLE BOOK', 'O', 'E') str_replace 
  FROM DUAL;

STR_REPLACE
------------
ERACLE BEEK

SELECT REPLACE('ORACLE BOOK', 'O', 'EEE') str_replace
  FROM DUAL;

STR_REPLACE
-----------------
EEERACLE BEEEEEEK

SELECT REPLACE('ORACLE BOOK', 'OA', 'EEE') str_replace
  FROM DUAL;

STR_REPLACE
------------
ORACLE BOOK

SELECT REPLACE('ORACLE BOOK', 'O') str_replace
  FROM DUAL;

STR_REPLACE
------------
RACLE BK

SELECT REPLACE('ORACLE BOOK', 'MA', 'PIN') str_replace
   FROM DUAL;

STR_REPLACE
-------------
PINACLE BOOK

SELECT REPLACE('ORACLE BOOK', 'ORACLE', 'PLS') str_replace
  FROM DUAL;

STR_REPLACE
-------------
PLS BOOK

Q : How to get the number of O’s in the string ‘THE ULTIMATE CODEMAN’?

Ans:

SELECT 
    'THE ULTIMATE CODEMAN' Original,
    LENGTH('THE ULTIMATE CODEMAN') - LENGTH(REPLACE('THE ULTIMATE CODEMAN', 'O')) "No Of O's"
  FROM DUAL;

While operating with any character function, we should keep in mind that characters are case sensitive, else few queries will produce undesired output confusing in many situations.

Q : How to get the number of O’s in the string ‘Oracle Organization’?

Ans:

SELECT 'Oracle Organization' Original,
 LENGTH('Oracle Organization') - LENGTH(REPLACE('Oracle Organization', 'O')) "No Of O's"
  FROM DUAL;

ORIGINAL               No Of O's
--------------------   ---------
Oracle Organization            2

SELECT 'Oracle Organization' Original,
 LENGTH('Oracle Organization') - LENGTH(REPLACE(UPPER('Oracle Organization'), 'O')) "No Of O's"
 FROM DUAL;

ORIGINAL               No Of O's
--------------------   ---------
Oracle Organization            3