Oracle Sys Refcurser in a function

Question

I'm having some trouble returning a pointer to the query.

Here is my current code:

create or replace procedure getRoute(route IN varchar, routeday IN varchar)
  return sys_refcursor is
  v_rc sys_refcursor;
begin
    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 timetable;
end;
/

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

I get a Syntax error on the first line saying "Expected AUTHID, EXTERNAL" However I'm also not sure if the code i've written will execute correctly either. Any hints or tips would be greatly appreciated.

Answer 1

The Oracle Documentation for CREATE PROCEDURE syntax can be found here.

CREATE PROCEDURE does not have a return clause (that is for FUNCTIONs); you should use an OUT parameter instead and remove the return statement at the end.

You also need to use OPEN cursor_name FOR to execute the SELECT query and associate it with the cursor.

Also - you don't need : infront of variables declared in the procedure's header. The :name syntax is for bind variables in SQL - PL/SQL handles binding of variables automatically without that syntax.

(You don't seem to be using the routeday argument so you could remove it if you aren't going to use it in the future).

Something like this:

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;
/

You could also consider using a strongly typed cursor instead of a weak cursor.

You can call it like this:

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

Or if you do want to use bind variables (outside the PL/SQL block):

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;