Export et Oracle
Par Cédric Tabin le vendredi 31.07.2009, 08:00 - Général - Lien permanent
Depuis quelque temps je travaille avec une base de données Oracle, et j'ai constaté qu'il n'est pas possible d'exporter autre chose que des dump au format binaire...
Comme je trouve généralement très pratique d'avoir toutes les requêtes de type INSERT, j'ai bricolé une procédure stockée (inspirée d'ici) qui permet de les générer.
Pour l'exemple, j'aimerais pouvoir récupérer les données contenues dans la table suivante :
CREATE TABLE "MESSAGE" ( "CODEPK" VARCHAR2(100 CHAR) NOT NULL ENABLE, "OPERATIONSTATUS" VARCHAR2(100 CHAR) NOT NULL ENABLE, "TYPE" VARCHAR2(100 CHAR) NOT NULL ENABLE, "REQUESTTYPE" VARCHAR2(255 CHAR), "EDMSDESCRIPTOR" CLOB, CONSTRAINT "PRIMARY_MESSAGE" PRIMARY KEY ("CODEPK") ENABLE )
Après avoir enregistré la procédure sur le serveur, il faut simplement lancer la commande suivante :
SELECT GET_INSERT_SCRIPT('MATABLE') FROM DUAL
La table DUAL est créée par Oracle. Elle ne contient qu'une colonne avec la valeur X (plus d'infos ici). Cela va renvoyer un résultat (plutôt indigeste) qui s'approche de ce genre :
SELECT 'INSERT INTO MESSAGE (CODEPK,OPERATIONSTATUS,TYPE,REQUESTTYPE,EDMSDESCRIPTOR) VALUES (' || CASE WHEN CODEPK IS NULL THEN 'NULL' ELSE '''' || REPLACE(CODEPK, '''', '''''') || '''' END || ',' || CASE WHEN OPERATIONSTATUS IS NULL THEN 'NULL' ELSE '''' || REPLACE(OPERATIONSTATUS, '''', '''''') || '''' END || ',' || CASE WHEN TYPE IS NULL THEN 'NULL' ELSE '''' || REPLACE(TYPE, '''', '''''') || '''' END || ',' || CASE WHEN REQUESTTYPE IS NULL THEN 'NULL' ELSE '''' || REPLACE(REQUESTTYPE, '''', '''''') || '''' END || ',' || CASE WHEN EDMSDESCRIPTOR IS NULL THEN to_clob('NULL') ELSE '''' || EDMSDESCRIPTOR || '''' END || ');' FROM MESSAGE
Toutefois, cela est également une requête SQL et c'est elle qui va faire tout le travail ! Il suffit donc simplement de faire un copier-coller et de réexecuter la requête pour récupérer le script d'insertion
INSERT INTO MESSAGE (CODEPK,OPERATIONSTATUS,TYPE,REQUESTTYPE,EDMSDESCRIPTOR) VALUES ('TASK_PROCESS_FAILURE','FAILURE','SYSTEM_FAILURE',NULL,NULL); INSERT INTO MESSAGE (CODEPK,OPERATIONSTATUS,TYPE,REQUESTTYPE,EDMSDESCRIPTOR) VALUES ('TASK_INVALID_FIELD','FAILURE','INVALID_INPUT',NULL,NULL); INSERT INTO MESSAGE (CODEPK,OPERATIONSTATUS,TYPE,REQUESTTYPE,EDMSDESCRIPTOR) VALUES ('DATA_INVALID','FAILURE','CORRUPTED_DATA',NULL,'<?xml version="1.0" ?><error>...</error>');
Voici donc le script en question que j'ai utilisé :
CREATE OR REPLACE FUNCTION GET_INSERT_SCRIPT(V_TABLE_NAME VARCHAR2) RETURN CLOB AS V_INSERT_QUERY CLOB; V_COLUMN_NAMES CLOB; V_COLUMN_DATA CLOB; BEGIN FOR R_TABLE IN (SELECT TABLE_NAME FROM user_tables WHERE TABLE_NAME = UPPER (V_TABLE_NAME)) LOOP V_INSERT_QUERY := 'SELECT ''INSERT INTO ' || R_TABLE.TABLE_NAME || ' ('; FOR R_COLUMN IN (SELECT * FROM user_tab_columns WHERE TABLE_NAME = R_TABLE.TABLE_NAME ORDER BY COLUMN_ID) LOOP IF R_COLUMN.COLUMN_ID > 1 THEN V_COLUMN_NAMES := V_COLUMN_NAMES || ','; V_COLUMN_DATA := V_COLUMN_DATA || ','; END IF; V_COLUMN_NAMES := V_COLUMN_NAMES || R_COLUMN.COLUMN_NAME; IF INSTR (R_COLUMN.DATA_TYPE, 'CHAR') > 0 OR INSTR (R_COLUMN.DATA_TYPE, 'VARCHAR2') > 0 THEN V_COLUMN_DATA := V_COLUMN_DATA || ''' || CASE WHEN ' || R_COLUMN.COLUMN_NAME || ' IS NULL THEN ''NULL'' ELSE '''''''' || replace(' || R_COLUMN.COLUMN_NAME || ', '''''''', '''''''''''') || '''''''' END || '''; ELSIF INSTR (R_COLUMN.DATA_TYPE, 'DATE') > 0 THEN V_COLUMN_DATA := V_COLUMN_DATA || ''' || CASE WHEN ' || R_COLUMN.COLUMN_NAME || ' IS NULL THEN ''NULL'' ELSE ''to_date('''''' || to_char(' || R_COLUMN.COLUMN_NAME || ', ''DD.MM.YYYY hh24:mi'') || '''''', ''''DD.MM.YYYY hh24:mi'''')'' END || '''; ELSIF INSTR (R_COLUMN.DATA_TYPE, 'CLOB') > 0 THEN V_COLUMN_DATA := V_COLUMN_DATA || ''' || CASE WHEN ' || R_COLUMN.COLUMN_NAME || ' IS NULL THEN to_clob(''NULL'') ELSE '''''''' || ' || R_COLUMN.COLUMN_NAME || ' || '''''''' END || '''; ELSE V_COLUMN_DATA := V_COLUMN_DATA || ''' || CASE WHEN ' || R_COLUMN.COLUMN_NAME || ' IS NULL THEN 0 ELSE ' || R_COLUMN.COLUMN_NAME || ' END || '''; END IF; END LOOP; END LOOP; V_INSERT_QUERY := V_INSERT_QUERY || V_COLUMN_NAMES || ') VALUES (' || V_COLUMN_DATA || ');'' FROM ' || V_TABLE_NAME; RETURN V_INSERT_QUERY; END;
C'est assez sommaire, mais suffisant pour des cas simples. Toutefois, il y a quelques problèmes qui subsistent et qui ne sont pas à négliger :
- Si le contenu d'un CLOB (Character Large OBject) est trop grand, il ne sera pas possible de l'insérer via un INSERT.
- Si la table contient trop de colonnes, la requête ne sera pas affichée entièrement (Oracle n'affiche que les 4000 premiers caractères d'un CLOB).
D'autres idées sont également les bienvenues