This content has been marked as final. Show 3 replies
user8703472 wrote:This isn't really a SQL*Developer issue but rather a PL/SQL issue that you will have problems with regardless of your development tool. This forum is for issues regarding SQL*Developer and will probably get a better and faster response in the SQL and PL/SQL forum.
I am building a dynamic Select Query, every value in the parm list with have a select query.
I have assigned the local variable as
vc_query := 'SELECT T1.ORG_ID' || vc_col || ' FROM ORG_MAIN T1, ORG_ATTRIBUTE T2 ' || vc_subcomp || ' ' || 'WHERE' || ' ' || vc_build_where || '' || vc_fac_type || '' || vc_fac || '' || 'AND' || ' TO_DATE( ' || '''' || vd_date || '''' || ', ''DD-MON-YY'') BETWEEN T1.ORG_START_DATE AND T1.ORG_END_DATE ' || ' ' || 'GROUP BY T1.ORG_ID' || vc_groupby || ' ' || 'ORDER BY T1.ORG_ID';
here the vc_subcomp variable should bring in the 76 select queries.
Since the varchar is only 32767 bytes. the dynamic SQL is not completely building up.
How can I solve this problem.
When I run the function , the error it shows is as below:-
PKG_EXTRACT_ALL.f_main_facility(10,'(2,7,3,24,25,29,30,31,32,33,34,35,36)','(25,23,35,1,2,3,4,18,19,20,21,22,24,26,27,28,29,30,31,32,33,34,36,15,16,17,5,6,7,8,9,10,11,12,13,14)','ALL',NULL) FROM DUAL
ERROR at line 2:
ORA-06502: PL/SQL: numeric or value error: character string buffer too small
ORA-06512: at "RXG12.PKG_EXTRACT_ALL", line 310
Be that as it may, is the line you posted the cited line 310? Is the resulting text to put into vc_query > 32767 bytes (this is possible)? Do you get the same error if you comment the line you copied that assigns vc_query? If so then that line is a problem; if not the problem is on a different line.
Make certain you are looking at line 310 (you can find this in SQL*Developer by enableing line numbers if you have not done so already).
Please mark this question ANSWERED now that you have reposted it in the SQL and PL/SQL forum where it belongs.
Re: ORA-06502:PL/SQL: numeric or value error: character string buffer too small