Not able to run large dynamic select query in stored procedure

Question

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;

When I run this sp it shows the following error

ORA-00600: internal error code, arguments: [qcscbAddToSelLists], [], [], [], [], [], [], [], [], [], [], []

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: numeric or value error: character string buffer too small

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

-- EDIT -- 6 Jun 2012

Hi All,

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*/

what do your DBAs say? Fairly sure an ora 600 is something that may need Oracle support to flesh out. — tbone
If you print the SQL statement you've generated (i.e. dbms_output.put_line( dynSqlComplete ) before opening the cursor and then try to execute that statement manually, does the statement work? If so, then you've narrowed the problem down to the call to OPEN. If not, the problem is in the SQL statement itself or the SQL statement that you're generating is malformed. — Justin Cave
@Justin, I have tried dbms_output.put_line( dynSqlComplete), in fact i tried dbms_output.put_line( 'hi' ) just before assigning the large query but it is not displaying. I think it is failing before that only. but when i reduce the varchar2 size and then execute, it is displaying "hi" and then character string buffer error is coming. — samar
What tool are you using? In SQLPlus, for example, you need to set serveroutput on in order to tell SQLPlus to create a buffer for dbms_output to write to and to read from the buffer and display the data. If you can't even print the word "hi", it implies that your tool hasn't been configured to display the output of dbms_output. You could also write the SQL statement to a database table and view the query after your code runs. — Justin Cave

tbone tbone · Accepted Answer · 2012-05-29T16:52:41

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;

Not able to run large dynamic select query in stored procedure

2 Answers