Split column value by semicolon into individual rows based on another column’s value

I need to generate rows based on the quantity value.

The barcode column will have value separate by semicolon “;” I want to turn each separated value into individual column.

If the barcode value is less than the quantity, other rows of barcode column will be null. The barcode value will not be more than the quantity.

| Name         | Quantity    | Barcode
+--------------+-------------+-----------------------------
| Apple        | 5           | barcode1;barcode2;barcode3

Expected Output:

| Name         | Barcode
+--------------+----------
| Apple        | barcode1
| Apple        | barcode2
| Apple        | barcode3
| Apple        | 
| Apple        | 

Answer

If you have more than one source row to split at once, a connect-by approach needs conditions to prevent cross matches; so you can do:

select name, regexp_substr(barcode, '(.*?)(;|$)', 1, level, null, 1)
from your_table
connect by name = prior name
and prior sys_guid() is not null
and level <= quantity;

As you are on 11g, and assuming that is 11gR2, You could also use a recursive CTE:

with rcte (name, pos, barcode, quantity, all_barcodes) as (
  select name, 1, regexp_substr(barcode, '(.*?)(;|$)', 1, 1, null, 1), quantity, barcode
  from your_table
  union all
  select name, pos + 1, regexp_substr(all_barcodes, '(.*?)(;|$)', 1, pos + 1, null, 1), quantity, all_barcodes
  from rcte
  where pos < quantity
)
select name, barcode
from rcte
order by name, pos;

db<>fiddle