• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 511
  • Last Modified:

How do I rectify the ORA-06502: PL/SQL:



 var_count := var_count + 1;
        If var_count >= 10 then
         var_count := 0;
         v_block := v_block +1 ;
 -- commit;

         DBMS_OUTPUT.PUT_LINE('Committed ' || v_block * 10 || ' records');
        end if;

Open in new window

end if;

END IF;

END;

 

END LOOP;

dbms_output.put_line('No of rows updated-='|| v_block * 10 +  var_count);

END;



ORA-06502: PL/SQL: numeric or value error: character to number conversion error

ORA-06512: at line 125

-----

If I remove the following line, the code is working fine.

dbms_output.put_line('No of rows updated-='|| v_block * 10 +  var_count);

How do I show the total number of records fetched in the cursor...

var_count and v_block declared as below.   Kindly let me know as to the error in the above code.


var_count    number(4) default 0;
 v_Block       number(4) 0;





 

0
lkrishna
Asked:
lkrishna
  • 4
  • 3
6 Solutions
 
slightwv (䄆 Netminder) Commented:
>>DBMS_OUTPUT.PUT_LINE('Committed ' || v_block * 10 || ' records');

change to:
DBMS_OUTPUT.PUT_LINE('Committed ' || to_char(v_block * 10) || ' records');

0
 
sdstuberCommented:
implicit conversion of entire calculation

dbms_output.put_line('No of rows updated-='|| (v_block * 10 +  var_count));

or better, use explicit

dbms_output.put_line('No of rows updated-='|| to_char(v_block * 10 +  var_count));



0
 
slightwv (䄆 Netminder) Commented:
The high level reason behind it:  At some point Oracle start restricting what implicit datatype conversions it is willing to attempt.

As soon as it saw the math ( number * number ) trying to be concatenated to a string, it wasn't sure if the result needed to be a 'number' or a 'string'.
0
NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

 
sdstuberCommented:
the problem is the addition


+ and || are equivalent in order of operations

so they are evaluated from left to right,

the original would do the multiplication, then concatenate that to the string, then attempt to add the var_count
0
 
sdstuberCommented:
for more information on the order of operations check

Table 2-3 in the pl/sql reference

http://download.oracle.com/docs/cd/E11882_01/appdev.112/e17126/fundamentals.htm#CHDCDGCF


this is from 11gR2 documentation, but the order is the same in all versions from 7 and higher
0
 
slightwv (䄆 Netminder) Commented:
I missed the second put_line with the '+'.  sdstuber is correct.

It's just a good rule of thumb to always do explicit data type conversions and you won't have this issue.
0
 
lkrishnaAuthor Commented:
Thanks to all .  This helped and I am able to get the total number of records through dbms_output.put_line.  thanks for your help
0
 
slightwv (䄆 Netminder) Commented:
Glad you have it working!

Don't forget to close out the question and award points to the post or posts that helped you resolve the issue.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now