Oracle related to count(*) syntax alternate options
One option would be to try the first query and catch the no_data_found exception

begin
  SELECT rate 
    into StdDefaultValue 
    FROM RateTable1L 
   where wkstn_oid_sh = p_pf_wkstn_oid_sh 
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    SELECT rate 
      into StdDefaultValue 
      FROM RateTable1 
     where wkstn_oid_sh = p_pf_wkstn_oid_sh 
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
       and exact ='STANDARD DEFAULT';
end;


or iterate through the tables

begin
  SELECT rate 
    into StdDefaultValue 
    FROM RateTable1L 
   where wkstn_oid_sh = p_pf_wkstn_oid_sh 
     AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
     and exact ='STANDARD DEFAULT';
exception
  when no_data_found
  then
    StdDefaultValue := null;
end;

if( StdDefaultValue is null )
then
  begin
    SELECT rate 
      into StdDefaultValue 
      FROM RateTable1
     where wkstn_oid_sh = p_pf_wkstn_oid_sh 
       AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
       and exact ='STANDARD DEFAULT';
  exception
    when no_data_found
    then
      StdDefaultValue := null;
  end;
end if;


If it is more likely that the value will be found in one of the tables, it would make sense to check that table first.  Depending on how many tables you have to check and how many times this is getting called, exception handling may not be the most efficient approach.  You might be better off with a single UNION ALL query assuming that the value will appear in at most one table.

  SELECT rate 
    into StdDefaultValue 
    FROM (SELECT rate 
            FROM RateTable1L 
           where wkstn_oid_sh = p_pf_wkstn_oid_sh 
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
             and exact ='STANDARD DEFAULT'
          UNION ALL
          SELECT rate 
            FROM RateTable1
           where wkstn_oid_sh = p_pf_wkstn_oid_sh 
             AND wkstn_oid_lng = p_pf_wkstn_oid_lng  
             and exact ='STANDARD DEFAULT')


From a data model standpoint, though, having 8 separate tables with the same basic structure and not knowing which table you need to query seems problematic.  It would seem like the sort of thing that is either calling for a parent entity with a type attribute that stores data common to all 8 tables or for creating a single table with 8 partitions. Either approach would make writing code far easier and would make the code more efficient.