title: Not able to run large dynamic select query in stored procedure
body: <p>I have a stored procedure which is executing a dynamic select query. The query string is large. The following is the stored procedure</p>
  
  <pre>
  
  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;
  
  </pre>
  
  <p>When I run this sp it shows the following error</p>
  
  <blockquote>
    <p><strong>ORA-00600: internal error code, arguments: [qcscbAddToSelLists], [], [], [], [], [], [], [], [], [], [], []</strong></p>
  </blockquote>
  
  <p>so I reduced the size of <code>dynSqlComplete</code> variable to varchar2(5000) and then ran the stored procedure. I got the following error:</p>
  
  <blockquote>
    <p><strong>ORA-06502: PL/SQL: numeric or value error: character string buffer too small</strong></p>
  </blockquote>
  
  <p>I have tried many things in vain and also I do not want to add them here because it will misguide.</p>
  
  <p><strong>-- EDIT -- 6 Jun 2012</strong></p>
  
  <p>Hi All,</p>
  
  <p>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 <code>START WITH</code> 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.</p>
  
  <pre>
  
        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*/
  
  </pre>