Comment interroger une colonne CLOB dans Oracle ?

Question

J'essaie d'exécuter une requête qui comporte quelques colonnes de type CLOB. Si j'exécute la requête normalement, tous ces champs ont juste (CLOB) comme valeur.

J'ai essayé d'utiliser DBMS_LOB.substr(column ) et je reçois l'erreur

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

Comment puis-je interroger la colonne CLOB ?

Answer 1

Cela fonctionne

select DBMS_LOB.substr(myColumn, 3000) from myTable

Answer 2

Lorsque vous obtenez la sous-chaîne d'une colonne CLOB et que vous utilisez un outil d'interrogation qui a des restrictions de taille/tampon, vous devez parfois définir le TAMPON à une taille supérieure. Par exemple, en utilisant SQL Plus, utilisez le paramètre SET BUFFER 10000 pour le fixer à 10000 car le défaut est 4000.

L'exécution du DBMS_LOB.substr vous pouvez également spécifier le nombre de caractères que vous souhaitez renvoyer et le décalage à partir duquel. Ainsi, en utilisant DBMS_LOB.substr(column, 3000) pourrait le limiter à un montant assez faible pour le tampon.

Voir documentation oracle pour plus d'informations sur la commande substr

    DBMS\_LOB.SUBSTR (
       lob\_loc     IN    CLOB   CHARACTER SET ANY\_CS,
       amount      IN    INTEGER := 32767,
       offset      IN    INTEGER := 1)
      RETURN VARCHAR2 CHARACTER SET lob\_loc%CHARSET;

Answer 3

J'ai rencontré un autre problème avec HugeClob dans ma base de données Oracle. Le site dbms_lob.substr ne permet qu'une valeur de 4000 dans la fonction, ex :

dbms_lob.substr(column,4000,1)

ainsi, pour mon HughClob qui était plus grand, j'ai dû utiliser deux appels en select :

select dbms_lob.substr(column,4000,1) part1, 
       dbms_lob.substr(column,4000,4001) part2 from .....

Comme j'appelais depuis une application Java, j'ai simplement concaténé les parties 1 et 2 et les ai envoyées par courrier électronique.

Answer 4

S'il s'agit d'une CLOB, pourquoi ne pouvons-nous pas to_char la colonne et ensuite effectuer une recherche normale ?

Créer un tableau

CREATE TABLE MY_TABLE(Id integer PRIMARY KEY, Name varchar2(20), message clob);

Créer quelques enregistrements dans cette table

INSERT INTO MY_TABLE VALUES(1,'Tom','Hi This is Row one');
INSERT INTO MY_TABLE VALUES(2,'Lucy', 'Hi This is Row two');
INSERT INTO MY_TABLE VALUES(3,'Frank', 'Hi This is Row three');
INSERT INTO MY_TABLE VALUES(4,'Jane', 'Hi This is Row four');
INSERT INTO MY_TABLE VALUES(5,'Robert', 'Hi This is Row five');
COMMIT;

Recherche dans la colonne clob

SELECT * FROM MY_TABLE where to_char(message) like '%e%';

Résultats

ID   NAME    MESSAGE   
===============================  
1    Tom     Hi This is Row one         
3    Frank   Hi This is Row three
5    Robert  Hi This is Row five

Answer 5

Une autre option consiste à créer une fonction et à l'appeler chaque fois que vous devez sélectionner la colonne clob.

create or replace function clob_to_char_func
(clob_column in CLOB,
 for_how_many_bytes in NUMBER,
 from_which_byte in NUMBER)
return VARCHAR2
is
begin
Return substrb(dbms_lob.substr(clob_column
                            ,for_how_many_bytes
                            ,from_which_byte)
            ,1
            ,for_how_many_bytes);
end;

et appeler cette fonction comme ;

SELECT tocharvalue, clob_to_char_func(tocharvalue, 1, 9999)
FROM (SELECT clob_column AS tocharvalue FROM table_name);