We have a function "test_dyanamic_before" in which we are dynamically calling another function "TEST_CHILD". I am pasting the sample codes but while running the test_dyanamic_before the flow always goes inside exception (see DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION') in test_dyanamic_before how can i avoid this and what are we doing wrong.

Select test_dyanamic_before(‘test_child’) from dual ;

Its giving a error

ORA-20000: ORA-06502: PL/SQL: numeric or value error: character string buffer too small ERRRRRRRRRRRROOOORRRRR ... Line: 29

The samples are as follows
/**********************************************/
TEST_CHILD
/**********************************************/
CREATE OR REPLACE FUNCTION test_child(var_1 VARCHAR2,var_2 OUT VARCHAR2 )
RETURN NUMBER AS
var_3 VARCHAR2(10) := 'AAA';
BEGIN
var_2 := var_3;
--DBMS_OUTPUT.PUT_LINE('inside etst_child');
RETURN length(var_2);
--RETURN 0;
END;


/**********************************************/
TEST_DYNAMIC BEFORE
/**********************************************/
CREATE OR REPLACE FUNCTION TEST_DYNAMIC_BEFORE(VAR_PROC VARCHAR2)
RETURN NUMBER
AS

VAR_CMD VARCHAR2(2000);
V_CURSOR_ID INTEGER;
VAR_L_RETURN NUMBER;
VAR_INPUT VARCHAR2(10) := 'TES';
VAR_OUTPUT VARCHAR2(1000);
DUMMY NUMBER;

BEGIN

VAR_CMD := ' BEGIN '||
' :RET_CODE :=' || VAR_PROC || '(' ||
' :VAR_1,' || ':VAR_2' ||
'); END;';

--------------------------------------------------------------------------------------------
BEGIN
--------------------------------------------------------------------------------------------
-- FOR TESTING DYNAMIC SQL

V_CURSOR_ID := DBMS_SQL.OPEN_CURSOR;
DBMS_SQL.PARSE (V_CURSOR_ID,VAR_CMD,DBMS_SQL.V7 );
DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':RET_CODE',VAR_L_RETURN);
DBMS_SQL.BIND_VARIABLE (V_CURSOR_ID,':VAR_1',VAR_INPUT);
DBMS_SQL.BIND_VARIABLE(V_CURSOR_ID,':VAR_2',VAR_OUTPUT);
dbms_output.put_line('HERE 1');
DUMMY := DBMS_SQL.EXECUTE (V_CURSOR_ID);
dbms_output.put_line('HERE 2');
DBMS_SQL.VARIABLE_VALUE(V_CURSOR_ID,':VAR_2',VAR_OUTPUT);
DBMS_SQL.CLOSE_CURSOR (V_CURSOR_ID);
DBMS_OUTPUT.PUT_LINE('inside MAIN' || VAR_OUTPUT);
--VAR_L_RETURN := TEST_CHILD(VAR_INPUT,VAR_OUTPUT);
--------------------------------------------------------------------------------------------
IF (VAR_L_RETURN != 0) THEN
BEGIN
ORA_RAISERROR(29, 'EXECUTION OF PENALTY ROUTINE FAILED ...', 29);
RETURN (VAR_L_RETURN);
END;
END IF;
EXCEPTION
WHEN OTHERS THEN
IF DBMS_SQL.IS_OPEN(V_CURSOR_ID) THEN
DBMS_SQL.CLOSE_CURSOR(V_CURSOR_ID);
--RETURN 0;
END IF;
DBMS_OUTPUT.PUT_LINE('INSIDE EXCEPTION');
--ORA_RAISERROR(SQLCODE, 'ERRRRRRRRRRRROOOORRRRR ...', 29);
END;
RETURN 80;
END;


PLz help


Regds
Aniruddha