Sometimes, we need, in an application, to extract some table rows to a flat file.
This is a PL/SQL procedure that allows to extract the rows of a table, or the INSERT ordrers.
You can download the script here
Extraction_table procedure
CREATE OR REPLACE PROCEDURE Extraction_Table ( PC$Table in Varchar2, -- Table name PC$File in Varchar2, -- Output file name PC$Directory in Varchar2, -- Outpur Directory name PC$Delimiter in Varchar2 Default ',', -- delimiter character PC$Header in Varchar2 Default 'O', -- Output column header PC$DateFMT in Varchar2 Default 'DD/MM/YYYY', -- Date format PC$Where in Varchar2 Default Null, -- Where clause PC$Order in Varchar2 Default Null -- Order by ) IS
LF$Fichier UTL_FILE.FILE_TYPE ; LC$Ligne Varchar2(32767) ; LI$I Integer ; LC$DateFMT Varchar2(40) := '''' || PC$DateFMT || '''' ;
TYPE REFCUR1 IS REF CURSOR ; cur REFCUR1;
-- Columns -- CURSOR C_COLTAB ( PC$Tab IN VARCHAR2 ) IS SELECT COLUMN_NAME, DATA_TYPE FROM USER_TAB_COLUMNS WHERE TABLE_NAME = PC$Tab AND DATA_TYPE IN ('CHAR','VARCHAR2','NUMBER','DATE','FLOAT') ;
LC$Separateur Varchar2(2) := PC$Delimiter ; LC$Requete Varchar2(10000) ; LC$Desc Varchar2(10000) ; LC$SQLW VARCHAR2(10000):= 'SELECT '; LC$Col VARCHAR2(256);
----------------------------- -- Open the output file -- ----------------------------- FUNCTION Ouvrir_fichier ( PC$Dir in Varchar2, PC$Nom_Fichier in Varchar2 ) RETURN UTL_FILE.FILE_TYPE IS Fichier UTL_FILE.FILE_TYPE ; LC$Msg Varchar2(256);
Begin
Fichier := UTL_FILE.FOPEN( PC$Dir, PC$Nom_Fichier, 'W', 32764 ) ;
If not UTL_FILE.IS_OPEN( Fichier ) Then LC$Msg := 'Erreur ouverture du fichier (' || PC$Dir || ') ' || PC$Nom_Fichier ; RAISE_APPLICATION_ERROR( -20100, LC$Msg ) ; End if ;
Return( Fichier ) ;
Exception
When UTL_FILE.INVALID_PATH Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File location is invalid.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_MODE Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The open_mode parameter in FOPEN is invalid.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_FILEHANDLE Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File handle is invalid.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_OPERATION Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'File could not be opened or operated on as requested.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.READ_ERROR Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the read operation.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.WRITE_ERROR Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Operating system error occurred during the write operation.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INTERNAL_ERROR then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Unspecified PL/SQL error'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.CHARSETMISMATCH Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'A file is opened using FOPEN_NCHAR,' || ' but later I/O operations use nonchar functions such as PUTF or GET_LINE.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.FILE_OPEN Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested operation failed because the file is open.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_MAXLINESIZE Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The MAX_LINESIZE value for FOPEN() is invalid;' || ' it should be within the range 1 to 32767.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_FILENAME Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The filename parameter is invalid.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.ACCESS_DENIED Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'Permission to access to the file location is denied.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.INVALID_OFFSET Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The ABSOLUTE_OFFSET parameter for FSEEK() is invalid;' ||' it should be greater than 0 and less than the total number of bytes in the file.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.DELETE_FAILED Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file delete operation failed.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ; When UTL_FILE.RENAME_FAILED Then LC$Msg := PC$Dir || PC$Nom_Fichier || ' : ' || 'The requested file rename operation failed.'; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
When others Then LC$Msg := 'Erreur : ' || To_char( SQLCODE ) || ' sur ouverture du fichier (' || PC$Dir || ') ' || PC$Nom_Fichier ; RAISE_APPLICATION_ERROR( -20070, LC$Msg ) ;
End Ouvrir_fichier ;
Begin
-- Open file -- LF$Fichier := Ouvrir_fichier( PC$Directory, PC$File ) ;
-- Output column header ? -- If Upper(PC$Header) = 'O' Then LI$I := 1 ; For COLS IN C_COLTAB( PC$Table ) Loop If LI$I = 1 Then LC$Ligne := LC$Ligne || COLS.COLUMN_NAME ; Else LC$Ligne := LC$Ligne || LC$Separateur || COLS.COLUMN_NAME ; End if ; LI$I := LI$I + 1 ; End loop ; -- Output column header -- UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ; ElsIf Upper(PC$Header) = 'I' Then LC$Separateur := ',' ; LC$Desc := 'INSERT INTO ' || PC$Table || ' (' ; LI$I := 1 ; For COLS IN C_COLTAB( PC$Table ) Loop If LI$I = 1 Then LC$Desc := LC$Desc || COLS.COLUMN_NAME ; Else LC$Desc := LC$Desc || LC$Separateur || COLS.COLUMN_NAME ; End if ; LI$I := LI$I + 1 ; End loop ; LC$Desc := LC$Desc || ' ) VALUES (' ; End if ;
-- Building of the query -- LI$I := 1 ;
FOR COLS IN C_COLTAB( PC$Table ) LOOP IF LI$I > 1 THEN LC$SQLW := LC$SQLW || '||' ; END IF ;
If COLS.DATA_TYPE IN ('NUMBER','FLOAT') Then LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',To_char("' || COLS.COLUMN_NAME || '"))' ; ElsIf COLS.DATA_TYPE = 'DATE' Then If Upper(PC$Header) = 'I' Then LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL,''NULL'',''to_date(''''''||' || 'To_char("' || COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' || '||'''''','''|| LC$DateFMT||''')'')' ; Else LC$Col := 'To_char("'|| COLS.COLUMN_NAME || '",'|| LC$DateFMT ||')' ; End if ; Else If Upper(PC$Header) = 'I' Then LC$Col := 'Decode(' || COLS.COLUMN_NAME || ',NULL, ''NULL'',' || '''''''''' || '||"'|| COLS.COLUMN_NAME || '"' || '||' || ''''''''')' ; Else LC$Col := '"'|| COLS.COLUMN_NAME || '"' ; End if ; End if ;
IF LI$I = 1 THEN LC$SQLW := LC$SQLW || LC$Col ; ELSE LC$SQLW := LC$SQLW || '''' || LC$Separateur || '''' || '||' || LC$Col ; END IF ; LI$I := LI$I + 1 ; END LOOP ;
LC$Requete := LC$SQLW || ' FROM ' || PC$Table ;
If PC$Where is not null Then -- add the WHERE clause -- LC$Requete := LC$Requete || ' WHERE ' || PC$Where ; End if ; If PC$Order is not null Then -- add the ORDER BY clause -- LC$Requete := LC$Requete || ' ORDER BY ' || PC$Order ; End if ;
-- Extrac the raws -- Open cur For LC$Requete ; Loop Fetch cur Into LC$Ligne ; Exit when cur%NOTFOUND ; -- Write to the output file -- If Upper(PC$Header) = 'I' Then UTL_FILE.PUT_LINE( LF$Fichier, LC$Desc || LC$Ligne || ' );' ) ; Else UTL_FILE.PUT_LINE( LF$Fichier, LC$Ligne ) ; End if ; End loop ;
Close cur ;
-- Close file -- UTL_FILE.FCLOSE( LF$Fichier ) ;
End Extraction_Table ; /
|
Examples:
Extraction of the EMP raws to the EMP.TXT file with column header :
SQL> execute extraction_table( 'EMP','EMP.TXT','FICHIERS_OUT' ) ;
Procédure PL/SQL terminée avec succès.
|
EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO
7369,SMITH,CLERK,7902,18/12/1980,880,,20
7499,ALLEN,SALESMAN,7698,21/02/1981,1936,300,30
7521,WARD,SALESMAN,7698,23/02/1981,1375,500,30
7566,JONES,MANAGER,7839,03/04/1981,3273,,20
7654,MARTIN,SALESMAN,7698,29/09/1981,1375,1400,30
7698,BLAKE,MANAGER,7839,02/05/1981,3135,,30
7782,CLARK,MANAGER,7839,10/06/1981,2695,,10
7788,SCOTT,ANALYST,7566,20/04/1987,3300,,20
7839,KING,PRESIDENT,,18/11/1981,5500,,10
7844,TURNER,SALESMAN,7698,09/09/1981,1650,0,30
7876,ADAMS,CLERK,7788,24/05/1987,1210,,20
7900,JAMES,CLERK,7698,04/12/1981,1045,,30
7902,FORD,ANALYST,7566,04/12/1981,3300,,20
7934,MILLER,CLERK,7782,24/01/1982,1430,,10
Extraction of INSERT orders for the EMP table where JOB='CLERK' :
SQL> execute extraction_table( 'EMP','EMP.INS','FICHIERS_OUT', ',', 'I', 'JOB = ''CLERK''' ) ;
Procédure PL/SQL terminée avec succès.
|
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7369,'SMITH','CLERK',7902,to_date('18/12/1980','DD/MM/YYYY'),880,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7876,'ADAMS','CLERK',7788,to_date('24/05/1987','DD/MM/YYYY'),1210,NULL,20 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7900,'JAMES','CLERK',7698,to_date('04/12/1981','DD/MM/YYYY'),1045,NULL,30 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (7934,'MILLER','CLERK',7782,to_date('24/01/1982','DD/MM/YYYY'),1430,NULL,10 );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9991,'Dupontont','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9992,'Duboudin','CLERK',NULL,NULL,NULL,NULL,NULL );
INSERT INTO EMP (EMPNO,ENAME,JOB,MGR,HIREDATE,SAL,COMM,DEPTNO ) VALUES (9994,'Schmoll','CLERK',NULL,NULL,2500,NULL,20 );
Francois