Q:

How to calculate the number of occurrences of a character in a varchar in a single SQL?

Say there is a variable 
v_Source := 'stack#over#flo#w';

How to get the number of occurrences of '#' in it in a single SQL query?

A:

select length('stack#over#flo#w') - length(replace('stack#over#flo#w','#',null)) 
from dual;

From oracle 11 you can use REGEXP_COUNT
select REGEXP_COUNT('stack#over#flo#w', '#') from dual;

A:

SELECT REGEXP_COUNT( 'stack#over#flo#w', '#' )
FROM   DUAL

