Substring reversa do valor com a posição

Você pode iniciar a pesquisa value -> <value>terminando com key -> Colourusing REGEXP_SUBSTRe, em seguida, substituir o ,key -> Colourusing REGEXP_REPLACE

select col1, 
REGEXP_REPLACE
(REGEXP_SUBSTR
(col1, 'value -> [^,]*, key -> Colour'), ', key -> Colour') AS new_val

from 
(SELECT '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
union all
SELECT '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
) tbl;

Violino

Se você quiser ser menos dependente da ordem dos pares "k -> v", você precisa ir mais longe na análise sintática com regexp:

with data(id, str) as (
    SELECT 1, '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' as col1 from dual
    union all
    SELECT 2, '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' as col1 from dual
    union all
    SELECT 3, '[{key -> Fruit, value -> Mango}, {key -> Colour, value -> Black}]' as col1 from dual
),
keyvalues as (
    select id, level as grp,
        regexp_replace(replace(regexp_substr(lst, '[^}]+', 1, level),'{',''), '^,\s+', '')
        as keyvalues
    from (
        select 
            id, regexp_replace(str, '\[(.*)\]', '\1') as lst
        from data 
    )
    connect by id = prior id and level <= regexp_count(lst, '}') and prior sys_guid() is not null
),
pairs as (
    select id, idx, grp, keyval,
        substr(keyval, 1, instr(keyval,' ->') - 1) as key, 
        substr(keyval, instr(keyval,' -> ') + length(' -> ')) as value 
    from (
        select id, level as idx,grp, trim( ' ' from regexp_substr(keyvalues, '[^,]+', 1, level)) as keyval
        from keyvalues
        connect by id = prior id and grp = prior grp and
            regexp_substr(keyvalues, '[^,]+', 1, level) is not null and prior sys_guid() is not null
    )
)
select v.id, v.keyval 
from pairs k
join pairs v on k.id = v.id and k.grp = v.grp and k.idx <> v.idx 
    and v.key = 'value' 
where k.value = 'Colour'
;

Você pode usar REGEXP_SUBSTRpara encontrar a correspondência:

SELECT REGEXP_SUBSTR(
         col1,
         '\{(key -> Colour, )?(value -> [^},]*?)(, key -> Colour)?\}',
         1,
         1,
         NULL,
         2
       ) AS colour
FROM   tbl

O que, para os dados da amostra:

CREATE TABLE tbl (col1) AS
SELECT EMPTY_CLOB() || '[{value -> Apple, key -> Fruit}, {value -> White, key -> Colour}]' from dual union all
SELECT EMPTY_CLOB() || '[{value -> Mango, key -> Fruit}, {value -> Black, key -> Colour}]' from dual;

Saídas:

Alternativamente, não tente codificar seu próprio formato de armazenamento. Se você for usar algo que tenha uma semelhança passageira com o formato JSON, então use o formato JSON em vez de uma aproximação.

UPDATE tbl
SET col1 = REGEXP_REPLACE(
             col1,
             '\{value -> (.*?), key -> (.*?)\}',
             '{"value": "\1", "key": "\2"}'
           )

Então você pode usar um analisador JSON adequado:

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$[*]'
         COLUMNS (
           key_name VARCHAR2(20) PATH '$.key',
           value    VARCHAR2(20) PATH '$.value'
         )
       ) j
WHERE  key_name = 'Colour'

Quais saídas:

ou:

SELECT j.*
FROM   tbl t
       CROSS APPLY JSON_TABLE(
         t.col1,
         '$'
         COLUMNS (
           fruit  VARCHAR2(20) PATH '$[*]?(@.key=="Fruit").value',
           colour VARCHAR2(20) PATH '$[*]?(@.key=="Colour").value'
         )
       ) j

Quais saídas:

violino