Re: PL/SQL and input files

From: <andreas.prusch_at_sintec.de>
Date: Sun, 24 May 1998 07:06:30 GMT
Message-ID: <6k8gtm$f7t$1@nnrp1.dejanews.com>

The following procedure expects the the first column is padded up with blank to 15 char, the second column is padded up with blanks to 30 columns and the third is padded up with blanks to 30 columns.

CREATE OR REPLACE
PROCEDURE Import(Filename VARCHAR2 := 'INPUT.DAT') IS

  TYPE REC_TYP IS RECORD (no NUMBER, text1 VARCHAR2(100), text2 VARCHAR2(100));
  rec REC_TYP;
  ExpFile UTL_FILE.FILE_TYPE;
  FileBuf VARCHAR2(2000);

BEGIN   ExpFile := UTL_FILE.FOPEN('C:\UTLF', Filename, 'r');

  LOOP
    BEGIN

      UTL_FILE.GET_LINE(ExpFile, FileBuf);
      rec.no       := TO_NUMBER(RTRIM(SUBSTR(FileBuf,1,15)));
      rec.text1    := RTRIM(SUBSTR(FileBuf,16,30));
      rec.text2    := RTRIM(SUBSTR(FileBuf,46,30));

    EXCEPTION
      WHEN NO_DATA_FOUND THEN
        EXIT;

    END;
  END LOOP;   UTL_FILE.FCLOSE(ExpFile);

END;
/

mfg
Andreas Prusch

In article <6k7oa3$4b$1_at_camel29.mindspring.com>,   "Kevin Bass" <akil1_at_mindspring.com> wrote:
>
> Is it possible to read the data from an input file into a PL/SQL block?. An
> example would like a file with the format of:
>
> 123456789 Doe John
> 987654321 Doe Jane
>
> Could this data be read into a PL/SQL block and used within the block?
>
> Kevin
>
>

-----== Posted via Deja News, The Leader in Internet Discussion ==----- http://www.dejanews.com/ Now offering spam-free web-based newsreading Received on Sun May 24 1998 - 02:06:30 CDT