OraFAQ Forum: SQL & PL/SQL

Home » SQL & PL/SQL » SQL & PL/SQL » error

Show: Today's Messages :: Polls :: Message Navigator
E-mail to friend

error [message #597711]

Mon, 07 October 2013 14:10

nataliafoster26
Messages: 64
Registered: October 2013

Member

I have this query

FUNCTION RENDER_SEG_AVAIL_DD(
                                PvProperty          P_PM_PROPERTY.PROPERTY_ID%TYPE,
                                pvUnitTypeID        P_PM_UNIT_TYPE.PM_UNIT_TYPE_ID%TYPE,
                                pvName              VARCHAR2,
                                pvAttrib            VARCHAR2,
                                pvUsageDate         DATE,
                                pvDefaultSeg        VARCHAR2 --15388 Hybrid 1.1 - Added parameter to support default segment logic
                            )
RETURN VARCHAR2
IS
 
 
TYPE SegRec IS RECORD
(
    DDValue VARCHAR2(50),
     DDDesc VARCHAR2(50)
);
 
 
TYPE SegTabType IS TABLE OF SegRec;
 
 
vSegData SegTabType;
 
 
vIndex      INTEGER;
vRecs       INTEGER;
vRetval     VARCHAR2(30000);
vUnallCount INTEGER;
vUnallOpt   VARCHAR(100) := '';
 
 
BEGIN
 
 
    vUnallCount := AVAILABLE.GET_UNALLOCATED_COUNT(pvUnitTypeID,pvUsageDate);
 
 
    IF vUnallCount > 0 THEN
        vUnallOpt := '<option value="---" SELECTED>Unallocated / '||TO_CHAR(vUnallCount)||'</option>'||CHR(10);
    END IF;
 
 
    SELECT Main.SEGMENT_CODE,Main.DDDesc
    BULK COLLECT INTO vSegData
    FROM
    (
        SELECT cc.SEGMENT_CODE,cc.SEGMENT_CODE || ' - ' ||s.SEGMENT_DESC||' / ' || TO_CHAR(ALLOCATION-UNITS_RESERVED) || ' Avail' DDDesc
        FROM  P_PM_CONTROL_COUNT cc,
             P_PM_SEGMENT s,
             S_PM_PROP_SEG ps
        WHERE cc.SEGMENT_CODE = s.SEGMENT_CODE
        AND s.SEGMENT_CODE = ps.SEGMENT_CODE
        AND ps.PROPERTY_ID = PvProperty
        AND s.SEGMENT_ACTIVE = 'Y'
        AND cc.PM_UNIT_TYPE_ID = pvUnitTypeID
        AND cc.Usage_Date = pvUsageDate
        AND cc.ALLOCATION-cc.UNITS_RESERVED > 0
        AND cc.SEGMENT_CODE IN

this is the log error i got

MODPLSQL-00261: mod_plsql: /auslive/unit_maint_pkg.maint_results
HTTP-404 Failed to execute target procedure ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated
Bind\nORA-06512: at "ATLAS.UNIT_MAINT_PKG", line 1652\nORA-06512: at "ATLAS.UNIT_MAINT_PKG", line 1037\nORA-06512:
at "ATLAS.UNIT_MAINT_PKG", line 366\nORA-06512: at "ATLAS.UNIT_MAINT_PKG", line 2048\nORA-06512: at line 33\n,
has you can see i get error on line 1652 which is this line
SELECT cc.SEGMENT_CODE,cc.SEGMENT_CODE || ' - ' ||s.SEGMENT_DESC||' / ' || TO_CHAR(ALLOCATION-UNITS_RESERVED) || ' Avail' DDDesc
due with truncated bind
this is a old error i got, with some specific data, Now when i run it with the same data, it actually works, even when DDDesc goes beyond 50 char it still works example:
DDDesc ="IEX - Internal Exchange-ow trading datess / 10000000 Avail" which is 58 in length

from what i have been reading online in about the error "truncated bind"
like from this article Jared Still's Ramblings: ORA-6502 "Bulk Bind: Truncated Bind" error
it can just be changing the lenght of dddesc which from what i can conclude its 50 right now , so its not even bothering about it right now, it doesnt care is 50 its by passing it

When i run this query

declare
TYPE SegRec IS RECORD
(
    DDValue VARCHAR2(50),
     DDDesc VARCHAR2(50)
);
TYPE SegTabType IS TABLE OF SegRec;
vSegData SegTabType;
BEGIN
    SELECT Main.SEGMENT_CODE,Main.DDDesc
    BULK COLLECT INTO vSegData
    FROM
    (
        SELECT 'IEX' SEGMENT_CODE,'IEX - InternalHHHHHHH/ 10000000 Avail' DDDesc
        FROM DUAL  
    ) Main;
END;
/

i will get no error, but lets say SELECT 'IEX' SEGMENT_CODE,'IEX - InternalHHHHHHHHHHHHHHHHHHHHH/ 10000000 Avail' DDDesc
the length is bigger than 50 it will give me a error on
ORA-06502: PL/SQL: numeric or value error: Bulk Bind: Truncated Bind
ORA-06512: at line 13

any tips will help thanks Sad