Numeric or value error: character to number conversion error

Question

create or replace
procedure prdBandwidth
is
    cursor c_bandwidth is select distinct mt.name bwname,b.circuitno circuitno,subapp.customerno customerno,netinfo.bandwidthtype bandwidthtype from wom.tbltsubscriberapplication subapp,wom.tbltllnetworkinfo netinfo,wom.tblmmastertype mt,(select max(applicationdate) as maxdate,circuitno,customerno from wom.TBLTSUBSCRIBERAPPLICATION,tblmaccount where nodename='End' and accountnumber=customerno group by circuitno, customerno) b  where subapp.applicationdate=b.maxdate and  subapp.circuitno=b.circuitno and subapp.llnetworkinfoid=netinfo.id and netinfo.bandwidthtype=mt.id;
    bwtype varchar2(6);
    bwtypelen number;
    bwtypesublen number;
    bwvalue varchar2(8);
      kbpsdata number;
begin
  DBMS_OUTPUT.PUT_LINE('hhahahahah');
  for crs_bandwidth in c_bandwidth
  loop 
    bwtypelen:=length(crs_bandwidth.bwname);
    DBMS_OUTPUT.PUT_LINE('bwtypelen:::'+bwtypelen);
    bwtype:=substr(crs_bandwidth.bwname,-4,4);
    DBMS_OUTPUT.PUT_LINE('bwtype:::'+bwtype);
    bwtypesublen:=length(bwtype);
     DBMS_OUTPUT.PUT_LINE('bwtypesublen:::'+bwtypesublen);
    bwtypesublen:=bwtypesublen-1;
     DBMS_OUTPUT.PUT_LINE('bwtypesublen:::'+bwtypesublen);
    bwvalue:=substr(crs_bandwidth.bwname,bwtypesublen,bwtypesublen);
    DBMS_OUTPUT.PUT_LINE('bwvalue:::'+bwvalue);
    if bwtype='mbps' then
        DBMS_OUTPUT.PUT_LINE('bwtype in if condition:::'+bwvalue);
        bwvalue:=bwvalue*1024;
        DBMS_OUTPUT.PUT_LINE('bwtype in if condition:::'+bwvalue);
    end if;
    update TBLMLEASEDLINECUSTOMER set BANDWIDTH=bwvalue where CUSTOMERID = (select CUSTOMERID from TBLMCUSTOMER  where  accountnumber=crs_bandwidth.customerno);
    end loop;
   commit;
end;

Error starting at line 36 in command:

exec prdBandwidth

Error report:

ORA-06502: PL/SQL: numeric or value error: character to number conversion error
ORA-06512: at "JISPBILCORBILLINWOM501.PRDBANDWIDTH", line 14
ORA-06512: at line 1
06502. 00000 -  "PL/SQL: numeric or value error%s"
*Cause:    
*Action:

this is the code

score 7 · Answer 1 · answered Jan 05 '12 at 12:59

7

In your DBMS_OUTPUT.PUT_LINE statements, try using '||' as a concatenation operator, rather than '+'.

answered Jan 05 '12 at 12:59

Mark J. Bobak

13,720
6
39
67

+1 I'm going to add this to my list of reasons not to use DBMS_OUTPUT to debug our code. – APC Jan 05 '12 at 14:02
5

@APC: there are lots of reasons for not using DBMS_OUTPUT, but this error has nothing to do with those and is a result of using the wrong operator - which will result in either a syntactic or semantic error in a PL/SQL operation or an SQL statement or any other language – symcbean Jan 05 '12 at 15:34

Numeric or value error: character to number conversion error

1 Answers1