Comment convertir CLOB en VARCHAR2 dans Oracle

La conversion de données au format chaîne entre différents types de données est généralement un processus simple dans Oracle, mais certains cas sont un peu plus difficiles que d'autres. L'un de ces cas se produit lorsque vous utilisez Oracle pour convertir CLOB en VARCHAR2, ce qui est difficile car une colonne VARCHAR2 a un maximum de 4 000 caractères, tandis que CLOB peut stocker d'énormes quantités de données de caractères. Vous pouvez néanmoins atteindre votre objectif, en utilisant soit la méthode dbms_lob.substr, soit TO_CHAR en spécifiant une taille maximale.

CLOB et VARCHAR2 expliqués

CLOB (ou « clob » familièrement) est un acronyme pour « character large object », et il peut stocker jusqu'à 4 Go de données. Il est utilisé par plusieurs systèmes de base de données, y compris DB2 et Oracle. CLOB est utile lorsque vous avez besoin de stocker de vastes quantités de données, mais la taille elle-même peut causer des problèmes dans certaines situations. C'est pourquoi la conversion d'un CLOB Oracle vers un format chaîne de taille plus gérable est souvent une bonne approche avant d'effectuer des opérations.

Le format VARCHAR2 stocke de plus petites quantités de données, généralement jusqu'à 4 000 octets, à moins que vous n'utilisiez une taille de chaîne maximale étendue, auquel cas il peut aller jusqu'à 32 767 octets. Pour un jeu de caractères à un octet, vous pouvez généralement obtenir jusqu'à 4 000 caractères dans une colonne. La différence entre les tailles maximales de ces deux formats de données est l'endroit où les problèmes surviennent lors de la conversion.

La méthode dbms_lob.substr

Pour convertir une chaîne CLOB en VARCHAR2, ouvrez votre programme SQL *Plus. La syntaxe pour la conversion que vous devez effectuer est :

SELECT dbms_lob.substr( colonne_clob, pour_combien_doctets, depuis_quel_octet ) FROM table

Ici, les valeurs entre parenthèses sont celles que vous devez changer, ainsi que « table » à la fin. Elles correspondent à la colonne (« colonne_clob ») et à la table avec laquelle vous travaillez. Le « pour_combien_doctets » vous donne l'opportunité de spécifier combien d'octets de données vous voulez convertir, et le « depuis_quel_octet » vous permet de spécifier un point de départ.

Donc, si vous avez une table appelée « taxes » et un champ appelé « net_pay », et en supposant que vous vouliez 4 000 octets en commençant par le premier, vous entreriez :

SELECT dbms_lob.substr(net_pay, 4000, 1) FROM taxes

Pour convertir la chaîne en format VARCHAR2.

La méthode TO_CHAR

Vous pouvez également utiliser la commande TO_CHAR avec une syntaxe très similaire pour atteindre le même objectif :

SELECT TO_CHAR(SUBSTR (colonne_clob,0,3999)) FROM table

Ici, les octets de début et de fin sont spécifiés comme 0 et 3999 (pour une chaîne de 4 000 caractères commençant depuis le début), mais les champs « colonne_clob » et « table » sont utilisés exactement de la même manière qu'avant. Donc, dans le même exemple, vous pourriez entrer :

SELECT TO_CHAR(SUBSTR (net_pay,0,3999)) FROM taxes

Fondamentalement, au lieu de compléter le processus en une seule étape, cela utilise la commande SUBSTR pour extraire une sous-chaîne de la chaîne plus grande, puis utilise « to_char » pour gérer la conversion. Si la colonne CLOB en question a moins de 4 000 octets d'informations, vous pouvez entrer le nom de la colonne dans les parenthèses après to_char au lieu de spécifier une sous-chaîne.