ORA-29275 exporting to Excel file

damir.vadas_250 · November 10, 2013, 2:18am

Hi!

Mine conf:
Toad 12.5.0..4 beta x64
Windows 7 x64 SP1
Oracle client: 11.2.0.3 EE x64
MS Office 2013 x64

When I want to export data from query to excel file, on first export try I get error:

When I look in file system, I see 4k Excel file created and blank.

When I close Export Dataset window and repeat the whole things again, export is successful and Excel file is created.

To test and see mine env here are some useful data:

NLS_DATABASE_PARAMETERS
PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CHARACTERSET UTF8 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 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_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE
NLS_RDBMS_VERSION 11.2.0.3.0

NLS_SESSION_PARAMETERS

PARAMETER VALUE
NLS_LANGUAGE AMERICAN
NLS_TERRITORY AMERICA
NLS_CURRENCY NLS_ISO_CURRENCY AMERICA NLS_NUMERIC_CHARACTERS ., NLS_CALENDAR GREGORIAN NLS_DATE_FORMAT dd.mm.yyyy hh24:mi:ss NLS_DATE_LANGUAGE AMERICAN NLS_SORT BINARY NLS_TIME_FORMAT HH.MI.SSXFF AM 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

Table definition which was used in export:
CREATE TABLE AUX.UPD_LOG

wimdelange_062 · November 10, 2013, 10:54pm

What happens if you run the query and paging down? I would expect that when you are around the 1000 records you get the same error?

Groetjes,

Wim

google.com/+WimdeLange

2013/11/10 damir.vadas_531 bounce-damirvadas_531@toadworld.com

ORA-29275 exporting to Excel file

Thread created by damir.vadas_531
Hi!

Mine conf:
Toad 12.5.0..4 beta x64
Windows 7 x64 SP1
Oracle client: 11.2.0.3 EE x64
MS Office 2013 x64

When I want to export data from query to excel file, on first export try I get error:

When I look in file system, I see 4k Excel file created and blank.

When I close Export Dataset window and repeat the whole things again, export is successful and Excel file is created.

To test and see mine env here are some useful data:

NLS_DATABASE_PARAMETERS
PARAMETER VALUE
NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA
NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,

NLS_CHARACTERSET UTF8
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
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_CHARACTERSET AL16UTF16
NLS_NCHAR_CONV_EXCP FALSE

NLS_RDBMS_VERSION 11.2.0.3.0

NLS_SESSION_PARAMETERS

PARAMETER VALUE
NLS_LANGUAGE AMERICAN

NLS_TERRITORY AMERICA
NLS_CURRENCY $

NLS_ISO_CURRENCY AMERICA
NLS_NUMERIC_CHARACTERS .,

NLS_CALENDAR GREGORIAN
NLS_DATE_FORMAT dd.mm.yyyy hh24:mi:ss

NLS_DATE_LANGUAGE AMERICAN
NLS_SORT BINARY

NLS_TIME_FORMAT HH.MI.SSXFF AM
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

Table definition which was used in export:
CREATE TABLE AUX.UPD_LOG
(
ID NUMBER(6),
CREATOR VARCHAR2(32 BYTE) CONSTRAINT UPD_LOG_C02 NOT NULL,
EXECUTOR VARCHAR2(32 BYTE) CONSTRAINT UPD_LOG_C04 NOT NULL,

EXECUTE_DATE DATE DEFAULT sysdate CONSTRAINT UPD_LOG_C05 NOT NULL,
STATUS VARCHAR2(32 BYTE) DEFAULT 'STARTED' CONSTRAINT UPD_LOG_C07 NOT NULL,
COMMENTS VARCHAR2(4000 BYTE) CONSTRAINT UPD_LOG_C08 NOT NULL,

SESSION_ID NUMBER,
MR_ID NUMBER(19),
SVN_LINK VARCHAR2(1000 BYTE),
FLOW VARCHAR2(100 BYTE)
)
RESULT_CACHE (MODE DEFAULT)
PCTUSED 0
PCTFREE 10
INITRANS 1

MAXTRANS 255
LOGGING
NOCOMPRESS
NOCACHE
NOPARALLEL
MONITORING;

I can resend data as well if you give me a proper place to put 1 MB of unzipped data

Brg,

Damir Vadas

To reply, please reply-all to this email.

Stop receiving emails on this subject.

Or Unsubscribe from Toad for Oracle - Beta notifications altogether.

Toad for Oracle - Beta Discussion Forum

Flag this post as spam/abuse.

damir.vadas_250 · November 11, 2013, 6:38am

What happens if you run the query and paging down?

I do not follow you at all..

8(

JohnDorlon · November 11, 2013, 6:43am

He means “run the query in the editor and just scroll down in the grid”. I don’t think this error has anything to do with exporting to excel. It’s interesting that it works the 2nd time.

I also wonder if the error has anything to do with Toad. Does the error come up when running from SQLPlus?

damir.vadas_250 · November 11, 2013, 8:08am

run the query in the editor and just scroll down in the grid

Produces the same error ... wov!
In that case I get first ora like on the picture and followed by second small error:
11-11-2013- 17-06-50.png

And yes, second run in Toad export action gave me no error (tested several times).

Brg,
Damir Vadas

P.S.
If you need any additional information, let me know I'll send to you no problems ...

Bert_Scalzo · November 11, 2013, 8:15am

Might be worth reading:

http://vibhork.blogspot.com/2011/02/fix-of-ora-29275-partial-multibyte.html

damir.vadas_250 · November 11, 2013, 8:18am

Hi

I think I found a way to overcome that (in the same time I was advised to go to link)

The problem may be reproduced on showing one single VARCHAR2 (32 BYTE) column:
select executor cc1 from aux.upd_log order by id;

But when I write:
select CONVERT(executor,‘UTF8’, ‘AL32UTF8’) cc1 from aux.upd_log order by id;
all works like a charm (export, first, last … everything).

Another problem is that sometimes after F4, and showing data with some filter I get the same error … and this I cannot overcome by any other convert command because is in the Toad directly …

8(

Brg,
Damir Vadas

JohnDorlon · November 11, 2013, 8:20am

Well, it seems that the problem is in the data, not Toad. If you need to work around it as above - can you make a view with the CONVERT functions in place, then use the view instead of the table?

damir.vadas_250 · November 11, 2013, 10:53am

hi

thx for your answer … but maybe you haven’t seen mine last post…

Another problem is that after F4 on some table, and showing data with some predefined filter I get the same error … and this I cannot overcome by any other convert command because is in the Toad directly …

Regardless, this might not be a Toad error, Is there any way to overcome that?

Brg

Damir Vadas

JohnDorlon · November 11, 2013, 11:01am

For the filters, the only information that we store is the “where” and “order by” clauses, so maybe those filters are brining the data to the problem rows into the first fetch.

That’s actually why I was suggesting the view - if you use the CONVERT functions as part of the view’s definition, then F4 on the view instead of the table, will that prevent the error?

I don’t know of a way to do anything about it in the describe table dialog, sorry.

damir.vadas_250 · November 11, 2013, 11:02am

Hi

Trick vith view will do the task …
Brg,

Damir Vadas