MacLochlainns Weblog

Michael McLaughlin's Technical Blog

Site Admin

Using UTL_FILE Package

with 5 comments

Sometimes I’m surprised. Today, the surprise came when somebody pointed to a potential error in another author’s book. The person who asked the question had to send me a screen shot before I believed it.

The author’s code encounters the following error because the code was designed to loop through a multiple line file, and the code called the UTL_FILE.FOPEN procedure with three instead of four parameters. While it works with only three parameters when the strings are less than or equal to 1,024 (thanks Gary), it throws read errors when a string exceeds the default. You use the fourth parameter when your string exceeds the default length of 1,024.

DECLARE
*
ERROR at line 1:
ORA-29284: file READ error
ORA-06512: at "SYS.UTL_FILE", line 106
ORA-06512: at "SYS.UTL_FILE", line 746
ORA-06512: at line 26

As mentioned, you fix the problem by using the fourth parameter like the call on lines 15 through 18 below:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
DECLARE
  /* Declare local input variables. */
  lv_location      VARCHAR2(60) := 'C:\Data\Direct';
  lv_file_name     VARCHAR2(40) := 'TextFile.txt';
 
  /* Declare a file reference pointer and buffer. */
  lv_file     UTL_FILE.FILE_TYPE;  -- File reference
  lv_line     VARCHAR2(32767);     -- Reading buffer
 
BEGIN
 
  /* Check for open file and close when open. */
  IF utl_file.is_open(lv_file) THEN
    utl_file.fclose(lv_file);
  END IF;
 
  /* Open the file for read-only of 32,767 lines of text.
     The fourth parameter is required when you want to use
     the GET_LINE procedure to read a file line-by-line. */
  lv_file := utl_file.fopen( location     => lv_location
                           , filename     => lv_file_name
                           , open_mode    => 'R'
                           , max_linesize => 32767);
 
  /* Read all lines of a text file. */
  LOOP
    /* Read a line of text, when the eof marker is found 
       the get_line procedure raises a NO_DATA_FOUND 
       error, which is why there's no explicit loop. */
    utl_file.get_line( file   => lv_file
                     , buffer => lv_line );
 
    /* Print the line of text or a line return because
       UTL_FILE doesn't read line returns. */
    dbms_output.put_line(NVL(lv_line,CHR(10)));
 
  END LOOP;
 
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    utl_file.fclose(lv_file);
END;
/

While the foregoing is traditional and uses a physical directory path from the local server’s operating system, you can use a virtual directory from Oracle Database 10g forward. This physical directory is also defined in the utl_file_dir parameter of the database server. A virtual directory hides the physical directory from the application software, which simplifies physical file maintenance when you need to move the files.

3
  lv_location      VARCHAR2(60) := 'DirectVirtualDirectory';

The preceding example works, and I’ll put one out converting the external to CLOB later in the week.

Written by maclochlainn

April 13th, 2014 at 11:46 pm

5 Responses to 'Using UTL_FILE Package'

Subscribe to comments with RSS or TrackBack to 'Using UTL_FILE Package'.

  1. Hi Michael,

    I tried running the anonymous block above for a text file containing the following lines:

    This is line1
    This is line2
    This is line3

    This is the final line

    The anonymous block above worked fine even without specifying the max_linesize parameter in line 18.
    Does the error occur only for specific multi-line data in the file or am I missing something here.
    I am testing on Oracle Database 11g Express Edition Release 11.2.0.2.0

    Sujoy

    14 Apr 14 at 1:25 am

  2. Sujoy,

    I find it works with shorter strings too. As Gary mentions, it fails with strings longer than 1,024, but why don’t you try my test file:

    Two years after the Battle of New York,[4] Steve Rogers lives in Washington, D.C. and works for the espionage agency S.H.I.E.L.D. while adjusting to contemporary society. Rogers, along with the agency's S.T.R.I.K.E. team and agent Natasha Romanoff, is sent to free hostages aboard a S.H.I.E.L.D. vessel from pirates led by mercenary Georges Batroc. Mid-mission, Rogers discovers Romanoff has another agenda: to extract data from the ship's computers for S.H.I.E.L.D. director Nick Fury. Rogers returns to the Triskelion, S.H.I.E.L.D.'s headquarters, to confront Fury and is briefed about Operation Insight: three Helicarriers linked to spy satellites, designed to preemptively eliminate threats. Fury, unable to decrypt Romanoff's recovered data, becomes suspicious about Insight.
     
    On his way to rendezvous with Maria Hill, Fury is ambushed by assailants led by a mysterious assassin called the Winter Soldier. Fury escapes to Rogers' apartment, and warns Rogers that S.H.I.E.L.D. is compromised. After handing Rogers a flash drive containing data from the ship, Fury is gunned down by the Winter Soldier. Fury dies in surgery, and Hill recovers the body. The next day, senior S.H.I.E.L.D. official Alexander Pierce summons Rogers to the Triskelion. When Rogers withholds Fury's information, Pierce brands him a fugitive. Hunted by the agency, Rogers meets with Romanoff. Using data in the flash drive they discover a secret S.H.I.E.L.D. bunker in New Jersey, where they activate a supercomputer containing the preserved consciousness of Arnim Zola. Zola reveals that since S.H.I.E.L.D. was founded after World War II, HYDRA has secretly operated within its ranks, sowing global chaos in the hope that humanity would willingly surrender its freedom in exchange for security. The pair narrowly escape death when a S.H.I.E.L.D. missile destroys the bunker.
     
    Rogers and Romanoff enlist the help of Sam Wilson, a former military pararescueman whom Rogers befriended, and acquire his powered "Falcon" wingpack. Deducing S.H.I.E.L.D. agent Jasper Sitwell is a HYDRA mole, they force him to divulge that Zola developed a data-mining algorithm that can identify individuals who might become future threats to HYDRA's plans. The Insight Helicarriers will sweep the globe, using satellite-guided guns to eliminate these individuals. En route to S.H.I.E.L.D. headquarters, Rogers, Romanoff, and Wilson are ambushed by the Winter Soldier and Sitwell is thrown into oncoming traffic. During the fight, Rogers recognizes the Winter Soldier as Bucky Barnes, his World War II comrade, before being captured by S.H.I.E.L.D. Hill rescues them and leads them to a safehouse where Fury, who had faked his death, is waiting with plans to sabotage the Helicarriers by replacing their controller chips.
     
    After members of the World Security Council arrive for the Helicarriers' launch, Rogers reveals HYDRA's plot to everyone at the Triskelion. Romanoff, disguised as one of the Council members, disarms Pierce. Fury arrives and forces Pierce to unlock access to S.H.I.E.L.D's database so Romanoff can expose HYDRA by leaking classified information. After a struggle, Fury shoots Pierce dead. Meanwhile, Rogers and Wilson storm two Helicarriers and replace the controllers, but the Winter Soldier destroys Wilson's suit and fights Rogers on the third. Rogers fends him off and replaces the final chip, allowing Hill to take control and have the vessels shoot down each other. Rogers falls from the Helicarrier as it crashes into the Triskelion, where Wilson fights double agent Rumlow, the S.T.R.I.K.E. team's leader. Rogers lands in the Potomac River and is rescued by the Winter Soldier, who then disappears. With S.H.I.E.L.D. in disarray, Fury, under the cover of his apparent death, heads to Eastern Europe in pursuit of HYDRA's remaining cells as Romanoff appears before a Senate subcommittee. Rogers and Wilson decide to track down the Winter Soldier with information from Romanoff on the Winter Soldier program.
     
    In a mid-credits scene, Baron von Strucker, at a HYDRA lab, proclaims the "age of miracles" has begun as scientists examine an energy-filled scepter1 and two prisoners: one with superhuman speed, the other with telekinetic powers. In a post-credits scene, the Winter Soldier visits a Captain America exhibit at the Smithsonian Institution.

    I added this to the code, to identify the read position when it fails with a three parameter call:

    37
    38
    39
    
      WHEN UTL_FILE.READ_ERROR THEN
        dbms_output.put_line('Position ['||utl_file.fgetpos(lv_file)||']');
        RETURN;

    It returns:

    POSITION [782]

    Let me know what you find, eh? BTW, it was a test on Oracle Database 12c (12.1.0.1.0) in a pluggable database. Thanks.

    maclochlainn

    14 Apr 14 at 2:05 am

  3. Documented feature. If you don’t supply the fourth parameter, it defaults to 1024. If you have a line greater than that, it will error out.

    http://docs.oracle.com/cd/B19306_01/appdev.102/b14258/u_file.htm#i1003526

    Maximum number of characters for each line, including the newline character, for this file (minimum value 1, maximum value 32767). If unspecified, Oracle supplies a default value of 1024.

    Gary

    14 Apr 14 at 5:42 am

  4. Thanks for the clarification Gary and Michael.
    I was able to reproduce the error.
    Thanks to you, I will now remember to add the fourth parameter.
    Hoping that none of my older codes do not run into any lines > 1024 :)

    Sujoy

    15 Apr 14 at 11:19 am

  5. […] a best practice approach to reading external files with utl_file […]

Leave a Reply