Line length overflow error in Native dynamic SQL

[happy_jack]

Hi all,

I am getting the following error while i use execute immediate inside my function.

"Line length overflow, limit of 255 chars per line"

is that a problem in my code or has it got anything to do with the environmental settings?

any help is much appreciated!

~ Ant.

[pando]

do you use dbms_output?¿

paste the code, execution and error


rgds

[happy_jack]

Hello Pando,

here's my code. the program halts at the exception handler (raise_app_error) and it gives me this line length overflow thing. I am trying to build a where clause dynamically and i am not using bind variables in the program. just appending stuff and doing an execute immediate.

let me know if you have any ideas.

Thanks!

CREATE OR REPLACE FUNCTION BOA_RPT_ACTUALS_FCT(p_resourceobs IN NUMBER,
p_projectid IN NUMBER,
p_obstypeid IN NUMBER,
p_startdate IN DATE,
p_enddate IN DATE) RETURN NUMBER
AS
v_acthrs NUMBER;
v_depth NUMBER;
v_where VARCHAR2(100);
v_SQLQuery VARCHAR2(10000);

BEGIN

SELECT depth INTO v_depth
FROM PRJ_OBS_UNITS
WHERE id = p_resourceobs
AND type_id = p_obstypeid
GROUP BY depth;

v_where := CASE v_depth
WHEN 0 THEN
'BROMV.level0_unit_id'
WHEN 1 THEN
'BROMV.level1_unit_id'
WHEN 2 THEN
'BROMV.level2_unit_id'
WHEN 3 THEN
'BROMV.level3_unit_id'
WHEN 4 THEN
'BROMV.level4_unit_id'
WHEN 5 THEN
'BROMV.level5_unit_id'
WHEN 6 THEN
'BROMV.level6_unit_id'
WHEN 7 THEN
'BROMV.level7_unit_id'
WHEN 8 THEN
'BROMV.level8_unit_id'
WHEN 9 THEN
'BROMV.level9_unit_id'
WHEN 10 THEN
'BROMV.level10_unit_id'
END;

v_SQLQuery := ' SELECT NVL(ROUND(SUM(nvl(PBS.slice,0)),2),0) slice'||
' FROM PRJ_PROJECTS PP, '||
' SRM_RESOURCES SR, '||
' BOA_PROJECT_OBS_MV BPOMV,'||
' BOA_RESOURCE_OBS_MV BROMV,'||
' PRTASK PK, PRASSIGNMENT PA,'||
' PRJ_BLB_SLICES PBS,'||
' PRJ_BLB_SLICEREQUESTS PBSR'||
' WHERE PP.prid = PK.prprojectid'||
' AND SR.id= PA.prresourceid'||
' AND PK.prid = PA.prtaskid'||
' AND PP.prid = BPOMV.projectid '||
' AND PBS.slice_request_id = PBSR.id '||
' AND PBSR.request_name = ''DAILYRESOURCEACTCURVE'''||
' AND PA.prid = PBS.prj_object_id'||
' AND PBS.slice_date BETWEEN '||chr(39)|| p_startdate ||chr(39)||
' AND '||chr(39)|| p_enddate ||chr(39)||
' AND BPOMV.obs_type_id = '|| p_obstypeid ||
' AND BROMV.obs_type_id = '|| p_obstypeid ||
' AND BROMV.resourceid = SR.id'||
' AND PP.prid = '|| p_projectid ||
' AND '||v_where||' = '||p_resourceobs;

DBMS_OUTPUT.PUT_LINE(v_SQLQuery);

EXECUTE IMMEDIATE v_SQLQuery INTO v_acthrs; --USING p_projectid, p_resourceobs;

RETURN v_acthrs;

EXCEPTION

WHEN no_data_found THEN
RETURN 0;
WHEN OTHERS THEN
raise_application_error(-20003, SUBSTR(SQLERRM,1,250));

END BOA_RPT_ACTUALS_FCT;

[happy_jack]

and ignore the dbms_output statement in the code. i had inserted it while debugging this code with breakpoints...

[zzz]

The error is occuring because of the DBMS_OUTPUT.PUT_LINE statement. Comment it out and you wouldn't get the error. If you want to debug the SQL generated, you could insert the SQL string into a temporary table and use it.