Erreur ORA-01704 : constante de chaine trop longue

**verrec_s** · 10/12/2004, 11h45

Bonjour,
je rencontre un souci que je ne m'explique pas dans une procedure PL/SQL sous Oracle 8i.
Dans une table j'ai 5 champs de type varchar2(4000).

Je suis obligé dans ma procédure de construire dynamiquement ma requète SQL. Pour cela je recupère ces champs dans des variables (elles aussi de type varchar2(4000)), tout se passe bien.

Ma requète dynamique est divisée en 5 chaines de type varchar2 (littéralement, une variable insert, select, from, where et groupby) que j'alimente sans problème dans la procédure.

Tout fonctionne bien jusqu'au moment ou j'effectue mon DBMS_SQL.PARSE pour exectuter ma requete construite.
En effet, plutot que de concaténer mes 5 chaines dans une seule variable (qui serait limitée à 32767 caractères, sachant qu'avec 5 colonnes de 4000 caractères j'en suis déjà à 20000 et la requète peut sous certaines conditions dépasser cette limite), je concactène donc directement mes variables de requète lors de l'appel à DBMS_SQL.PARSE.

J'ai déjà effectué ce type de méthode, sans que cela ne me pose de problème, qq1 voit-il d'où peut venir le problème?

thx,
STeF

**plaineR** · 10/12/2004, 12h17

Tout d'abord bienvenu sur le forum

Ensuite, pour répondre à ta question :

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
SQL> desc dbms_sql
....
PROCEDURE PARSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              NUMBER(38)              IN    
 STATEMENT                      VARCHAR2                IN    
 LANGUAGE_FLAG                  NUMBER(38)              IN    
PROCEDURE PARSE
 Argument Name                  Type                    In/Out Default?
 ------------------------------ ----------------------- ------ --------
 C                              NUMBER(38)              IN    
 STATEMENT                      TABLE OF VARCHAR2(256)  IN    
 LB                             NUMBER(38)              IN    
 UB                             NUMBER(38)              IN    
 LFFLG                          BOOLEAN                 IN    
 LANGUAGE_FLAG                  NUMBER(38)              IN    
...

Tu ne peux passer en argument que du varchar2 ou du table of varchar2(256)
=> tu passes une chaine de caractères qui fait plus de 32767 caractères d'ou l'erreurs

La solution est donc de découper ta chaine par élément de 256 caractères dans un tableau et de passer ce tableau en paramètre.

Une autre solution serait de gérer des bind variables pour les valeurs qui font 4000 caractères ce qui réduirait sans doute la longueur de ta requête.

**verrec_s** · 13/12/2004, 08h36

Merci pour l'accueil :o

Je suis en train de découper mes chaines dans une collection de varchar(256), le week end etant passe, je n'ai pas encore fini.

Cela dit, quelque chose me tracasse. Je viens de lancer ma procédure en mode débug et je me rends compte que dans le cas concret que je suis en train de tester, la taille total de toutes mes chaines reunis n'est seulement que de 8377 caractères. Je ne comprends donc pas pourquoi il part en exception...

:

STeF

**LeoAnderson** · 13/12/2004, 09h03

Le plus sûr est d'encadrer la concaténation des variables dans un bloc BEGIN/EXCEPTION/END et ainsi de tracer précisément et de manière sûre le contenu des variables qui provoquent l'exception.

Code :

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

1
2
3
4
5
6
7
BEGIN
   v_Requete := v_Col1||v_Col2||v_Col3||v_Col4||v_Col5;
EXCEPTION
   WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('COL1 a une longueur de '||to_char( length(v_Col1) ) );
      etc...
END;

**Orameur** · 13/12/2004, 09h06

à quel moment votre programme sort à l'exception?

**verrec_s** · 13/12/2004, 09h13

J'utilise Toad, donc je trace les variables contenant la taille des chaines.
Pour etre précis, le traitement part en exception à la ligne :

DBMS_SQL.PARSE(curseur, sql_cmd_insrt || sql_cmd_slct || sql_cmd_from || sql_cmd_wher || sql_cmd_grpby, DBMS_SQL.NATIVE);

Et le debuggueur me sort :
taille_insrt = 227
taille_slct = 442
taille_from = 147
taille_wher = 7286
taille_grpby = 272

Au début j'ai pensé que mon pb venait de la taille de sql_cmd_wher, mais dans le doute pour l'instant, je l'ai déclaré en varchar(32000).

**LeoAnderson** · 13/12/2004, 09h17

Au lieu d'effectuer la concaténation au moment de l'appel, effectuez-là avant (puisqu'on a vu que ça revenait de toute façon au même)

Et le debugger, c'est pas que je ne lui fais pas confiance, mais, en cas de doute, rien de tel que les messages gérés directement par Oracle...

**verrec_s** · 13/12/2004, 09h47

J'ai déjà testé de tout concaténer dans une variable avant de faire l'appel a dbms_sql.parse. C'etait meme ce que je faisais au depart.

En tracant avec Oracle, il me renvoit la meme chose que le debuggueur.

**LeoAnderson** · 13/12/2004, 09h52

C'est normal !

http://ora-01704.ora-code.com/

ORA-01704: string literal too long
Cause: The string literal is longer than 4000 characters.
Action: Use a string literal of at most 4000 characters. Longer values may only be entered using bind variables.

**orafrance** · 13/12/2004, 10h58

une question bête... pourquoi ne pas utiliser EXECUTE IMMEDIATE ?

**verrec_s** · 13/12/2004, 11h03

Parce qu'avec execute immediate, le pb est le meme

**orafrance** · 13/12/2004, 11h15

Si j'ai bien compris tu fais un truc du style :

Code :

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

1
2
 
EXECUTE IMMEDIATE 'SELECT toto FROM tata WHERE col1 = ' || var1 || ' AND col2 = '|| var2 ...

Est-ce que ça ne fonctionnerait pas mieux en remplaçant la syntaxe précédente par :

Code :

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

1
2
 
EXECUTE IMMEDIATE 'SELECT toto FROM tata WHERE col1 = :1 AND col2 = :2' ... USING var1, var2...

http://sheikyerbouti.developpez.com/...mmediate/#L4.3

**verrec_s** · 13/12/2004, 12h05

Effectivement, je n'avais pas utilisé de bind variable.
Je viens de le faire, en simplifiant ma requete, mais maintenant j'obtiens le message :
ERREUR-1461 ORA-01461: une valeur 'LONG' ne peut être liée que dans une colonne de type 'LONG'

Je ne comprends pas..., je commence à perdre la boule.
Le truc ce que je vais etre obligé d'avoir une variable de + de 4000 caractères (l'appli a été faite de cette facon la, et je ne fais que la maintenir, pas la refaire complètement).

En tous cas, je tiens à remercier tous ceux qui ont participé (ca m'a permis de tester plusieurs méthodes

) et surtout pour la réactivité.

J'espère que j'arriverai à trouver une solution !!!

**orafrance** · 13/12/2004, 12h15

il y a un LONG... alors là c'est moche

Il faut passer par une conversion en CLOB

**verrec_s** · 13/12/2004, 12h18

Aie, j'avoue ne jamais avoir utilisé de CLOB
Comment qu'on fait?

**orafrance** · 13/12/2004, 12h43

c'est TRES lourd

Il faut créer 2 tables bidons :

Code :

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

1
2
CREATE GLOBAL TEMPORARY TABLE table_long (id DATE, dummy LONG) ON COMMIT DELETE ROWS;
CREATE GLOBAL TEMPORARY TABLE table_clob (id DATE, dummy CLOB) ON COMMIT DELETE ROWS;

Et tu peux créer la fonction suivante :

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
CREATE OR REPLACE FUNCTION long2clob (long_var LONG)
RETURN CLOB AS
clob_var CLOB;
date_var DATE := SYSDATE;
 
BEGIN
INSERT INTO table_long
VALUES (date_var, long_var);
 
execute immediate 'INSERT INTO table_clob SELECT id, TO_LOB(dummy) FROM table_long WHERE id = :1' USING date_var;
 
SELECT dummy
INTO clob_var
FROM table_clob
WHERE id = date_var;
 
RETURN clob_var;
END;
/

Exemple d'utilisation :

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
20
CREATE TABLE orafrance (macol CLOB)
/
 
Table créée.
 
declare
toto long := 'je suis content';
begin
insert into orafrance values (long2clob(toto));
end;
/
 
Procédure PL/SQL terminée avec succès.
 
select * from orafrance
/
 
MACOL
--------------------------------------------------------------------------------
je suis content

**verrec_s** · 13/12/2004, 13h34

Juste pour ma culture générale, j'aimerai savoir pq il me balance cette erreur (1461) ?

Pourquoi me parle-t-il de long? D'après les info que j'ai trouvé sur le net, ce message apparait lorsque l'on essai d'inserer dans une colonne qui n'est pas de type long, un élément long.

Mais de quel élément long s'agit-il?

**orafrance** · 13/12/2004, 14h05

est-ce qu'une des variables n'est pas de type LONG ou alors les valeurs ne viennent-elles pas d'une table avec un LONG ?

**verrec_s** · 13/12/2004, 14h20

Non justement, mes variables ne sont que du varchar2 (variables servant à créer dynamiquement la requête) et la requête en elle meme est un insert de ce type :

Code :

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

insert into table(chp1, chp2... chpN) select chp1, chp2... chpN from table2, table3 where .... group by ...

Et meme dans tous les champs que je ramène dans la sous instruction select, aucun des champs n'est de type LONG. Et dans les colonnes dans lesquelles je veux insérer mes valeurs il n'y en a pas non plus...

Histoire d'être sur, j'ai meme executé manuellement ma requête, elle fonctionne bien.

J'en perds mon latin

**orafrance** · 13/12/2004, 14h30

probablement que si la chaine de caractères est trop longue, Oracle considère que c'est du LONG

Erreur ORA-01704 : constante de chaine trop longue

Oracle

Discussions similaires

Partager

Partager