3 Replies Latest reply: Sep 25, 2009 8:50 AM by 107255 RSS

    UTL_FILE.PUT_LINE limited to 8192 characters

    107255
      I have a packaged procedure that extracts data from the database and puts it into a file using UTL_FILE. The procedure worked consistently under Windows (DB ver 10.0.2.3.0) but fails with the error "ORA-06502: PL/SQL: numeric or value error" after I ported the application over to Mac OS X (10.0.2.4.0).

      The line number for the error corresponds with a call to UTL_FILE.PUT_LINE. I knew that some of the lines were fairly "wide" (over 10000 chars) so I suspected that the line size limit under OS X might be smaller than on Windows (despite me using UTL_FILE.FOPEN(dir_name, file_name, 'W', 32000);). So I created a test script which wrote progressively longer lines using UTL_FILE.PUT_LINE in a loop: i.e.
      X
      XX
      XXX
      XXXX
      etc.

      The error occurred when the loop counter hit 8192 chars (i.e. 8191 X's + a newline char).
      The characterset is the default (I created the database during the s/w installation) so I'm not using a multibyte char set.

      I was wondering whether anyone else has had the same problem and if so, how do you overcome/workaround it?

      Many thanks in advance,
      Graham J Bailey