Using 11.0.1. A result of COMPRESS function is stored as long varchar. That solution worked for years. Recently, some records were found to be corrupt, that is, DECOMPRESS fails. Please advise if storing a compressed text in a long varchar field could be the culprit.

asked 17 Apr '13, 12:13

Igor's gravatar image

Igor
46114
accept rate: 0%

edited 17 Apr '13, 12:15


The output of COMPRESS is binary data so you should be using a LONG BINARY column to store the compressed data. When storing the binary data in a LONG VARCHAR column the server will assume that the data is in the character set of the database, ... so it is conceivable that bytes that fall outside of the database character set could be mangled in some way. Having said that, I can't think of any low level operation within the server that would 'look' at the characters? ... but there may be one that I am not aware of.

Note that using a long varchar blob to store binary data will cause more overhead in the server because, by default, a long varchar column will build a character index for any blob larger than 8 pages.


Hmmm, I guess the low level operation that builds the character index is looking at the character data... so if your character set is multibyte I can imagine that the last byte or two in the blob could be mangled if it was not a 'well formed' character.

permanent link

answered 17 Apr '13, 12:23

Mark%20Culp's gravatar image

Mark Culp
23.3k9132274
accept rate: 40%

edited 17 Apr '13, 12:29

Thanks Mark.

(18 Jul '13, 20:56) Igor
1

@Mark: I always assumed that mangling would only occur when the varchar-holding-binary-data crossed the client-server boundary and was subject to all the stuff that happens there (transalation etc).

Now you are saying that, depending on what a particular byte value actually is, INSERT, UPDATE, SELECT, COMPRESS and DECOMPRESS operations inside the server could mess that byte up?

Methinks I'm gonna switch away from VARCHAR if I can't trust it to protect my ones and zeros :)

alt text

(19 Jul '13, 06:41) Breck Carter
Replies hidden

In my understanding guessing, the internal index would only be modified during INSERT and UPDATE, so only these operations might be suspicious here...

I always assumed that mangling would only occur...

That's been my strong assupmtion, as well. Oh well...

(19 Jul '13, 06:58) Volker Barth
Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

Question tags:

×10

question asked: 17 Apr '13, 12:13

question was seen: 912 times

last updated: 19 Jul '13, 06:58