title: UTL_FILE in pl/pgsql
body: <p>Can someone explain to me how to use UTL_FILE package with PostgreSQL (pl/pgsql). Every time I do a query I have this error</p>
  
  <blockquote>
    <p>missing FROM clause entry for table "utl_file"`:</p>
  </blockquote>
  
  <p>This is my code:</p>
  
  <pre><code>CREATE OR REPLACE FUNCTION xxxxxx(
      IN strficname text,
      IN lnguser bigint,
      IN lngproduct bigint,
      IN lnginsurer bigint,
      IN lngregion bigint,
      OUT intstat1 bigint,
      OUT intstat2 bigint,
      OUT intstat3 bigint,
      OUT intstat4 bigint,
      OUT intstat5 bigint,
      OUT intstat6 bigint,
      OUT intstat7 bigint,
      OUT intstat8 bigint,
      OUT intstat9 bigint,
      OUT strrejfilename text)
    RETURNS record AS
  $BODY$
  DECLARE
  
  
    strDirName      varchar(30)              := 'SGINSURANCE_DIR_SOURCE';
    strLogDirName   varchar(30)              := 'SG_DIR_ARCHIVE_LOG_INT';
    strRejDirName   varchar(30)              := 'SG_DIR_ARCHIVE_PROD_REJ';
    strProdDirName  varchar(30)              := 'SG_DIR_ARCHIVE_PROD_INT';
    oCopy           UTL_FILE.FILE_TYPE ;
    oFile           UTL_FILE.FILE_TYPE ;
    oLog            UTL_FILE.FILE_TYPE ;
    oReject         UTL_FILE.FILE_TYPE ;
    strLine           varchar(32767) ;
    strMsg          varchar(256) ;
    -- oExcept         Exception ;
    datIntegration  tbl_integration.int_integrationdate%type;
    lngRecord       bigint;
    strLogName      varchar(200);
    strRejectName   varchar(200);
    intResult       integer;
    lngReject       bigint;
    lngUpdate       bigint;
    lngInsert       bigint;
    datFile         timestamp;
    strProfile      varchar(200);
  
    lngSequence           bigint;
  
  
  BEGIN
  
    delete from tbl_rejection where rej_user = lngUser and rej_integration is null;
  
    -- Init statistics
    intStat2 := 0;
    intStat3 := 0;
    intStat4 := 0;
    intStat5 := 0;
    intStat6 := 0;
    intStat7 := 0;
    intStat8 := 0;
    intStat9 := 0;
  
    -- Open file
    Begin
      oFile := UTL_FILE.FOPEN( strDirName, strFicName, 'R', 32764 ) ;
    Exception
      When OTHERS Then
        strMsg := SQLERRM || ' [' || strDirName || '] -&gt; ' || strFicName;
        Raise  ;
    End ;
  -- Calculate file date
    begin
      select to_date(f_convertdate_d2(substring(strFicName from 8 for 10)),'YYYYMMDD') into STRICT  datFile ;
     exception
      when others then
        datFile := date_trunc('day', LOCALTIMESTAMP);
    end;
  
    -- Creating log file
    Begin
      select substr(strFicName,1,length(strFicName)-4) || '_log_' || to_char(LOCALTIMESTAMP,'YYYYMMDDHH24MISS') || '.txt'  into strLogName ;
      oLog := UTL_FILE.FOPEN( strLogDirName, strLogName, 'W', 32764 ) ;
    Exception
      When OTHERS Then
        strMsg := SQLERRM || ' [' || strLogDirName || '] -&gt; Log file';
        Raise  ;
    End ;
  
    -- Creating reject log file
    Begin
      select substr(strFicName,1,length(strFicName)-4) || '_blocked_records.txt'  into strRejectName ;
      oReject := UTL_FILE.FOPEN( strRejDirName, strRejectName, 'W', 32764 ) ;
    Exception
      When OTHERS Then
        strMsg := SQLERRM || ' [' || strRejDirName || '] -&gt; Reject file';
        Raise  ;
    End ;
    strRejFileName := strRejectName;
  
    -- Creating copy file
    Begin
      oCopy := UTL_FILE.FOPEN( strProdDirName, strFicName, 'W', 32764 ) ;
    Exception
      When OTHERS Then
        strMsg := SQLERRM || ' [' || strProdDirName || '] -&gt; Copy file';
        Raise  ;
    End ;
  
    -- Read file
    Begin
      -- init log file
      select utl_file.put_line ( oLog , 'Process file name : ' || strFicName ) ;
      select utl_file.put_line ( oLog , 'Process date : ' || to_char ( LOCALTIMESTAMP , 'YYYY-MM-DD HH24:MI:SS' ) ) ;
      begin
        select prf_label into strProfile
          from tbl_profile , tbl_user
          where prf_code = usr_profile
          and usr_id = lngUser;
      exception
        when others then
          strProfile := '';
      end;
      select utl_file.put_line ( oLog , 'User profile : ' || strProfile ) ;
      select utl_file.put_line ( oLog , '----------------------------------------------------' ) ;
  
      -- Test previous integration
      if POSITION('_BLOCKED_RECORDS' in upper(strFicName)) = 0 then
        begin
          select int_integrationdate into datIntegration
            from tbl_integration
            where int_filename = strFicName
            and int_filetype = 'RFB_CASCO';
          strMsg := 'File already integrated on ' || to_char(datintegration,'YYYY-MM-DD');
          intStat1 := 0;
          Raise  ;
        exception
          when NO_DATA_FOUND then
            null ;
        end;
      end if;
  
      intStat1 := 1;
  
      lngRecord := 0;
      lngReject := 0 ;
      lngUpdate := 0 ;
      lngInsert := 0 ;
  
      Loop
        select UTL_FILE.GET_LINE( oFile , strLine ) ;
        select utl_file.put_line ( oCopy , strLine ) ;
        select trim(strLine) into STRICT  strLine ;
        if POSITION(';|' in strLine) &gt; 0 then
          strLine := substr(strLine,1,POSITION(';|' in strLine) - 1);
        end if;
        lngRecord := lngRecord + 1;
        if strLine is not null then
          intResult := 0;
          select xxxxxx ( strLine , lngRecord , strFicName , oLog , oReject , lngUser , lngProduct , lngInsurer , lngRegion , datFile , intResult );
  
          if intResult = 0 then
            lngReject := lngReject + 1 ;
          elsif intResult = 1 then
            lngUpdate := lngUpdate + 1 ;
          else
            lngInsert := lngInsert + 1 ;
          end if;
        end if;
      End loop ;
  
     Exception
  
       When NO_DATA_FOUND Then
          select UTL_FILE.FCLOSE( oFile ) ;
          select UTL_FILE.FCLOSE( oCopy ) ;
  
          select utl_file.put_line ( oLog , '----------------------------------------------------' ) ;
          select utl_file.put_line ( oLog , 'Number of total records processed : ' || lngRecord ) ;
          select utl_file.put_line ( oLog , 'Number of records rejected : ' || lngReject ) ;
          select utl_file.put_line ( oLog , 'Number of accounts updated : ' || lngUpdate ) ;
          select utl_file.put_line ( oLog , 'Number of accounts inserted : ' || lngInsert ) ;
  
          select UTL_FILE.FCLOSE( oLog ) ;
          select UTL_FILE.FCLOSE( oReject ) ;
  
          -- Save integration
          insert into tbl_integration ( int_filename , int_integrationdate , int_filetype , int_filedate) values ( strFicName , LOCALTIMESTAMP , 'RFB_CASCO' , datFile);
  
          select currval('seq_integration') into lngSequence ;
  
          update tbl_integration
            set int_productselected = 'RFB_CASCO'
            , int_registername = strFicName
            , int_numrecordregister = lngRecord
            , int_numrecordintegrated = lngInsert + lngUpdate
            , int_user = lngUser
            , int_login = (SELECT usr_login from tbl_user where usr_id = lngUser)
            where int_id = lngSequence;
  
          update tbl_rejection
            set rej_integration = lngSequence
            , rej_login = (SELECT usr_login from tbl_user where usr_id = lngUser)
            where rej_integration is null
            and rej_user = lngUser;
  
          commit;
    End;
  
    -- update tbl_contract set cnt_status='C' where cnt_status='I' and cnt_ClosingDate &lt; sysdate and cnt_product not in (select prd_id from tbl_product where prd_name in ('BSGV_Quietis','BSGV_Quietis_Payroll','BSGV_Quietis_Premium','BSGV_Confidence'));
  
    commit;
  
    -- Creating process log file
    Begin
      oLog := UTL_FILE.FOPEN( strLogDirName, 'Integrations archive_log_' || to_char(LOCALTIMESTAMP,'YYYYMMDD') || '.txt', 'A', 32764 ) ;
    Exception
      When OTHERS Then
        strMsg := SQLERRM || ' [' || strLogDirName || '] -&gt; Process log file';
        Raise  ;
    End ;
  
    -- Filling log file
    select utl_file.put_line ( oLog , 'Process file name: RFB_CASCO' );
    select utl_file.put_line ( oLog , 'Process date: ' || to_char(LOCALTIMESTAMP,'YYYY-MM-DD HH24:MI:SS') );
    begin
      select usr_login into strline from tbl_user where usr_id = lngUser;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , 'User id: ' || strLine );
    select utl_file.put_line ( oLog , 'Filename:  ' || strFicName );
    begin
      select par_value_txt into strLine from tbl_parameter where par_code = 1;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , 'Original directory: ' || strLine );
    begin
      select par_value_txt into strLine from tbl_parameter where par_code = 15;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , 'Final directory: ' || strLine );
  
    select utl_file.put_line ( oLog , '---' );
    begin
      select par_value_txt into strLine from tbl_parameter where par_code = 15;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , substr(strFicName,1,length(strFicName)-4) || ' source file is successfully auto archived into the parameterized ' || strLine || ' directory');
  
    begin
      select par_value_txt into strLine from tbl_parameter where par_code = 10;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , substr(strLogName,1,length(strLogName)-4) || ' log file is successfully auto archived into the parameterized ' || strLine || ' directory');
  
    begin
      select par_value_txt into strLine from tbl_parameter where par_code = 14;
    exception
      when others then
        strLine := '';
    end;
    select utl_file.put_line ( oLog , substr(strRejectName,1,length(strRejectName)-4) || ' rejection file is successfully auto archived into the parameterized ' || strLine || ' directory');
    select utl_file.put_line ( oLog , '---' );
  
    select utl_file.put_line ( oLog , 'Number of created files: 2');
    select utl_file.put_line ( oLog , 'Number of copied files: 1');
    select utl_file.put_line ( oLog , 'Number of moved files : 0');
    select utl_file.put_line ( oLog , '%-----------------------------------------------------------------------%' );
    select utl_file.put_line ( oLog , '%-----------------------------------------------------------------------%' );
  
    select utl_file.fclose ( oLog );
  
  Exception
     When others Then
       select UTL_FILE.FCLOSE_ALL ;
       rollback ;
       RAISE;
  
  End;
  $BODY$
    LANGUAGE plpgsql VOLATILE SECURITY DEFINER
    COST 100;
  </code></pre>