La lecture clob ligne par ligne, avec les pl\sql

Dans mon projet j'utilise oracle comme base de données principale et j'ai été confronté à un problème avec l'analyse des clob. Supposons donc que nous avons un clob avec la valeur

   aaaaaa
   cccccc
   bbbbbb

Et il est stocké dans le test de la table ...

J'ai besoin d'écrire plsql procédure pour obtenir ce clob et le couper de sorte que je vais avoir un tableau avec les trois éléments [aaaaaa,cccccccc,bbbbbbb].

Est-il possible de trouver des solutions?

Qu'avez-vous essayé? Avez-vous regardé le DBMS_LOB le paquet? Si vous pouvez faire cela à un VARCHAR2 chaîne, vous pouvez adapter cette approche avec le MÉTIER de l'exploitation (e..g INSTR, SUBSTR).
Jetez un oeil à la documentation d'Oracle pour le package DBMS_LOB. Aller à elle, et bonne chance.
j'ai essayé ce code 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;
Et... ce qui s'est passé? Ne vous obtenez une erreur? Les mauvais résultats? (Veuillez poster le code et les résultats que les modifications apportées à la question, non pas que les commentaires). Je vous suggère de travailler sur la logique avec un VARCHAR2 colonne, puis regardez la DBMS_LOB équivalents de traiter avec un CLOB à la place. Ce qui ressemble, il sera presque travailler pour VARCHAR2, si nStartIndex est initialisé à 1 et nEndIndex est 0 ou plus, mais la dernière parm de substr est mal, et il n'affiche pas la dernière ligne, je pense.

InformationsquelleAutor ilya.stmn | 2012-07-25

Voici un morceau de code qui fonctionne.
Je vous suggère d'utiliser explicite plutôt les curseurs implicites (POUR i IN (select...)), pour l'exécution but.

D'abord voici le script de création de cas de test.

create table test (c clob);

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

Alors voici le script pour lire ligne par ligne 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;

quantité variable est utilisé pour détecter la fin de la position de la ligne. Être carfull, dans certains cas, la fin de ligne est CHR(10)||CHR(13) (CR + LF), et dans certains cas, il est seulement CHR(10).

Je suis certains très bizarre résultats avec cette option si la première ligne est plus courte que les autres lignes. La recherche en elle... Exemple de test de la table: insert into test (c) values ( 'az'||chr(10)|| 'qsdfghjklm'||chr(10)|| 'wxcvbn'||chr(10));
J'ai trouvé quelques bugs plus dans ce code, et j'ai essayé de la corriger. Découvrez ma réponse.
Levallois mise à jour de votre exemple de code pour s'assurer que vous une référence de la colonne correcte (c) au sein de votre table de test dans le cadre de votre curseur c_clob. Votre solution fonctionne maintenant très bien.

InformationsquelleAutor Pierre-Gilles Levallois

Tandis que le SQL regexp /se connecter par niveau d'approche est sans doute le plus élégant, il est assez mauvais performancewise (pour mon cas de test sur 11.2.0.3.0). Beaucoup plus rapide est simple à analyser comme cela.

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;

C'est précis et rapide. Doit être accepté de répondre.

InformationsquelleAutor Andy Haack

Au cas où...
- vous avez installé APEX
- et le clob est inférieure à 32K
vous pouvez également regarder dans le code suivant:

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;
/

InformationsquelleAutor Jürgen Sieben

échantillon pour dynamicly longueur de lignes

et alternative pour les systèmes UNIX et les fichiers WIN

et CR/LF sur la fin du fichier ou sans elle

Créer une table pour le 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;
declare
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;

InformationsquelleAutor Tomas Svizensky

C'est un suivi de la réponse repose sur @Pierre-Gilles Levallois sa réponse.
Car je pense qu'il contenait quelques bugs, j'ai essayé de les corriger.

Des lignes vides causés erreurs
Des sauts de ligne, où encore dans l'impression (peut être voulu)
"montant :=" n'était pas à l'intérieur de la boucle while, je pense que cela a causé un gros bug si une valeur a été plus courte que la première ligne dans le CLOB

J'ai mis en place ce qu'elle est plutôt rapide et sale de bugs. Je suis sûr qu'il doit être plus élégant des solutions... voilà. L'exemple de la table:

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)

Et la modification du code:

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;

InformationsquelleAutor Wouter

Emmanuel Répondre

C'est une solution élégante qui fonctionne très bien avec des clobs dépassant 32767 caractères ou de lignes dépassant 4K caractères.

Norme ANSI Requête:

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 de Requête Spécifique (message d'origine):

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;

Posté wiki de la communauté que la réponse est Emmanuel.

InformationsquelleAutor

J'ai créé une table appelée lixo_mq:

CREATE TABLE LIXO_MQ (CAMPO1 VARCHAR2(4000))

J'ai copié impression procédure et il a changé de travail différents:

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;

InformationsquelleAutor Marcus Poli

Un pipeline de fonction avec quelques options supplémentaires pour conduire le comportement. Testé/fonctionne sur Windows, Oracle 11g (j'ai quelques soupçons, elle peut ne pas sous *nix environnements, à cause de la façon dont les lignes de résilier).

CREATE OR REPLACE FUNCTION ETL_HELPER_PARSE
(P_CLOB NCLOB, P_LINES_TO_SKIP INT DEFAULT 0, P_PUT_EMPTY_LINES CHAR DEFAULT 'N') RETURN SYS.ODCIVarchar2List PIPELINED
AS
c_top_lines_to_skip  CONSTANT NUMBER  NOT NULL := P_LINES_TO_SKIP;
c_output_empty_lines CONSTANT CHAR(1) NOT NULL := P_PUT_EMPTY_LINES; 
--
l_len     INT := DBMS_LOB.GETLENGTH(P_CLOB);
l_hit     INT := 0;
l_offset  INT := 1;
l_amount  INT;  
l_buffer  VARCHAR2(32767);
l_cnt     INT := 1;  
BEGIN  
WHILE ( l_offset < l_len )
LOOP
l_hit := DBMS_LOB.INSTR (
lob_loc    => P_CLOB           -- IN   CLOB      CHARACTER SET ANY_CS
,pattern    => CHR(13)||CHR(10) -- IN   VARCHAR2  CHARACTER SET lob_loc%CHARSET
,offset     => l_offset         -- IN   INTEGER := 1
,nth        => 1                -- IN   INTEGER := 1
);
l_amount := CASE WHEN COALESCE(l_hit, 0) > 0 THEN l_hit - l_offset ELSE l_len - l_offset + 1 END;
-- `l_amount=0` means a new empty line has been encountered
IF l_cnt > c_top_lines_to_skip       
THEN
IF l_amount > 0
THEN
DBMS_LOB.READ(P_CLOB, l_amount, l_offset, l_buffer);
PIPE ROW (l_buffer);
ELSIF UPPER(c_output_empty_lines) = 'Y'
THEN
PIPE ROW ('');
END IF;
END IF;
l_offset := CASE WHEN COALESCE(l_hit, 0) > 0  THEN l_hit + 2 ELSE l_len END;    
l_cnt := l_cnt + 1;
end loop;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('Error : '||SQLERRM);
END ETL_HELPER_PARSE;

Bonjour RoadVampire! Pourriez-vous fournir un lien vers le résultat final de ce code?

InformationsquelleAutor RoadVampire

-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;
/

Pouvez-vous expliquer ce que votre code est en train de faire? Vous pouvez modifier votre réponse à ajouter cette information.

InformationsquelleAutor Venkat Nannaka

Vous devez vous connecter pour publier un commentaire.