I have been trying to store the key value pair which is like:

 TaskType: SRC_Copay,
 [BILLING_ACCOUNT_NUMBER]: 3124332,
 [TOTAL_DESKTOP_COST]: 11423.1,
 [TOTAL_WALKOUT_COST]: 5477.93,
 STATUS: New Sundry Request,

Requestor:

I want this to be saved into a table as:

Like: key in separate column as key and value in separate column as data.

How can I do this? Please help

CodePudding user response：

You are almost succeeding, why stop here?

with tab1 as (
select ' TaskType: SRC_Copay,
 [BILLING_ACCOUNT_NUMBER]: 3124332,
 [TOTAL_DESKTOP_COST]: 11423.1,
 [TOTAL_WALKOUT_COST]: 5477.93,
 STATUS: New Sundry Request,' str
  from dual
)
select regexp_substr(regexp_substr(t1.str, '[^,] ', 1, level), '[^:] ', 1, 1),
       regexp_substr(regexp_substr(t1.str, '[^,] ', 1, level), '[^:] ', 1, 2)
  from tab1 t1
connect by level < regexp_count(t1.str, ',')   1

CodePudding user response：

A solution using PLSQL that solves what you are looking for.

• A type to store the record
• A type to store the table
• A pipelined table function to recover the rows split by key value

So the code for the demo

SQL> create table t1 ( c1 clob ) ;

Table created.

SQL> insert into t1
with tab1 as (
select ' TaskType: SRC_Copay,
 [BILLING_ACCOUNT_NUMBER]: 3124332,
 [TOTAL_DESKTOP_COST]: 11423.1,
 [TOTAL_WALKOUT_COST]: 5477.93,
 STATUS: New Sundry Request,' str
  from dual
 ) select * from tab1 ;  

1 row created.

SQL> desc t1
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 C1                                                 CLOB

Now, we create the types and the pipelined function

SQL> CREATE OR REPLACE TYPE t_tf_row AS OBJECT ( key varchar2(100), value varchar2(100) );
/  2

Type created.

SQL> CREATE OR REPLACE TYPE t_tf_tab IS TABLE OF t_tf_row;
/  2

Type created.

SQL> create or replace function split_clob RETURN t_tf_tab PIPELINED 
as
begin
    for h in 
    (
    select regexp_substr(regexp_substr(to_char(t1.c1), '[^,] ', 1, level), '[^:] ', 1, 1) key,
           regexp_substr(regexp_substr(to_char(t1.c1), '[^,] ', 1, level), '[^:] ', 1, 2) value
    from t1
    connect by level < regexp_count(t1.c1, ',')   1
    and prior rowid = rowid and prior sys_guid() is not null
    )
    loop
        PIPE ROW(t_tf_row(h.key,h.value));   
    end loop;
end;
/

Function created.

Let's query the pipelined function

SQL> set lines 200 pages 0
SQL> col key for a40
SQL> select key from table(split_clob())
 TaskType
 [BILLING_ACCOUNT_NUMBER]
 [TOTAL_DESKTOP_COST]
 [TOTAL_WALKOUT_COST]
 STATUS

SQL> select value from table(split_clob());
 SRC_Copay
 3124332
 11423.1
 5477.93
 New Sundry Request