Вопрос по plsqldeveloper, plsql, oracle – Чтение clob построчно с pl \ sql

1

В моем проекте я использую оракул в качестве основной базы данных и столкнулся с проблемой разбора clob. Итак, предположим, у нас есть сгусток со значением

   aaaaaa
   cccccc
   bbbbbb

И он хранится в табличном тесте ...

Мне нужно написать процедуру plsql, чтобы получить этот сгусток и разделить его так, чтобы у меня был массив с тремя элементами [aaaaaa, cccccccc, bbbbbbb].

Есть ли возможные решения?

Посмотрите документацию Oracle дляDBMS_LOB package, Иди к нему, и удачи. Bob Jarvis
И что случилось? Вы получаете ошибку? Неправильные результаты? (Пожалуйста, отправляйте код и результаты как изменения к вопросу, а не как комментарии). Я предлагаю вам выработать логику сVARCHAR2 столбец, затем посмотрите наDBMS_LOB эквиваленты, чтобы иметь дело с CLOB вместо этого. Похоже, это будет почти работать дляVARCHAR2, еслиnStartIndex инициализируется в1 а такжеnEndIndex является0 или выше, но последний параметрsubstr неправильно, и это не покажет последнюю строку, я думаю. Alex Poole
я пробовал этот кодFOR i IN (SELECT * from test ) LOOP LOOP EXIT WHEN nEndIndex<1; nEndIndex := INSTR(i.value, CHR(10),nStartIndex); vLine := SUBSTR(i.incass, nStartIndex, nEndIndex); DBMS_OUTPUT.put_line(vLine); nStartIndex := nEndIndex + 1; nLineIndex:=nLineIndex+1; END LOOP; END LOOP; ilya.stmn
Что вы пробовали? Вы смотрели наDBMS_LOB пакет? Если вы можете сделать это сVARCHAR2 строку, вы можете адаптировать этот подход с соответствующими операциями LOB (например, INSTR, SUBSTR). Alex Poole

Ваш Ответ

8   ответов
0

Я создал таблицу под названиемlixo_mq:

CREATE TABLE LIXO_MQ (CAMPO1 VARCHAR2(4000))

Я скопировалprintout Порядок и поменял его на работу другую:

PROCEDURE PRINTOUT (P_CLOB IN OUT NOCOPY CLOB) IS
   V_APARTIR                     NUMBER (20);
   V_CONTAR                      NUMBER (20);
   V_LINHA                       VARCHAR2 (4000);
   V_REG                         NUMBER (20);
   V_CORINGA                     VARCHAR2 (10) := CHR (10);
   V_ERRO                        VARCHAR2 (4000);
BEGIN
   IF (DBMS_LOB.ISOPEN (P_CLOB) != 1) THEN
      DBMS_LOB.OPEN (P_CLOB, 0);
   END IF;

   V_APARTIR                  := 1;
   V_REG                      := 1;

   WHILE DBMS_LOB.INSTR (LOB_LOC                       => P_CLOB
                        ,PATTERN                       => V_CORINGA
                        ,OFFSET                        => 1
                        ,NTH                           => V_REG
                        ) > 0
   LOOP
      V_CONTAR                   :=
                 DBMS_LOB.INSTR (LOB_LOC                       => P_CLOB
                                ,PATTERN                       => V_CORINGA
                                ,OFFSET                        => 1
                                ,NTH                           => V_REG
                                )
               - V_APARTIR;

      IF V_APARTIR > 1 THEN
         V_LINHA                    :=
                         DBMS_LOB.SUBSTR (LOB_LOC                       => P_CLOB
                                         ,AMOUNT                        =>   V_CONTAR
                                                                           - 1
                                         ,OFFSET                        =>   V_APARTIR
                                                                           + 1
                                         );
      ELSE
         V_LINHA                    :=
                                 DBMS_LOB.SUBSTR (LOB_LOC                       => P_CLOB
                                                 ,AMOUNT                        => V_CONTAR
                                                 ,OFFSET                        => V_APARTIR
                                                 );
      END IF;

      INSERT INTO LIXO_MQ
                  (CAMPO1
                  )
           VALUES (   V_REG
                   || ':'
                   || V_LINHA
                  );

      COMMIT;
      V_APARTIR                  :=
                           DBMS_LOB.INSTR (LOB_LOC                       => P_CLOB
                                          ,PATTERN                       => V_CORINGA
                                          ,OFFSET                        => 1
                                          ,NTH                           => V_REG
                                          );
      V_REG                      :=   V_REG
                                    + 1;
   END LOOP;

   IF (DBMS_LOB.ISOPEN (P_CLOB) = 1) THEN
      DBMS_LOB.CLOSE (P_CLOB);
   END IF;
EXCEPTION
   WHEN OTHERS THEN
      V_ERRO                     :=    'Error : '
                                    || SQLERRM;

      INSERT INTO LIXO_MQ
                  (CAMPO1
                  )
           VALUES (V_ERRO
                  );

      COMMIT;
END PRINTOUT;
-1
    declare
    c_clob clob := empty_clob();
    c_offset number;
    c_len number;
    read_cnt number;
    prev_buf number := 0;
    read_str varchar2(32000);
    BEGIN
   -- Read the clob in to the local variable
        select c into c_clob from test;
        c_offset := 1;
   -- Get the length of the clob
        c_len := dbms_lob.getlength(c_clob);
   -- Read till the current offset is less the length of clob
    while(c_offset <= c_len)
        loop
   -- Get the index of the next new line character
           read_cnt := instr(c_clob, CHR(10), c_offset, 1);
           exit when read_cnt = 0;
   -- Read the clob in the index
           read_str := dbms_lob.substr(c_clob, read_cnt-c_offset, c_offset);                                          
           dbms_output.put_line('Line#' || read_str);
   -- Now the current offset should point after the read line
           c_offset := read_cnt+1;
           end loop;
        END;
    /
Можете ли вы объяснить, что делает ваш код? Вы можете отредактировать свой ответ, чтобы добавить эту информацию.
3

В случае... - у вас установлен APEX - а сабб меньше 32К Вы также можете посмотреть следующий код:

declare
  l_text varchar2(32767) := '...';
  l_rows wwv_flow_global.vc_arr2;
begin
  l_rows := apex_util.string_to_table(l_text, chr(10));
  for i in 1 .. l_rows.count loop
    dbms_output.put_line(l_rows(i));
  end loop;
end;
/
1

sample for dynamicly length of rows

and alternative for UNIX and WIN files

and CR/LF on end of file or without it

Create table for TEST
drop table pbrev.test_SVT_tmp;
create table pbrev.test_SVT_tmp (xc clob);
insert into pbrev.test_SVT_tmp (xc) values (
--'azertyuiop;11' || chr(13) || chr(10) ||'qsdfghjklm;7878' || chr(13) || chr(10) ||'wxcvbn;0' || chr(13) || chr(10) );
'azertyuiop;11' || chr(13) || chr(10) ||'qsdfghjklm;7878' || chr(13) || chr(10) ||'wxcvbn;0' );
'azerty jhjh  huiop;11
qsdfgkj  hjklhhhhhhhhhhhm;7878
wxcvbn;0
dkjsk kjdsk5456 4654 5646 54645
FINISH'
);
delete from pbrev.test_SVT_tmp ;
select xc from pbrev.test_SVT_tmp;
--SET SERVEROUTPUT ON;
--SET SERVEROUTPUT OFF;
de,clare
    nStartIndex number := 1;
    nEndIndex number := 1;
    nLineIndex number := 0;
    vLine varchar2(2000);
    cursor c_clob is
    select xc from pbrev.test_SVT_tmp;
    c clob;
    procedure printout
       (p_clob in out nocopy clob) is
      offset number := 1;
      amount number := 32767;
      amount_last number := 0;
      len    number := dbms_lob.getlength(p_clob);
      lc_buffer varchar2(32767);
      line_seq pls_integer := 1;
      -- For UNIX type file - replace CHR(13) to NULL
      CR char := chr(13);
      --CR char := NULL;
      LF char := chr(10);      
      nCRLF number;
      sCRLF varchar2(2);
      b_finish boolean := true;
begin
      sCRLF := CR || LF;
      nCRLF := Length(sCRLF);
      if ( dbms_lob.isopen(p_clob) != 1 ) then
        dbms_lob.open(p_clob, 0);
      end if;
      amount := instr(p_clob, sCRLF, offset);
      while ( offset < len )
      loop
        -- For without CR/LF on end file
        If amount < 0 then
          amount := len - offset + 1;
          b_finish := false;
        End If;
        dbms_lob.read(p_clob, amount, offset, lc_buffer);
        If b_finish then
          lc_buffer := SUBSTR(lc_buffer,1,Length(lc_buffer)-1);  
        End If;
        if (line_seq-1) > 0 then
          amount_last := amount_last + amount;
          offset := offset + amount; 
        else
          amount_last := amount;
          offset := amount + nCRLF;
        end if;
        amount := instr(p_clob, sCRLF, offset);
        amount := amount - amount_last;
        dbms_output.put_line('Line #'||line_seq||': '||lc_buffer);
        line_seq := line_seq + 1;
      end loop; 
      if ( dbms_lob.isopen(p_clob) = 1 ) then
        dbms_lob.close(p_clob);
      end if; 
    exception
      when others then
         dbms_output.put_line('Error : '||sqlerrm);
    end printout;
begin
    open c_clob;
    loop
       fetch c_clob into c;
       exit when c_clob%notfound;
       printout(c);
    end loop;
    close c_clob;
end;
0

Это последующий ответ, основанный на его ответе Пьера-Жиля Леваллуа. Поскольку я думаю, что в нем есть некоторые ошибки, я попытался их исправить.

Empty lines caused errors Newlines where still in the print output (might be desired) "amount :=" was not inside the while loop, i think this caused a big bug if any value was shorter than the first line in the CLOB

Я реализовал это как довольно быстрые и грязные исправления. Я уверен, что должны быть более элегантные решения ... Вот так. Пример таблицы:

create table test (c clob);

insert into test (c) values (
-- line 1 (empty)
chr(10)||'line 2'
||chr(10) -- line 3 (empty)
||chr(10)||'line 4'
||chr(10)||'line 5'
||chr(10)); -- line 6 (empty)

И измененный код:

set serveroutput on;
declare
    cursor c_clob is
    select c from test;

    c clob;
    -------------------------------
    procedure printout
       (p_clob in out nocopy clob) is
      offset number := 1;
      amount number := 32767;
      len    number := dbms_lob.getlength(p_clob);
      lc_buffer varchar2(32767);
      i pls_integer := 1;
    begin
      if ( dbms_lob.isopen(p_clob) != 1 ) then
        dbms_lob.open(p_clob, 0);
      end if;
      while ( offset < len )
          loop
            -- If no more newlines are found, read till end of CLOB
            if (instr(p_clob, chr(10), offset) = 0) then
                amount := len - offset + 1;
            else
                amount := instr(p_clob, chr(10), offset) - offset;
            end if;

            -- This is to catch empty lines, otherwise we get a NULL error
            if ( amount = 0 ) then
                lc_buffer := '';
            else
                dbms_lob.read(p_clob, amount, offset, lc_buffer);
            end if;
            dbms_output.put_line('Line #'||i||':'||lc_buffer);

            -- This is to catch a newline on the last line with 0 characters behind it
            i := i + 1;
            if (instr(p_clob, chr(10), offset) = len) then
                lc_buffer := '';
                dbms_output.put_line('Line #'||i||':'||lc_buffer);
            end if;

            offset := offset + amount + 1;
          end loop; 
     if ( dbms_lob.isopen(p_clob) = 1 ) then
        dbms_lob.close(p_clob);
      end if; 
    exception
      when others then
         dbms_output.put_line('Error : '||sqlerrm);
    end printout;
    ---------------------------
begin
    dbms_output.put_line('-----------');
    open c_clob;
    loop
       fetch c_clob into c;
       exit when c_clob%notfound;
       printout(c);
    end loop;
    close c_clob;
end;
13

Вот кусок кода, который работает. Я предлагаю вам использовать явные курсоры вместо неявных (FOR i IN (select ...)) для повышения производительности.

Сначала вот скрипт для создания тестового случая.

create table test (c clob);

insert into test (c) values (
'azertyuiop
qsdfghjklm
wxcvbn
');

Тогда вот скрипт для чтения строки за строкой Clob:

/* Formatted on 28/08/2012 14:16:52 (QP5 v5.115.810.9015) */
declare
    nStartIndex number := 1;
    nEndIndex number := 1;
    nLineIndex number := 0;
    vLine varchar2(2000);

    cursor c_clob is
    select c from test;

    c clob;
    -------------------------------
    procedure printout
       (p_clob in out nocopy clob) is
      offset number := 1;
      amount number := 32767;
      len    number := dbms_lob.getlength(p_clob);
      lc_buffer varchar2(32767);
      i pls_integer := 1;
    begin
      if ( dbms_lob.isopen(p_clob) != 1 ) then
        dbms_lob.open(p_clob, 0);
      end if;
      amount := instr(p_clob, chr(10), offset);
      while ( offset < len )
      loop
        dbms_lob.read(p_clob, amount, offset, lc_buffer);
        dbms_output.put_line('Line #'||i||':'||lc_buffer);
       offset := offset + amount;
       i := i + 1;
      end loop; 
          if ( dbms_lob.isopen(p_clob) = 1 ) then
        dbms_lob.close(p_clob);
      end if; 
    exception
      when others then
         dbms_output.put_line('Error : '||sqlerrm);
    end printout;
    ---------------------------
begin
    dbms_output.put_line('-----------');
    open c_clob;
    loop
       fetch c_clob into c;
       exit when c_clob%notfound;
       printout(c);
    end loop;
    close c_clob;
end;

& APOS; количество & APOS; переменная используется для определения конца строки. Будьте осторожны, в некоторых случаях конец строки - это CHR (10) || CHR (13) (CR + LF), а в некоторых других случаях это только CHR (10).

Я обнаружил еще несколько ошибок в этом коде и попытался их исправить. Проверьте мой ответ.
Я получаю некоторые очень странные результаты с этим, если первая строка короче, чем другие строки. Рассматривая его ... Пример тестовой таблицы: вставьте в test (c) значения («az» || chr (10) || «qsdfghjklm» || chr (10) || «wxcvbn» | chr ( 10));
@tonyf спасибо, это здорово.
@ Pierre-Gilles Levallois обновил ваш пример кода, чтобы убедиться, что вы ссылаетесь на правильный столбец (c) в своей тестовой таблице как часть вашего курсора c_clob. Ваше решение теперь работает нормально.
0

Эммануил ответ

Это элегантное решение, которое прекрасно работает с гвоздями, длина которых превышает 32767 символов, или строками, длина которых превышает 4 000 символов.

Стандартный запрос ANSI:

DECLARE
  v_tmp clob :='aaaa'||chr(10)||
               'bbb'||chr(10)||
               'ccccc';
BEGIN
  FOR rec IN (WITH clob_table(c) as (SELECT v_tmp c FROM DUAL),
                   recurse(text,line) as (SELECT regexp_substr(c, '.+', 1, 1) text,1 line
                                            FROM clob_table
                                           UNION ALL
                                          SELECT regexp_substr(c, '.+', 1, line+1),line+1
                                            FROM recurse r,clob_table
                                           WHERE line<regexp_count(c, '.+'))
            SELECT text,line FROM recurse) LOOP
    dbms_output.put_line(rec.text);
  END LOOP;
END;

Oracle Specific Query (оригинальный пост):

DECLARE
  v_tmp clob :='aaaa'||chr(10)||
               'bbb'||chr(10)||
               'ccccc';
BEGIN
  FOR rec IN (WITH clob_table(c) as (SELECT v_tmp c FROM DUAL)
            SELECT regexp_substr(c, '.+', 1, level) text,level line
             FROM clob_table
          CONNECT BY LEVEL <= regexp_count(c, '.+')) LOOP
      dbms_output.put_line(rec.text);
  END LOOP;
END;
Размещено как вики сообщества, так как соответствующий ответ - Эммануил.
5

Хотя подход SQL regexp / connect by level является, вероятно, наиболее элегантным, он довольно плох с точки зрения производительности (для моего тестового примера на 11.2.0.3.0). Гораздо быстрее такой простой анализ, как этот.

procedure parse_clob(p_clob in clob) is
l_offset pls_integer:=1;
l_line varchar2(32767);
l_total_length pls_integer:=length(p_clob);
l_line_length pls_integer;
begin
  while l_offset<=l_total_length loop
    l_line_length:=instr(p_clob,chr(10),l_offset)-l_offset;
    if l_line_length<0 then
      l_line_length:=l_total_length+1-l_offset;
    end if;
    l_line:=substr(p_clob,l_offset,l_line_length);
    dbms_output.put_line(l_line); --do line processing
    l_offset:=l_offset+l_line_length+1;
  end loop;
end parse_clob;
Это точно и быстро. Должен быть принятый ответ.

Похожие вопросы