Oracle 用CSV数据解析CLOB字段，并将内容放入适当的字段中

我的问题是最初发布在11/9/2015上的问题的变体。解析包含CSV数据的CLOB字段。

我加载到clob字段中的分隔符数据包含表单属性 = data中的输出〜clob字段最多可以包含60个属性 (下面的简短列表)，所有这些属性都需要分析以将数据提取到具有匹配属性字段的表中。这是clob数据的示例

pid = 29942 ~ uid = 0 ~ 旧auid = 4294967295 ~ auid = 0 ~ tty = (无) ~ 旧ses = 4294967295 ~ ses = 5020 ~ res = 1 ~

我的输出表将具有下面列出的所有属性。目标是将给定记录的CLOB字段提取到正确的输出表字段中。

我已经在linux shell脚本中完成了这个解析应用程序，但是性能非常慢。
任何可以提供的指导都值得赞赏。

问候，
罗伯特

pid =
arch =
cwd =
项目 =
Protitle =
uid =
名称 =
系统 =
auid =
old-auid =
inode =
per =
ses =
成功 =
tty =

像这样的事情应该会让你开始

SQL> create table T ( c clob );

Table created.

SQL>
SQL> declare
  2     v varchar2(32000) := 'pid=29942~uid=0~old-auid=4294967295~auid=0~tty=(none)~old-ses=4294967295~ses=5020~res=1~';
  3     ctemp clob;
  4   begin
  5     for i in 1 .. 8 loop
  6       v := v ||v;
  7     end loop;
  8
  9     dbms_lob.createtemporary(ctemp,true);
 10     ctemp := v;
 11
 12     for i in 1 .. 20 loop
 13       dbms_lob.writeappend(ctemp,length(v),v);
 14     end loop;
 15
 16     insert into T values (ctemp);
 17     commit;
 18   end;
 19   /

PL/SQL procedure successfully completed.

SQL>
SQL> select dbms_lob.getlength(c) from t;

DBMS_LOB.GETLENGTH(C)
---------------------
               473088

1 row selected.

SQL>
SQL>
SQL> drop table GTT;

Table dropped.

SQL>
SQL> create global temporary table GTT ( id number,
  2  c_pid varchar2(20),
  3  c_uid varchar2(20),
  4  c_old_auid varchar2(20),
  5  c_auid varchar2(20),
  6  c_tty varchar2(20),
  7  c_old_ses varchar2(20),
  8  c_ses varchar2(20),
  9  c_res varchar2(20)
 10  )
 11  on commit preserve rows;

Table created.

SQL>
SQL>
SQL>  set serverout on
SQL>  declare
  2     l_big_chunk varchar2(32767);
  3     l_leftover varchar2(200);
  4     l_offset pls_integer;
  5     l_len pls_integer;
  6
  7     type t_rows is table of gtt%rowtype index by pls_integer;
  8     l_rows t_rows;
  9
 10     l_delim pls_integer;
 11     l_eq    pls_integer;
 12     l_elem  varchar2(100);
 13     l_col_name varchar2(30);
 14     l_row_idx pls_integer := 0;
 15   begin
 16   for i in ( select rownum r, c from t ) loop
 17     l_offset := 1;
 18
 19     loop
 20       l_big_chunk := dbms_lob.substr(i.c,32700,l_offset);
 21       l_len := length(l_big_chunk);
 22       l_offset := l_offset + l_len;
 23       l_big_chunk := l_leftover || l_big_chunk;
 24
 25       dbms_application_info.set_client_info(i.r||'-'||l_offset);
 26
 27       loop
 28         l_delim := instr(l_big_chunk,'~');
 29         exit when l_delim = 0 or l_big_chunk is null;
 30
 31  --dbms_output.put_line('l_big_chunk='||l_big_chunk);
 32  --dbms_output.put_line('l_delim='||l_delim);
 33
 34         l_elem := substr(l_big_chunk,1,l_delim-1);
 35         l_eq := instr(l_elem,'=');
 36         l_col_name := substr(l_elem,1,l_eq-1);
 37
 38  --dbms_output.put_line('l_elem='||l_elem);
 39  --dbms_output.put_line('l_eq='||l_eq);
 40  --dbms_output.put_line('l_col_name='||l_col_name);
 41
 42         if l_col_name = 'pid' then
 43           l_row_idx := l_row_idx + 1;
 44           l_rows(l_row_idx).c_pid := substr(l_elem,l_eq+1);
 45         elsif l_col_name = 'uid' then
 46           l_rows(l_row_idx).c_uid := substr(l_elem,l_eq+1);
 47         elsif l_col_name = 'old-auid' then
 48           l_rows(l_row_idx).c_old_auid := substr(l_elem,l_eq+1);
 49         elsif l_col_name = 'auid' then
 50           l_rows(l_row_idx).c_auid := substr(l_elem,l_eq+1);
 51         elsif l_col_name = 'tty' then
 52           l_rows(l_row_idx).c_tty := substr(l_elem,l_eq+1);
 53         elsif l_col_name = 'old-ses' then
 54           l_rows(l_row_idx).c_old_ses := substr(l_elem,l_eq+1);
 55         elsif l_col_name = 'ses' then
 56           l_rows(l_row_idx).c_ses := substr(l_elem,l_eq+1);
 57         elsif l_col_name = 'res' then
 58           l_rows(l_row_idx).c_res := substr(l_elem,l_eq+1);
 59         end if;
 60
 61         l_big_chunk := substr(l_big_chunk,l_delim+1);
 62       end loop;
 63       l_leftover := l_big_chunk;
 64
 65       exit when l_len < 32700;
 66     end loop;
 67
 68   end loop;
 69     forall i in 1 .. l_rows.count
 70        insert into gtt values  l_rows(i) ;
 71
 72     dbms_output.put_line('rows = '||sql%rowcount);
 73     commit;
 74
 75   end;
 76   /
rows = 5376

PL/SQL procedure successfully completed.

SQL>
SQL> select count(*) from gtt;

  COUNT(*)
----------
      5376

1 row selected.

SQL>
SQL> select * from gtt where rownum <= 10;

        ID C_PID                C_UID                C_OLD_AUID           C_AUID               C_TTY                C_OLD_SES            C_SES                C_RES
---------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- --------------------
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1
           29942                0                    4294967295           0                    (none)               4294967295           5020                 1

10 rows selected.