Solved

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

Posted on 2011-03-02
8
501 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
  • 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
Independent Software Vendors: 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!

 
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

Technology Partners: 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

Suggested Solutions

Title # Comments Views Activity
ODBC in excel 2016 in Windows 10 via VBA 16 240
Oracle Insert not working 10 47
sum of columns in a row in oracle 3 41
Oracle programming for starter 14 27
Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
Via a live example show how to connect to RMAN, make basic configuration settings changes and then take a backup of a demo database
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

735 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