?
Solved

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

Posted on 2011-03-02
8
Medium Priority
?
507 Views
Last Modified: 2012-05-11


 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
Comment
Question by:lkrishna
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 77

Expert Comment

by:slightwv (䄆 Netminder)
ID: 35019212
>>DBMS_OUTPUT.PUT_LINE('Committed ' || v_block * 10 || ' records');

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

0
 
LVL 74

Accepted Solution

by:
sdstuber earned 1000 total points
ID: 35019238
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 35019240
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
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 35019259
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
 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 1000 total points
ID: 35019284
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 35019313
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
 

Author Comment

by:lkrishna
ID: 35029055
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
 
LVL 77

Assisted Solution

by:slightwv (䄆 Netminder)
slightwv (䄆 Netminder) earned 1000 total points
ID: 35029112
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Working with Network Access Control Lists in Oracle 11g (part 2) Part 1: http://www.e-e.com/A_8429.html Previously, I introduced the basics of network ACL's including how to create, delete and modify entries to allow and deny access.  For many…
How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question