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).
Thank U Sir,
I was not sure about where i am suppose to post my problem.
Yes the size of the local variable is the problem. I posted it in the right place. Looking forward for a solution or idea to solve it. Thanks Again for directing me to the right plc.
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