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