CREATE TABLE TAB_ARRANGEMENT
(
  FLOOR             NUMBER,
  PERSONS_IN_ORDER  VARCHAR2(200 BYTE)
);
/
SET DEFINE OFF;
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (1, 'KRISHNA,GOPAL,ARJUN,RAKHI,NISHA,,,,,,DEBA');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (2, 'SAKTHI,RAKESH,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (3, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (4, 'ARUN,PRADEEP,ABHI,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (5, 'RAGHAV,KARTIK,SAMULE,TINTIN,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (6, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (7, ',,,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (8, 'RAKESH.G,,,,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (9, 'GEETHA,PRIYA,NEHA,HEMA,,,,,,');
Insert into TAB_ARRANGEMENT
   (FLOOR, PERSONS_IN_ORDER)
 Values
   (10, 'PRASAD,,,,,,,,,');
COMMIT;

SQL> select * from TAB_ARRANGEMENT order by floor;
     FLOOR PERSONS_IN_ORDER
---------- --------------------------------------------------
         1 KRISHNA,GOPAL,ARJUN,RAKHI,NISHA,,,,,,DEBA
         2 SAKTHI,RAKESH,,,,,,,,
         3 ,,,,,,,,,,
         4 ARUN,PRADEEP,ABHI,,,,,,,,
         5 RAGHAV,KARTIK,SAMULE,TINTIN,,,,,,
         6 ,,,,,,,,,,
         7 ,,,,,,,,,,
         8 RAKESH.G,,,,,,,,,
         9 GEETHA,PRIYA,NEHA,HEMA,,,,,,
        10 PRASAD,,,,,,,,,

10 rows selected.

SQL> col seat_number format a11
SQL> col statement format a40
SQL> with
  2    data as (
  3      select floor, column_value,
  4             '4B'||to_char(floor)||'W'||to_char(column_value) seat_number,
  5             substr(PERSONS_IN_ORDER,
  6                    instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value),
  7                    instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value+1)
  8                    - instr(','||PERSONS_IN_ORDER||',', ',', 1, column_value)-1)
  9               person
 10      from TAB_ARRANGEMENT,
 11           table(cast(multiset(select level from dual
 12                               connect by level <= regexp_count(PERSONS_IN_ORDER,',')+1)
 13                      as sys.odciNumberList))
 14    )
 15  select floor, seat_number,
 16         'On floor '||to_char(floor)||' '||
 17         nvl2(person,
 18              initcap(person)||' sit''s at '||seat_number||'.',
 19              seat_number||' is empty.')
 20           statement
 21  from data
 22  order by floor, column_value
 23  /
     FLOOR SEAT_NUMBER STATEMENT
---------- ----------- ----------------------------------------
         1 4B1W1       On floor 1 Krishna sit's at 4B1W1.
         1 4B1W2       On floor 1 Gopal sit's at 4B1W2.
         1 4B1W3       On floor 1 Arjun sit's at 4B1W3.
         1 4B1W4       On floor 1 Rakhi sit's at 4B1W4.
         1 4B1W5       On floor 1 Nisha sit's at 4B1W5.
         1 4B1W6       On floor 1 4B1W6 is empty.
         1 4B1W7       On floor 1 4B1W7 is empty.
         1 4B1W8       On floor 1 4B1W8 is empty.
         1 4B1W9       On floor 1 4B1W9 is empty.
         1 4B1W10      On floor 1 4B1W10 is empty.
         1 4B1W11      On floor 1 Deba sit's at 4B1W11.
         2 4B2W1       On floor 2 Sakthi sit's at 4B2W1.
         2 4B2W2       On floor 2 Rakesh sit's at 4B2W2.
         2 4B2W3       On floor 2 4B2W3 is empty.
         2 4B2W4       On floor 2 4B2W4 is empty.
         2 4B2W5       On floor 2 4B2W5 is empty.
         2 4B2W6       On floor 2 4B2W6 is empty.
         2 4B2W7       On floor 2 4B2W7 is empty.
         2 4B2W8       On floor 2 4B2W8 is empty.
         2 4B2W9       On floor 2 4B2W9 is empty.
         2 4B2W10      On floor 2 4B2W10 is empty.
         3 4B3W1       On floor 3 4B3W1 is empty.
         3 4B3W2       On floor 3 4B3W2 is empty.
         3 4B3W3       On floor 3 4B3W3 is empty.
         3 4B3W4       On floor 3 4B3W4 is empty.
         3 4B3W5       On floor 3 4B3W5 is empty.
         3 4B3W6       On floor 3 4B3W6 is empty.
         3 4B3W7       On floor 3 4B3W7 is empty.
         3 4B3W8       On floor 3 4B3W8 is empty.
         3 4B3W9       On floor 3 4B3W9 is empty.
         3 4B3W10      On floor 3 4B3W10 is empty.
         3 4B3W11      On floor 3 4B3W11 is empty.
         4 4B4W1       On floor 4 Arun sit's at 4B4W1.
         4 4B4W2       On floor 4 Pradeep sit's at 4B4W2.
         4 4B4W3       On floor 4 Abhi sit's at 4B4W3.
         4 4B4W4       On floor 4 4B4W4 is empty.
         4 4B4W5       On floor 4 4B4W5 is empty.
         4 4B4W6       On floor 4 4B4W6 is empty.
         4 4B4W7       On floor 4 4B4W7 is empty.
         4 4B4W8       On floor 4 4B4W8 is empty.
         4 4B4W9       On floor 4 4B4W9 is empty.
         4 4B4W10      On floor 4 4B4W10 is empty.
         4 4B4W11      On floor 4 4B4W11 is empty.
         5 4B5W1       On floor 5 Raghav sit's at 4B5W1.
         5 4B5W2       On floor 5 Kartik sit's at 4B5W2.
         5 4B5W3       On floor 5 Samule sit's at 4B5W3.
         5 4B5W4       On floor 5 Tintin sit's at 4B5W4.
         5 4B5W5       On floor 5 4B5W5 is empty.
         5 4B5W6       On floor 5 4B5W6 is empty.
         5 4B5W7       On floor 5 4B5W7 is empty.
         5 4B5W8       On floor 5 4B5W8 is empty.
         5 4B5W9       On floor 5 4B5W9 is empty.
         5 4B5W10      On floor 5 4B5W10 is empty.
         6 4B6W1       On floor 6 4B6W1 is empty.
         6 4B6W2       On floor 6 4B6W2 is empty.
         6 4B6W3       On floor 6 4B6W3 is empty.
         6 4B6W4       On floor 6 4B6W4 is empty.
         6 4B6W5       On floor 6 4B6W5 is empty.
         6 4B6W6       On floor 6 4B6W6 is empty.
         6 4B6W7       On floor 6 4B6W7 is empty.
         6 4B6W8       On floor 6 4B6W8 is empty.
         6 4B6W9       On floor 6 4B6W9 is empty.
         6 4B6W10      On floor 6 4B6W10 is empty.
         6 4B6W11      On floor 6 4B6W11 is empty.
         7 4B7W1       On floor 7 4B7W1 is empty.
         7 4B7W2       On floor 7 4B7W2 is empty.
         7 4B7W3       On floor 7 4B7W3 is empty.
         7 4B7W4       On floor 7 4B7W4 is empty.
         7 4B7W5       On floor 7 4B7W5 is empty.
         7 4B7W6       On floor 7 4B7W6 is empty.
         7 4B7W7       On floor 7 4B7W7 is empty.
         7 4B7W8       On floor 7 4B7W8 is empty.
         7 4B7W9       On floor 7 4B7W9 is empty.
         7 4B7W10      On floor 7 4B7W10 is empty.
         7 4B7W11      On floor 7 4B7W11 is empty.
         8 4B8W1       On floor 8 Rakesh.G sit's at 4B8W1.
         8 4B8W2       On floor 8 4B8W2 is empty.
         8 4B8W3       On floor 8 4B8W3 is empty.
         8 4B8W4       On floor 8 4B8W4 is empty.
         8 4B8W5       On floor 8 4B8W5 is empty.
         8 4B8W6       On floor 8 4B8W6 is empty.
         8 4B8W7       On floor 8 4B8W7 is empty.
         8 4B8W8       On floor 8 4B8W8 is empty.
         8 4B8W9       On floor 8 4B8W9 is empty.
         8 4B8W10      On floor 8 4B8W10 is empty.
         9 4B9W1       On floor 9 Geetha sit's at 4B9W1.
         9 4B9W2       On floor 9 Priya sit's at 4B9W2.
         9 4B9W3       On floor 9 Neha sit's at 4B9W3.
         9 4B9W4       On floor 9 Hema sit's at 4B9W4.
         9 4B9W5       On floor 9 4B9W5 is empty.
         9 4B9W6       On floor 9 4B9W6 is empty.
         9 4B9W7       On floor 9 4B9W7 is empty.
         9 4B9W8       On floor 9 4B9W8 is empty.
         9 4B9W9       On floor 9 4B9W9 is empty.
         9 4B9W10      On floor 9 4B9W10 is empty.
        10 4B10W1      On floor 10 Prasad sit's at 4B10W1.
        10 4B10W2      On floor 10 4B10W2 is empty.
        10 4B10W3      On floor 10 4B10W3 is empty.
        10 4B10W4      On floor 10 4B10W4 is empty.
        10 4B10W5      On floor 10 4B10W5 is empty.
        10 4B10W6      On floor 10 4B10W6 is empty.
        10 4B10W7      On floor 10 4B10W7 is empty.
        10 4B10W8      On floor 10 4B10W8 is empty.
        10 4B10W9      On floor 10 4B10W9 is empty.
        10 4B10W10     On floor 10 4B10W10 is empty.

105 rows selected.

SELECT Floor_number,Seat_Number,
  (CASE
    WHEN person IS NOT NULL
    THEN 'On Floor '||floor_number||' '||person||' sit''s at '||Seat_Number
    ELSE 'On Floor '||floor_number||' '||Seat_Number||' is empty' END) STATEMENT
FROM
  (SELECT floor_number, Person, PO, ('4B'||floor_number||'W'||po) Seat_Number
  FROM
    (WITH CTE AS   (SELECT floor  AS floor_number, REPLACE (PERSONS_IN_ORDER,',',' , ') AS Person FROM TAB_ARRANGEMENT)
  SELECT TRIM(REGEXP_SUBSTR(Person, '[^,]+', 1, level)) AS Person,floor_number,level PO,DBMS_RANDOM.VALUE RV
  FROM CTE  CONNECT BY level <= REGEXP_COUNT(Person, '[^,]+')
  AND PRIOR floor_number       = floor_number
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL));