Oracle 21c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
ALTER SESSION SET NLS_DATE_FORMAT = 'YYYY-MM-DD HH24:MI:SS';
CREATE TYPE string_list IS TABLE OF VARCHAR2(20);
CREATE FUNCTION getFlightSeats (
  v_seatRange IN VARCHAR2,
  v_maxseat   IN VARCHAR2 DEFAULT 'D'
) RETURN string_list PIPELINED
IS
  spos PLS_INTEGER := 1;
  dpos PLS_INTEGER;
  epos PLS_INTEGER;
  lseat VARCHAR2(20);
  useat VARCHAR2(20);
  lx PLS_INTEGER;
  ly PLS_INTEGER;
  ux PLS_INTEGER;
  uy PLS_INTEGER;
BEGIN
  IF v_seatRange IS NULL THEN
    RETURN;
  END IF;
  LOOP
    epos := INSTR(v_seatrange, ',', spos);
    dpos := INSTR(v_seatrange, '-', spos);
    IF epos > spos AND (dpos = 0 OR dpos > epos) THEN
      PIPE ROW (SUBSTR(v_seatrange, spos, epos - spos));
    ELSIF epos = 0 AND dpos = 0 THEN
      PIPE ROW (SUBSTR(v_seatrange, spos));
    ELSE
      lseat := SUBSTR(v_seatrange, spos, dpos - spos);
      lx := TO_NUMBER(SUBSTR(lseat, 1, LENGTH(lseat) - 1));
      ly := ASCII(SUBSTR(lseat, -1, 1));
      IF epos > 0 THEN
        useat := SUBSTR(v_seatrange, dpos + 1, epos - dpos - 1);
      ELSE
        useat := SUBSTR(v_seatrange, dpos + 1);
      END IF;
      ux := TO_NUMBER(SUBSTR(useat, 1, LENGTH(useat) - 1));
      uy := ASCII(SUBSTR(useat, -1, 1));


      IF lx < ux THEN
        FOR y IN ly .. ASCII(v_maxseat) LOOP
          PIPE ROW (lx || CHR(y));
        END LOOP;
        FOR x IN lx + 1 .. ux - 1 LOOP
          FOR y IN ASCII('A') .. ASCII(v_maxseat) LOOP
            PIPE ROW (x || CHR(y));
          END LOOP;
        END LOOP;
        FOR y IN ASCII('A') .. uy LOOP
          PIPE ROW (ux || CHR(y));
        END LOOP;
      ELSE
        FOR y IN ly .. uy LOOP
          PIPE ROW (lx || CHR(y));
        END LOOP;
      END IF;
    END IF;
    
    EXIT WHEN epos = 0;
    spos := epos + 1;
  END LOOP;
END;
/
CREATE TABLE seat_table (seat_number) AS
SELECT x || y
FROM   (SELECT LEVEL AS x FROM DUAL CONNECT BY LEVEL <= 20)
       CROSS JOIN
       (SELECT CHR(64 + LEVEL) AS y FROM DUAL CONNECT BY LEVEL <= 4)
80 rows affected

SELECT seat_number
FROM   SEAT_TABLE
WHERE  seat_number MEMBER OF getFlightSeats(
                               v_seatRange => '3A,5B,6B-6D,9C-11B',
                               v_maxseat   => 'D'
                             );


SEAT_NUMBER


3A

5B

6B

6C

6D

9C

9D

10A

10B

10C

10D

11A

11B