Discussion :

[Endymion222]

Bonjour à tous,

Voila, j'ai un magnifique champ LONG dans lequel j'essaye de rechercher une chaine de caractère... Je sais c'est à priori impossible.

J'ai essayé de faire une fonction transformant le LONG en varchar2 (avec pertes) :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
create or replace function comm (ind51 in NUMBER) return varchar2 is
res varchar2(400) ;
begin
     select commentaire into res from &user_cort.rep_act where P51=ind51 ;
     return res ;
end comm ;
/

et de l'utiliser comme ca :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
update ic_actes
       set actes_compl = 1
       where
            comm (ic_actes.E51) LIKE '%toto%'
;

Je reçois en réponse un

ORA-06502: PL/SQL : erreur numérique ou erreur sur une valeur: tampon de chaîne
de caractères trop petit

J'ai réduit la longueur du varchar2 de la fonction à 1, sans changement...

Pitié, ne me dites pas que c'est pas possible... (ou je pose ma démission )

[plaineR]

Déclare res en varchar2(32767).

Mais attention, il ne faut pas que ton long excède 32767 caractères, sinon ta technique ne fonctionnera pas.

[EDIT] Pourquoi n'utilises-tu pas les CLOB ? [/EDIT]

[Endymion222]

varchar2 peut depasser les 4000 caractères ah...

Bein, j'ai pas le choix, la BDD est telle qu'elle est... (et pour faire changer quelque chose dans cette boite... je vous dit pas)

[plaineR]

varchar2 peut depasser les 4000 caractères ah...

Oui et non...

Oui : en pl/sql la longueur maxi d'un varchar2 est de 32767 caractères (comme le long).
Non: en sql la longueur maxi d'un varchar2 est de 4000 caractères (alors que le long peut aller jusqu'à 2Go).

[Endymion222]

Je viens de jeter un oeil aux CLOB...

Ce type serait de très très loin plus adapté...

J'ai deux questions :

1/ Peut t'on changer le type de LONG vers CLOB à la volée (sans faire une copie de table par exemple)?

2/ La requete pour inserer un mot dans un LONG est elle la même que pour l'inserer dans un CLOB (faut il ajouter des '' par exemple)?

[LeoAnderson]

ça dépend.... de la version de la base ! :-)

en 8i, non, pas vraiment.
En 10g, oui :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

ALTER TABLE <LATABLE> MODIFY <LACOL> CLOB;

En 9i, ben euh.. je sais plus...

[plaineR]

1/ Peut t'on changer le type de LONG vers CLOB à la volée (sans faire une copie de table par exemple)?

C'est très simple, il suffit de faire :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
insert into table2 (col1, col2, ..., col_clob) 
select col1, col2, ..., to_lob (col_long)
from table1;

Tu supprimes tabe1 et tu renommes table2. Sinon tu peux ajouter une colonne à table1, puis supprimer la colonne long.

La requete pour inserer un mot dans un LONG est elle la même que pour l'inserer dans un CLOB (faut il ajouter des '' par exemple)?

Il me semble que c'est la même... Insertion dans un clob :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

insert into table1 (col1, col2, ...., col_clob) values (1,2,..., 'clob');

Pour plus d'informations sur les clob, 2 articles :
http://helyos.developpez.com/lob/
http://sheikyerbouti.developpez.com/pl_sql/?page=Chap9

[Endymion222]

Merci beaucoup...

Après batterie de test... notre application ne supporte pas les CLOB (un select sur un CLOB => au revoir...)

* soupir *

Merci quand même... * va se pendre *

[Endymion222]

Bonjour !

Je reviens à la charge sur mon pb... Je suis en train de travailler sur le code fourni par Sheik Yerbouti dans son excellent cours PL/SQL. (http://sheikyerbouti.developpez.com/...=Chap9#L9.2.18)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
DECLARE
  lobd CLOB;
  pattern VARCHAR2 := 'abcde';
  position INTEGER := 10000;
BEGIN
  -- Initialisation du LOB
  SELECT b_col INTO lobd
  FROM lob_table
  WHERE key_value = 21;
 
  position := DBMS_LOB.INSTR(lobd,pattern, 1, 1);
 
  IF position = 0 THEN
     dbms_output.put_line('Pattern non trouvée');
  ELSE
     dbms_output.put_line('Pattern trouvée en position ' || position);
  END IF;
END;

C'est exactement ce qu'il me faut (enfin, la version procédure), mais quand je l'execute, ca me fait des trucs vraiment bizarre...

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
create or replace function complementaire (ind51 in NUMBER) return number is
  lobd CLOB;
  pattern VARCHAR2(1) := 'a';
  position NUMBER := 10000;
BEGIN
 
  SELECT commentaire INTO lobd from actes_temp where P51=ind51 ;
  --position := DBMS_LOB.INSTR(lobd,pattern, 1, 1);
 
  return position ;
END complementaire ;
/

Si je met le SELECT en commentaire, ca me renvois 10000 (normal)
Sinon, ca me renvois null...

[plaineR]

Ta colonne commentaire n'est pas null pour ta valeur ind51?

Si non, que ce passe-t-il si tu tapes directement sous SQL*Plus :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

SELECT DBMS_LOB.INSTR(commentaire, 'a') from actes_temp where P51=ind51;

[Endymion222]

(désolé pour la lenteur de la réponse, j'ai été un peu overbooké )

Le champs n'est pas null (j'ai eu peur un moment)

SQL> SELECT DBMS_LOB.INSTR(commentaire, 'a') from actes_temp where P51=-15595 ;

DBMS_LOB.INSTR(COMMENTAIRE,'A')
-------------------------------
56

[plaineR]

Et que donne cela ?

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
create or replace function complementaire (ind51 in NUMBER) return number is 
  position NUMBER := 10000; 
BEGIN 
  SELECT DBMS_LOB.INSTR(commentaire, 'a') INTO position from actes_temp where P51=ind51 ; 
  return position ; 
END complementaire ; 
/

[EDIT]
Pourquoi passes-tu par une fonction ? Pourquoi ne pas utiliser directement DBMS_LOB.INSTR ?
[/EDIT]

[Endymion222]

Justement, entre temps, j'ai trouvé une solution en utilisant une vue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

create or replace view pos_actes as select P51, DBMS_LOB.INSTR(commentaire,'a', 1, 1) as position from actes_temp ;

[plaineR]

Je ne sais pas si une vue est vraiment indispensable

Pourquoi ne pas directement utiliser dbms_lob.instr ?
Si je me souviens ton besoin initial était de faire une clause where du style :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
select *
from matable
where dbms_lob.instr (commentaire, 'a') != 0;

[Endymion222]

Je fais une clause where un peu tordue :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
update ic_actes
       set actes_compl = (select 1 from pos_actes where
                                 ic_actes.E51 = pos_actes.P51 AND
                                 position is not null )
;

C'est probablement faisable sans la vue, mais elle me facilité la vie (et à priori ne ralentit pas le traitement)

Encore merci!