comment avoir plus de 4000 Char dans une variable CLOB?

Version imprimable

Bonjour à tous,

je suis confronté au probleme suivant. J´ai une requete SQL dans laquelle un champ de type CLOB comporte plus de 4000 caracteres(exactement 5332).
Mais à l´appele de la requete il ne me ressort que 4000. C´est un select normal et j´utilise l´expresssion :

DBMS_LOB.SUBSTR(bftext,4000,1) pour recuperer la variable mais ca limite à 4000. Avant cela, à la place de DBMS_LOB.SUBSTR on utilisait to_char(bftext) et ca nous renvoyait l´erreur :

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion

Comment faire pr recuperer cette variable de plus de 4000 caracteres?

La requete(la partie concernée puisque cette requete est sehr longue):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
 
 
  select * from
             (
 
                select
 
 
                         case BFTCode
                         when 'PASCH' then
 
                             'Histoire du '
                         else 
 
                           nvl(regexp_replace(DBMS_LOB.SUBSTR(bftext,4000,1), '- Examen realisé: \s+?([^'||v_Linebreak||']*)'||v_Linebreak||'.*$', '\1',1,1,'n'), 'Examen')
 
                             ||' du '
                         end
                         ||
 
                         case BFTCode
                         when 'PASCH' then
                             regexp_replace(
                               regexp_substr(
                                 DBMS_LOB.SUBSTR(bftext,4000,1)
                                 -- DBMS_LOB.SUBSTR(bftext,4000,1)
                                 , ' - Date Examen:\s+\d\d\d\d\d\d\d\d'
                                 , 1, 1 
                               )
                               , ' - Date Examen:\s+(\d\d\d\d)(\d\d)(\d\d)', '\3.\2.\1' 
                             )
 
                             || ' - ' || regexp_replace(BFTitre, '\s+-$', '')
                         else 
                             regexp_substr(
                               regexp_substr(
                                 DBMS_LOB.SUBSTR(bftext,4000,1)
                                 , ' - Date Examen:\s+\d\d.\d\d.\d\d\d\d'
                                 , 1, 1  
                               )
                                , '\d\d.\d\d.\d\d\d\d'
                             )
                         end
 
                         || v_Linebreak
 
                         || rpad('-',
                                 case BFTCode
                                 when 'RAD' then 48
                                 when 'PASCH' then 73
                                 else 43
                                 end
                                 , '-')||v_Linebreak
 
                         || replace(
                              replace(
                                replace(
                                  regexp_replace(
                                    regexp_replace(
                                      regexp_replace(
                                        rtrim(DBMS_LOB.SUBSTR(bftext,4000,1))
                                        , ' - Recommandation : .*', '', 1, 1, 'n')
                                      , ' - Date Examen:\s+[0-9.]{8,10}'||v_Linebreak, '')
                                    , '- Examen realisé: \s+?([^'||v_Linebreak||']*?)'||v_Linebreak, '',1,1,'n')
                                  , ' - Befundtext: '||v_Linebreak, '')
                                , ' - Beurteilung: '||v_Linebreak, '')
                              , '- Critiques: '||v_Linebreak, '')
 
                         || v_Linebreak RESULTAT
 
                    from bfbefind
 
                         join BFTBefindtyp on BFTRefnr = bftyp
                   where BFHoRefnr = p_HoRefnr
                     and BFStatus = 1 
                     and BFTCode in ('RAD', 'PASCH', 'ENDO', 'SONO', 'NSONO', 'EPU', 'NACHK', 'EK')
                order by BFTCode, BFDate, BFAuftragsnr, BFTitre
            )

Merci pour votre aide

23/04/2010, 10h02
popopop

Bonjour,

Il faut que tu récupères le contenu de ton champ de type CLOB dans une varible de ce même type.

Après tu peux effectuer tes divers traitements sur cette variable.

http://download.oracle.com/docs/cd/B...4258/d_lob.htm

oulalala je suis pas bien réveillé...
j'avais cru voir 40 000 8O

Par contre je ne comprend pas bien ton problème...

tu fais : BMS_LOB.SUBSTR(bftext,4000,1) => c'est normal que tu as 4000 caractères
23/04/2010, 10h21
makohsarah

merci j´ai lu le lien mais je ne vois tjrs pas comment je pourrais mettre le resultat dans une variabel (j´appele la requete dans un cursor et comme j´ai dit c´est une longue requete , il y a un UNION ALL SELECT *** qui suit celle que j´ai mise dans le message)

merci
23/04/2010, 10h24
makohsarah

effectivement tu fais : DBMS_LOB.SUBSTR(bftext,4000,1) ne prend pas plus de 4000 en paramettre sinon on a un message d´erreur du genre:

ORA-06502: PL/SQL: numeric or value error: character string buffer too small

Comme j´ai dit avant j´avais pas du tout de reponse mais une erreur :

ORA-22835: Buffer too small for CLOB to CHAR or BLOB to RAW conversion car on utilisais un to_char(bftext) à la place.

Donc je cherche une alternative....
23/04/2010, 11h32
sgora

Ton CLOB fait plus de 32K ? Si je me souviens bien, les variables caractère PL/SQL peuvent contenir jusqu'à 32768 octets.
23/04/2010, 11h39
makohsarah

Hi,

ma variable bftext est declarée comme CLOB dans ma Table (BFTEXT CLOB). Je ne sais pas si il faut faire une autre declaration?

J´utilise Oracle 11.

j´ai pris un cas ou le bftext ne fonctionne pas, il est tronqué à 4000 avec cette procedure.
J´ai fait le test dans cette fonction :
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 l_data varchar2(10000); l_clob clob; begin select bftext into l_clob from bfbefind where bfrefnr = 355692; l_data := dbms_lob.substr( l_clob, dbms_lob.getlength(l_clob), 1 ); dbms_output.put_line(l_data); end; /
Et j´obtiens le resultat escompté non tronqué. Comment je pourrai introduire cela à ma procedure?

merci

23/04/2010, 13h27
sgora

De la même manière, tu déclares une variable de type clob et tu fais un select into, puisque ton select *.... de ta procédure ne ramène qu'une colonne concaténée.
23/04/2010, 15h55
makohsarah

compliqué compliqué

Bonjour a la communautée,

je reviens avec mon probleme de recuperation de variable CLOB. J´ai écrit ce petit script de test, il compile sans problemes (j´utilise Toad 9.7.2 et oracle 11).

Mais pour recuperer les valeurs dans la variable v_test je ne m´en sors pas (v_test est vide dans l´onglet DBMS Output et pourtant dans l´onglet script Output j´ai les resultats complets).

Dans le sous onglet Grid1 de l´onglet Script Output les valeurs sont affichées mais tronquées, elle sont pas completes (Pourquoi cela??).

Mais mon gros probleme c´est de recuperer la valeur de bftext dans v_test !!!!!!

Je ne sais pas pourquoi elle est vide. comment y remedier?
Code:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 variable r refcursor declare v_test clob; begin open :r for select bftext into v_test from bfbefind join BFTBefindtyp on BFTRefnr = bftyp where BFHoRefnr = 272866 and BFStatus = 1 and BFTCode in ('RAD', 'PASCH', 'ENDO', 'SONO', 'NSONO', 'EPU', 'NACHK', 'EK') order by BFTCode, BFTitel; dbms_output.put_line ('Valeur : ' || v_test ); end; / print r
Merci de l´aide

29/04/2010, 14h32
Waldar

Mais pourquoi dbms_output ?
Pourquoi pas return ?
29/04/2010, 14h40
makohsarah
En fait cette requete se trouve dans une procedure qui à comme parametre de retour un curseur. Exemple:
Code:

1 2 3 4 5 6 7 Procedure FallxText( MyRefCursor IN OUT SYS_REFCURSOR, p_HoRefnr IN NUMBER ,p_debug IN NUMBER default 0 ) AS .....
c´est pour ca que dans mon exemple j´essaie de de trouver un moyen qui va m´aider à faire passer le contenu de la colonne bftext de type CLOB dans ce curseur sans pour autant limiter la taille à 4000 caracteres. J´ai par exemple un cas ou bftext à 5600 caracteres.