PL/SQL AVG Function with VS2008 causing "Arithmetic operation resulted in an overflow"

I'm sure I'm overlooking something simple here, but anyway - I'm trying to build a PL/SQL procedure which implements AVG via a decode function, see below. I keep getting an arithmetic overflow error but can't figure out what needs changing to hold the right size for the type (or even if that's what's required!)

If I change the AVG to Count, Sum or Max, all is fine, so I know the decode is working correctly, I'm just not sure why AVG isn't. Any pointers greatly appreciated.

Rgds BBz

PROCEDURE GET_DATAMEANS (
    fLOTCODE IN VARCHAR2,
    fFROMDATE IN DATE,
    fTODATE IN DATE,
    THEDATA OUT SYS_REFCURSOR) IS

    TYPE loc_array_type IS TABLE OF VARCHAR2(40);  -- array type
    sql_str VARCHAR2(10000);    -- SQL statement
    loc_array    loc_array_type;     -- array for test names

    BEGIN -- executable part starts here

        -- get the test names for the given lot code
        SELECT 
            PT_TESTNAME BULK COLLECT INTO loc_array
        FROM 
            (SELECT DISTINCT
                TESTPARMS.PT_TESTNAME, TESTPARMS.PT_TESTNUM
                FROM "PRETEST".PRETEST_LOT@PRS_DBLINK LOT,
                "PRETEST".PRETEST_MEASURE@PRS_DBLINK MEASURE,
                "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK TESTPARMS
                WHERE (LOT.PT_LOTSQ = MEASURE.PT_LOTSQ)
                AND (MEASURE.PT_LOTSQ = TESTPARMS.PT_LOTSQ)
                AND (MEASURE.PT_TESTNUM = TESTPARMS.PT_TESTNUM)
                AND (LOT.PT_LOTID = fLOTCODE)开发者_JAVA技巧
                ORDER BY PT_TESTNUM);

        -- build the SQL string
        sql_str := '';
        sql_str := sql_str ||   'SELECT ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOTID, ';
        sql_str := sql_str ||   '   PRETEST_LOT.PT_LOCTYPE, ' ;
        sql_str := sql_str ||   '   PRETEST_LOT.PT_TESTDATE, ';

        -- add the decodes for column headings
        FOR i IN loc_array.first..loc_array.last LOOP
            sql_str := sql_str 
                || '  AVG ( decode ( PRETEST_TEST_PARMS.PT_TESTNAME, '''
                || loc_array(i) || ''', PRETEST_MEASURE.PT_MEAS_VALUE  , null )) '
                || loc_array(i);
                IF (i < loc_array.last) THEN
                    sql_str := sql_str || ', ';
                END IF;
        END LOOP;

        -- build the remainder of the SQL
        sql_str := sql_str || ' FROM ';
        sql_str := sql_str || '     "PRETEST".PRETEST_LOT@PRS_DBLINK PRETEST_LOT, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_MEASURE@PRS_DBLINK PRETEST_MEASURE, ';
        sql_str := sql_str || '     "PRETEST".PRETEST_TEST_PARMS@PRS_DBLINK PRETEST_TEST_PARMS ';

        sql_str := sql_str || ' WHERE  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTSQ = PRETEST_MEASURE.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_LOTSQ = PRETEST_TEST_PARMS.PT_LOTSQ AND ';
        sql_str := sql_str || '     PRETEST_MEASURE.PT_TESTNUM = PRETEST_TEST_PARMS.PT_TESTNUM AND ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOCTYPE=''9A08-55/T'' AND  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE Between :fFROMDATE And :fTODATE  ';

        sql_str := sql_str || ' GROUP BY ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOTID,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_LOCTYPE,  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE  ';

        sql_str := sql_str || ' ORDER BY  ';
        sql_str := sql_str || '     PRETEST_LOT.PT_TESTDATE  ';

        -- run the query
        OPEN THEDATA FOR sql_str USING fFROMDATE, fTODATE;

END GET_DATAMEANS;

The error appears to be caused by a difference in decimal place resolution between Oracle and VS2008. Apparently, Oracle will return around (not sure exactly) 36 dp whereas VS2008 only handles around 27 dp (again, not sure of exact number.)

Wrapping the AVG statement with Round( .... , 12 ) will return a 12 dp number which VS2008 will accept.