[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

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

Posted on 2011-03-02
8
Medium Priority
?
508 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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
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

Get free NFR key for Veeam Availability Suite 9.5

Veeam is happy to provide a free NFR license (1 year, 2 sockets) to all certified IT Pros. The license allows for the non-production use of Veeam Availability Suite v9.5 in your home lab, without any feature limitations. It works for both VMware and Hyper-V environments

Question has a verified solution.

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

Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
When it comes to protecting Oracle Database servers and systems, there are a ton of myths out there. Here are the most common.
This video shows how to copy a database user from one database to another user DBMS_METADATA.  It also shows how to copy a user's permissions and discusses password hash differences between Oracle 10g and 11g.
This video explains at a high level about the four available data types in Oracle and how dates can be manipulated by the user to get data into and out of the database.

650 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