Home » SQL & PL/SQL » SQL & PL/SQL » UTL_FILE and very strange ORA-29285 (Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production, Unix)
UTL_FILE and very strange ORA-29285 [message #530584] Wed, 09 November 2011 03:23 Go to next message
korro
Messages: 2
Registered: November 2011
Location: Poland
Junior Member
Hello.
From few days I'm trying to figure out where is problem, maybe you will help me.

I'm about to write ~1m records to text file. DB charset is WE8ISO8859P1. I have German special characters in data. During data processing (writing file) I'm getting ORA-29285 (file writing error) on lines with char ä (Latin small letter a with diaeresis) - chr(228). For all others special characters it's ok.

Most strangest thing is, that when I concat this column with space (concat(name, ' ')) there is no error.
chr(228) is not last char in 'name' column.

After research, I can see, that error is raised not during put_line or put, but on new_line function.

Can You please give me some advice, what more can I try?
Thanks in advance.

Report message to a moderator

Re: UTL_FILE and very strange ORA-29285 [message #530592 is a reply to message #530584] Wed, 09 November 2011 03:53 Go to previous messageGo to next message
muralikri
Messages: 638
Registered: August 2011
Location: chennai
Senior Member
Can you post the code?

Report message to a moderator

Re: UTL_FILE and very strange ORA-29285 [message #530594 is a reply to message #530584] Wed, 09 November 2011 03:56 Go to previous messageGo to next message
Michel Cadot
Messages: 68775
Registered: March 2007
Location: Saint-Maur, France, https...
Senior Member
Account Moderator
Are you sure you have nothing in UTF8 somewhere?
For instance, on the process environment that started the instance.

Regards
Michel

Report message to a moderator

Re: UTL_FILE and very strange ORA-29285 [message #530595 is a reply to message #530592] Wed, 09 November 2011 03:57 Go to previous messageGo to next message
korro
Messages: 2
Registered: November 2011
Location: Poland
Junior Member
Quote:
Can you post the code?

Code is from Ask TOM:
/* Formatted on 09.11.2011 10:54:20 (QP5 v5.139.911.3011) */
CREATE OR REPLACE FUNCTION dump_csv (p_query       IN VARCHAR2,
                                     p_separator   IN VARCHAR2 DEFAULT ',',
                                     p_dir         IN VARCHAR2,
                                     p_filename    IN VARCHAR2)
   RETURN VARCHAR2
IS
   l_output        UTL_FILE.file_type;
   l_theCursor     INTEGER DEFAULT DBMS_SQL.open_cursor;
   l_columnValue   VARCHAR2 (2000);
   l_status        INTEGER;
   l_colCnt        NUMBER DEFAULT 0;
   l_separator     VARCHAR2 (10) DEFAULT '';
   l_cnt           NUMBER DEFAULT 0;
   l_err           NUMBER DEFAULT 0;
BEGIN
   l_output := UTL_FILE.fopen (p_dir, p_filename, 'w');

   DBMS_SQL.parse (l_theCursor, p_query, DBMS_SQL.native);

   FOR i IN 1 .. 255
   LOOP
      BEGIN
         DBMS_SQL.define_column (l_theCursor,
                                 i,
                                 l_columnValue,
                                 2000);
         l_colCnt := i;
      EXCEPTION
         WHEN OTHERS
         THEN
            IF (SQLCODE = -1007)
            THEN
               EXIT;
            ELSE
               RAISE;
            END IF;
      END;
   END LOOP;

   DBMS_SQL.define_column (l_theCursor,
                           1,
                           l_columnValue,
                           2000);

   l_status := DBMS_SQL.execute (l_theCursor);

   LOOP
      EXIT WHEN (DBMS_SQL.fetch_rows (l_theCursor) <= 0);
      l_separator := '';

      FOR i IN 1 .. l_colCnt
      LOOP
         DBMS_SQL.COLUMN_VALUE (l_theCursor, i, l_columnValue);
         UTL_FILE.put (l_output, l_separator || l_columnValue);
         l_separator := p_separator;
      END LOOP;

      UTL_FILE.new_line (l_output);
      l_cnt := l_cnt + 1;
   END LOOP;

   DBMS_SQL.close_cursor (l_theCursor);

   UTL_FILE.fclose (l_output);

   RETURN 'ok: ' || l_Cnt || ',err: ' || l_err;
END dump_csv;

Sample data:
Miete Endgerät


Michel Cadot wrote on Wed, 09 November 2011 10:56
Are you sure you have nothing in UTF8 somewhere?

I'm sure. As far as my co-workers said, it was ok before migrating to Exadata.


One more tip. If I put to file only chr(228) (select chr(228) from table) it works fine, but when I concat chr(228) with any other character (select concat(chr(228), 'f' ) from table) I'm getting ORA-29285.

[Updated on: Wed, 09 November 2011 04:59]

Report message to a moderator

Re: UTL_FILE and very strange ORA-29285 [message #530639 is a reply to message #530595] Wed, 09 November 2011 08:17 Go to previous message
Solomon Yakobson
Messages: 3305
Registered: January 2010
Location: Connecticut, USA
Senior Member
I can't reproduce it:

SQL> select * from v$version
  2  /

BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE    11.2.0.1.0      Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production

SQL> select * from nls_session_parameters
  2  /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE

17 rows selected.

SQL> select * from nls_database_parameters
  2  /

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_LANGUAGE                   AMERICAN
NLS_TERRITORY                  AMERICA
NLS_CURRENCY                   $
NLS_ISO_CURRENCY               AMERICA
NLS_NUMERIC_CHARACTERS         .,
NLS_CHARACTERSET               WE8MSWIN1252
NLS_CALENDAR                   GREGORIAN
NLS_DATE_FORMAT                DD-MON-RR
NLS_DATE_LANGUAGE              AMERICAN
NLS_SORT                       BINARY
NLS_TIME_FORMAT                HH.MI.SSXFF AM

PARAMETER                      VALUE
------------------------------ ----------------------------------------
NLS_TIMESTAMP_FORMAT           DD-MON-RR HH.MI.SSXFF AM
NLS_TIME_TZ_FORMAT             HH.MI.SSXFF AM TZR
NLS_TIMESTAMP_TZ_FORMAT        DD-MON-RR HH.MI.SSXFF AM TZR
NLS_DUAL_CURRENCY              $
NLS_COMP                       BINARY
NLS_LENGTH_SEMANTICS           BYTE
NLS_NCHAR_CONV_EXCP            FALSE
NLS_NCHAR_CHARACTERSET         AL16UTF16
NLS_RDBMS_VERSION              11.2.0.1.0

20 rows selected.

SQL> EXEC dbms_output.put_line(dump_csv('select concat(chr(228),''f'') from dual',',','TEMP','x.txt'));
ok: 1,err: 0

PL/SQL procedure successfully completed.

SQL> host notepad c:\temp\x.txt

SQL>


Notepad displays äf.

SY.

Report message to a moderator

Previous Topic: column value multiplication in sql
Next Topic: Working with Objects
Goto Forum:
  

[ Syndicate this forum (XML) ] [ RSS ]

Current Time: Tue Mar 03 04:21:34 CST 2026
.:: Forum Home :: Blogger Home :: Wiki Home :: Contact :: Privacy ::.