Oracle 10:使用HEXTORAW填充Blob数据 [英] Oracle 10: Using HEXTORAW to fill in blob data

问题描述

我们在Oracle中有一个带有BLOB列的表，该表需要填充少量的任意字节数据-我们永远不会放入超过4000字节的数据.

We have a table in Oracle with a BLOB column that needs to be filled with a small amount of arbitrary byte data--we will never put in more than 4000 bytes of data.

我正在使用现有的基于C ++ OCI的基础结构，这使得在某些情况下使用绑定变量极为困难，因此我仅需使用简单的查询来填充此BLOB列. (我们正在努力使其现代化，但这不是今天的选择，)

I am working with an existing C++ OCI-based infrastructure that makes it extremely difficult to use bind variables in certain contexts, so I need to populate this BLOB column using only a simple query. (We are working to modernize it but that's not an option today,)

我们对这样的查询感到幸运:

We had some luck with a query like this:

UPDATE MyTable
   SET blobData = HEXTORAW('0EC1D7FA6B411DA5814...lots of hex data...0EC1D7FA6B411DA5814')
 WHERE ID = 123;

起初，这很好用.但是，最近我们遇到了需要放入2000字节以上数据的情况.此时，我们遇到一个Oracle错误ORA-01704: string literal too long，因为传递给HEXTORAW的字符串超过4000个字符.我尝试将字符串拆分，然后与||串联，但这并没有闪避错误.

At first, this was working great. However, recently we encountered a case where we need to put in more than 2000 bytes of data. At this point, we hit an Oracle error, ORA-01704: string literal too long because the string being passed to HEXTORAW was over 4000 characters. I tried splitting up the string and then concatenating with ||, but this didn't dodge the error.

因此，我需要一种方法来更新此列，并使用简单的查询将其填充超过2000字节的数据.是否有可能?

So, I need a way to update this column and fill it with more than 2000 bytes' worth of data using a simple query. Is it possible?

(我知道如果我可以使用绑定变量将是微不足道的-实际上与该表交互的其他应用程序都使用该精确技术-但不幸的是，我无法在此处重构数据库胆量.只需要将数据放入表中即可.)

(I know if I had bind variables at my disposal it would be trivial--and in fact other apps which interact with this table use that exact technique--but unfortunately I am not in a position to refactor the DB guts here. Just need to get data into the table.)

一种行不通的有前途的方法是连接RAW:

One promising approach that didn't work was concatenating RAWs:

UTL_RAW.CONCAT(HEXTORAW('...'), HEXTORAW('...'), HEXTORAW('...'))

这回避了字符串长度限制，但是看来Oracle在RAW的长度上也具有匹配的内部2000字节限制.所以我不能用RAW填充blob.也许有一个函数可以将多个RAW连接成一个BLOB.

This dodges the string-length limit, but it appears that Oracle also has a matching internal 2000 byte limit on the length of a RAW. So I can't populate the blob with a RAW. Maybe there is a function that concatenates multiple RAWs into a BLOB.

推荐答案

显然，如果使用PL/SQL，则可以超过这些限制.如果您直接在UPDATE语句中执行HEXTORAW也不起作用-它需要在单独的语句中完成，例如:

Apparently you can exceed these limits if you use PL/SQL. It doesn't work if you do the HEXTORAW within the UPDATE statement directly, either--it needs to be done in a separate statement, like this:

DECLARE
  buf RAW(4000); 
BEGIN
  buf := HEXTORAW('C2B97041074...lots of hex...0CC00CD00');
  UPDATE MyTable
     SET blobData = buf
   WHERE ID = 462;
END;

在我的一生中，我永远不会理解Oracle的某些局限性.就像一切是它自己的特例.

For the life of me I'll never understand some of Oracle's limitations. It's like everything is its own little special case.

这篇关于Oracle 10:使用HEXTORAW填充Blob数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！