Overblog Suivre ce blog
Editer l'article Administration Créer mon blog

Search

Free tool

Look and Feel Project

9 octobre 2005 7 09 /10 /octobre /2005 00:00

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

Partager cet article

Repost 0
Francois Degrelle - dans Oracle PL-SQL
commenter cet article

commentaires

Ira 04/02/2009 16:53

Found it by google.Great, thanks!

zizo 31/01/2007 16:13

Interesting  blog, I have used a couple of functions from it.Thank You

Francois Degrelle 31/01/2007 16:22

You're welcome  ;o)