Solved

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

Posted on 2011-03-02
8
503 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 250 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 250 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
PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

 
LVL 74

Assisted Solution

by:sdstuber
sdstuber earned 250 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 250 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 250 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 250 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

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

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…
Have you ever had to make fundamental changes to a table in Oracle, but haven't been able to get any downtime?  I'm talking things like: * Dropping columns * Shrinking allocated space * Removing chained blocks and restoring the PCTFREE * Re-or…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
This video shows how to recover a database from a user managed backup

726 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