PLSQL -字符串缓冲区太小

Question

我正在尝试运行以下查询，以遍历一堆包含HTML页面的CLOB：

SELECT template_id, REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i') AS match
  FROM (
    SELECT template_id,  REGEXP_REPLACE(html_text, '<!--.*?-->', '', 1, 0, 'n') AS clean_html
     FROM (
           SELECT t.id as template_id, dbms_lob.substr(t.data, 4000, 1) AS html_text
           FROM template t
     )
  )
CONNECT BY REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i') IS NOT NULL
 GROUP BY template_id,
      clean_html,
      LEVEL
order by 1

但是在运行我的查询之后，我几乎直接得到了“第1行字符串缓冲区太小”。

如果我在一个只有几个模板的小数据集上运行它，那么一旦我在我的整个HTML模板列表上运行它，它就会抛出错误。

我认为这个问题与dbms_lob.substr有关(data，4000，1)，并且可能与由于隐藏字符(或其他什么原因)而超过4000个字节的子字符串有关，但我不知道如何修复它。

如果我编写了dbms_lob.substr( data，2000，1)，那么我的子字符串太小，丢失了我的HTML文件中的重要数据。如果我使它大于4000，那么我立即得到“缓冲区太小”错误。

有人知道我怎么解决这个问题吗？理想情况下，我想要检查我的整个“数据”字段，而不仅仅是前4000个字符。但是，如果前面的4000个字符在我长长的HTML文件列表上工作，那么就可以了。

谢谢

Answer 1

我假设您使用GROUP BY来获得不同的值，对吗？在很多记录上使用CONNECT BY的问题是，它会返回很多重复的记录。但是GROUP BY和DISTINCT并不是处理这个问题的正确方法。取出GROUP BY并将以下行追加到查询中：

AND PRIOR template_id = template_id
AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL

此外，我认为您希望在最内部的查询中添加以下条件：

WHERE REGEXP_LIKE(t.data, 'src="[^"]*\.js"', 'i')

否则，查询将始终为template_id的每个值返回至少一行。

我不太清楚CLOBs发生了什么，但我还不能准确地再现您的错误。

编辑的

我想我有办法了。请试用以下方法：

SELECT template_id, REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i') AS match
  FROM (
    SELECT template_id,  REGEXP_REPLACE(html_text, '<!--.*?-->', '', 1, 0, 'n') AS clean_html
     FROM (
       SELECT t.id as template_id, t.data AS html_text
         FROM template t
        WHERE REGEXP_LIKE(t.data, 'src="[^"]*\.js"', 'i')
     )
  )
CONNECT BY TO_CHAR(REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i')) IS NOT NULL
  AND PRIOR template_id = template_id
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

我认为问题在于CONNECT BY条款中的比较

CONNECT BY REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i') IS NOT NULL

在这里比较CHAR比CLOB要好

CONNECT BY TO_CHAR(REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i')) IS NOT NULL

您甚至可以在这里使用DBMS_LOB.SUBSTR()来确保安全：

CONNECT BY DBMS_LOB.SUBSTR(REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i'), 4000, 1) IS NOT NULL

最后，我认为您不需要那么多级别的子查询。我认为你可以做以下几件事：

SELECT template_id, REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i') AS match
  FROM (
    SELECT t.id AS template_id, REGEXP_REPLACE(t.data, '<!--.*?-->', '', 1, 0, 'n') AS clean_html
      FROM template t
     WHERE REGEXP_LIKE(t.data, 'src="[^"]*\.js"', 'i')
  )
CONNECT BY DBMS_LOB.SUBSTR(REGEXP_SUBSTR(clean_html, 'src="[^"]*\.js"', 1, LEVEL, 'i'), 4000, 1) IS NOT NULL
  AND PRIOR template_id = template_id
  AND PRIOR DBMS_RANDOM.VALUE IS NOT NULL;

希望这能有所帮助。