Oracle Sys Refcurser в функции

Документацию Oracle для синтаксиса CREATE PROCEDURE можно найти здесь.

CREATE PROCEDURE не имеет предложения возврата (то есть для FUNCTIONs); вместо этого вы должны использовать параметр OUT и удалить оператор return в конце.

Вам также необходимо использовать OPEN cursor_name FOR для выполнения запроса SELECT и связать его с курсором.

Кроме того, вам не нужно : перед переменными, объявленными в заголовке процедуры. Синтаксис :name предназначен для привязки переменных в SQL — PL/SQL автоматически обрабатывает привязку переменных без этого синтаксиса.

(Кажется, вы не используете аргумент routeday, поэтому вы можете удалить его, если не собираетесь использовать его в будущем).

Что-то вроде этого:

create or replace procedure getRoute(
  in_route      IN  varchar,
  in_routeday   IN  varchar,
  out_cursor    OUT SYS_REFCURSOR
)
is
begin
    OPEN out_cursor FOR
    select DISTINCT BBT_JOURNEYSTOPS.SERVICE "Service",
                    BBT_JOURNEYSTOPS.STOP_REFERENCE "StopNo",
                    STOP_NAME "Near",
                    BBT_STOPS.ROAD_NAME "On",
                    BBT_JOURNEYSTOPS.JOURNEYTIME "Duration"    
    from BBT_JOURNEYSTOPS
         inner join BBT_WEEKLYSCHEDULE
         on BBT_WEEKLYSCHEDULE.SERVICE = BBT_JOURNEYSTOPS.SERVICE
         inner join BBT_STOPS
         on BBT_JOURNEYSTOPS.STOP_REFERENCE = BBT_STOPS.STOPREF
    where  (UPPER(BBT_JOURNEYSTOPS.SERVICE) LIKE UPPER('%'|| in_route || '%'))
    AND    (TO_NUMBER(SUBSTR(BBT_WEEKLYSCHEDULE.TIMEUNTIL, 1, 2)) > TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')))
    ORDER BY BBT_JOURNEYSTOPS.SERVICE, BBT_JOURNEYSTOPS.JOURNEYTIME;
end;
/

Вы также можете рассмотреть возможность использования строго типизированного курсора вместо слабого курсора.

Вы можете назвать это так:

DECLARE
  route VARCHAR2 := 'abc';
  routeday VARCHAR2 := 'Tuesday';
  rc SYS_REFCURSOR;
BEGIN
  getRoute(
    in_route    => route,
    in_routeday => routeday,
    out_cursor  => rc
  );
END;
/

Или, если вы хотите использовать переменные связывания (вне блока PL/SQL):

VARIABLE route VARCHAR2;
VARIABLE routeday VARCHAR2;
VARIABLE rc REFCURSOR;

BEGIN
  route := 'abc';
  routeday := 'Tuesday';
END;
/    

BEGIN
  getRoute(
    in_route    => :route,
    in_routeday => :routeday,
    out_cursor  => :rc
  );
END;
/

PRINT :rc;

Вы должны использовать OPEN FOR синтаксис и функцию вместо процедуры, а также : не требуется в основном коде для параметров

create or replace function getRoute(route IN varchar, routeday IN varchar)
  return sys_refcursor is
  v_rc sys_refcursor;
begin
    open v_rc  FOR
    select DISTINCT BBT_JOURNEYSTOPS.SERVICE "Service", BBT_JOURNEYSTOPS.STOP_REFERENCE "StopNo", 
  STOP_NAME "Near", BBT_STOPS.ROAD_NAME "On", BBT_JOURNEYSTOPS.JOURNEYTIME "Duration"    
    from BBT_JOURNEYSTOPS
    inner join BBT_WEEKLYSCHEDULE
    on BBT_WEEKLYSCHEDULE.SERVICE = BBT_JOURNEYSTOPS.SERVICE
    inner join BBT_STOPS
    on BBT_JOURNEYSTOPS.STOP_REFERENCE = BBT_STOPS.STOPREF
    where  (UPPER(BBT_JOURNEYSTOPS.SERVICE) LIKE UPPER('%'|| route || '%')) AND 
      (TO_NUMBER(SUBSTR(BBT_WEEKLYSCHEDULE.TIMEUNTIL, 1, 2)) > TO_NUMBER(TO_CHAR(SYSDATE, 'HH24')))
    ORDER BY BBT_JOURNEYSTOPS.SERVICE, BBT_JOURNEYSTOPS.JOURNEYTIME;


  return v_rc ;
end;

/

Также вы не можете использовать EXEC в коде pl\sql.

DECLARE
 rc refcursor;
BEGIN
  :rc := getRoute(:route, :routeday);
END;