Oracle 18c

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table INVOICES (INVOICE clob);
insert into INVOICES
select '123456 (LED TV); 234543 (LED light); 654876 (LED monitor);' from dual union all
select '234567 (CAMERA); 965487 (LENS); 894513 (CAMERA stand);'    from dual;
2 rows affected

create table INVOICE_CATEGORIZED (INVOICE_NUMBER number, INVOICE_NAME varchar2(100));
insert into INVOICE_CATEGORIZED values (123456, 'LED TV');
1 rows affected

SELECT * FROM INVOICE_CATEGORIZED


INVOICE_NUMBER
INVOICE_NAME


123456
LED TV


INSERT INTO INVOICE_CATEGORIZED 
SELECT TAB.INVOICE_NUMBER, TAB.INVOICE_NAME FROM
(SELECT 
TRIM(dbms_lob.SUBSTR(INVOICE_INN,6 ,1)) AS INVOICE_NUMBER, 

SUBSTR(INVOICE_INN, 
INSTR(INVOICE_INN, '(') + 1,
INSTR(INVOICE_INN, ')') - INSTR(INVOICE_INN, '(') - 1 )
 AS INVOICE_NAME

-- HERE INVOICE_INN IS STRING NOW, SO WE CAN DO STRING OPERATIONS ON IT ONWARD

FROM
(
-- DIVIDING ; SEPARATED STRING TO INDIVIDUAL STRING
SELECT
    TRIM(CASE WHEN INVOICE_SINGLE.COLUMN_VALUE = 1 THEN
    dbms_lob.SUBSTR(INVOICE, 
    dbms_lob.INSTR(INVOICE,';',1,INVOICE_SINGLE.COLUMN_VALUE) - 1,
    1 
    )
    ELSE
    dbms_lob.SUBSTR(INVOICE, 
    dbms_lob.INSTR(INVOICE,';',1,INVOICE_SINGLE.COLUMN_VALUE) - 1
    - dbms_lob.INSTR(INVOICE,';',1,INVOICE_SINGLE.COLUMN_VALUE - 1),
    dbms_lob.INSTR(INVOICE,';',1,INVOICE_SINGLE.COLUMN_VALUE - 1) + 1)
    END) AS INVOICE_INN
FROM
    INVOICES T,
    TABLE ( CAST(MULTISET(
        SELECT
            LEVEL
        FROM
            DUAL
        CONNECT BY
            dbms_lob.INSTR(INVOICE,';',1,LEVEL) <> 0
    ) AS SYS.ODCINUMBERLIST) ) INVOICE_SINGLE)) TAB 
     WHERE NOT EXISTS (SELECT 1 FROM INVOICE_CATEGORIZED IC
    WHERE IC.INVOICE_NUMBER  = TAB.INVOICE_NUMBER
    AND IC.INVOICE_NAME = TAB.INVOICE_NAME)
5 rows affected

SELECT * FROM INVOICE_CATEGORIZED


INVOICE_NUMBER
INVOICE_NAME


123456
LED TV

234543
LED light

654876
LED monitor

234567
CAMERA

965487
LENS

894513
CAMERA stand
INVOICE_NUMBER	INVOICE_NAME
123456	LED TV
234543	LED light
654876	LED monitor
234567	CAMERA
965487	LENS
894513	CAMERA stand