无法在存储过程中运行大型动态选择查询 [英] Not able to run large dynamic select query in stored procedure

问题描述

我有一个存储过程正在执行动态选择查询.查询字符串很大.以下是存储过程

I have a stored procedure which is executing a dynamic select query. The query string is large. The following is the stored procedure

create or replace
procedure My_SP
(
procRefCursor out sys_refcursor,
--My other input variables here
)
is

dynSqlComplete varchar2(8000) := 'n/a';

begin

  dynSqlComplete := 'Large query here';

  open procRefCursor for dynSqlComplete;

end;

当我运行此sp时，它显示以下错误

When I run this sp it shows the following error

ORA-00600:内部错误代码，参数:[qcscbAddToSelLists]，[]，[]，[]，[]，[]，[]，[]，[]，[]，[]， []

所以我将dynSqlComplete变量的大小减小为varchar2(5000)，然后运行存储过程.我收到以下错误:

so I reduced the size of dynSqlComplete variable to varchar2(5000) and then ran the stored procedure. I got the following error:

ORA-06502:PL/SQL:数字或值错误:字符串缓冲区太小

我徒劳地尝试了许多事情，也不想在这里添加它们，因为这会误导人.

I have tried many things in vain and also I do not want to add them here because it will misguide.

-编辑-2012年6月6日

大家好，

我能够指出问题所在，但仍无法解决.我试图部分地运行查询，但发现查询抛出错误.它包含START WITH，当我评论它时，查询开始工作.我已经给出了下面的代码，并注释了给出错误的代码.

I was able to pin point the problem but I am not yet able to solve it. I tried to run the query part by part and I found the query which was throwing an error. It contained START WITH and when i commented it the query started to work. I have given the code below and commented out the code which is giving error.

      SELECT RowNum AS RowNumber1,
      GR.*,
      --LEVEL AS LineageLvl,
      VDE.*
    FROM
      (SELECT *
      FROM group_relations  left outer join relation_classifier_instances RC on 
      rc.relation_id = group_relations.Group_relation_id  WHERE group_relation_type_id IN
      (19,20,32,38,42,43)  and (rc.relation_id is null) 
      ) GR
    LEFT OUTER JOIN Vendor_Feed_data_elements VDE
    ON GR.Group_Relation_Type_Id = 19
    AND GR.Primary_GroupField_Id = VDE.Vendor_Data_Element_Id
      /* Code which is giving the error
      START WITH
      (
        VDE.Vendor_Data_Element_Id IS NOT NULL  )
      CONNECT BY nocycle prior GR.RELATED_GROUPFIELD_ID = GR.PRIMARY_GROUPFIELD_ID*/

推荐答案

您可以尝试在11g的CLOB中使用Clob，类似(未测试):

You can try using a clob a CLOB in 11g, something like (untested):

declare
  l_sql clob;
  l_str1 varchar2(32767);
  l_str2 varchar2(32767);
begin
  dbms_lob.createtemporary(l_sql, false);

  l_str1 := 'some large SQL chunk';
  l_str2 := 'and the rest of large SQL chunk';

  l_sql := l_str1;
  dbms_lob.writeappend(l_sql, length(l_str2), l_str2);

  execute immediate l_sql;

  dbms_lob.freetemporary(l_sql);

end;

这篇关于无法在存储过程中运行大型动态选择查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！