?
Solved

ORA-06512 error

Posted on 2006-06-19
9
Medium Priority
?
6,032 Views
Last Modified: 2012-05-05
Hi,
i had already posted this prcedure and once again i would like to reopen it
i have a  problem and i  m getting this exception for this procedure and i donot know how to handle this could anyone help with this.

CREATE OR REPLACE PROCEDURE proc_checking
IS
cursor checkcursor is select * from checking;
value1 checking.codename%TYPE;
value2 checking.codename%TYPE;
value3 checking.codename%TYPE;
value4 number(3);
value5 number(3);
BEGIN
FOR checkcounter in checkcursor
LOOP
begin
select codename into value1 from checking2 where codename =checkcounter.codename;
EXCEPTION
WHEN NO_DATA_FOUND THEN
value1:=null;
end;
begin
select count(*) into value5 from checking1;
IF value5 >0 THEN
select distinct(substr(value,1,length(checkcounter.codename))) into value2 from checking1 where substr(value,1,length(checkcounter.codename))=checkcounter.codename;
ELSE value2:=null;
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
value2:=null;
end;
IF value1 is not null and value2 is null THEN
insert into checking1(value)VALUES(checkcounter.codename||'1');
ELSif value1 is not null and value2 is not null THEN
select max(to_number(substr(value,length(checkcounter.codename)+1,length(value)-length(checkcounter.codename)))) into value3 from checking1 where substr(value,1,length(checkcounter.codename)) =checkcounter.codename;
value4:= to_number(value3)+1;
insert into checking1(value)VALUES(checkcounter.codename||to_char(value4));
ELSIF value1 is null and value2 is not null THEN
insert into checking1(value)VALUES(checkcounter.codename||'1');
ELSIF value1 is null and value2 is null THEN
insert into checking1(value)VALUES(checkcounter.codename);
END IF;
END LOOP;
END proc_checking;
/


and when i execute i m getting ORA-06512 and

*
ERROR at line 1:
ORA-01722: invalid number

can anyone help me in solving this


thanks in advance
0
Comment
Question by:mahee999
8 Comments
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16936622
Maybe this would fail with similar exception?

select max(to_number(substr(c1.value,length(c2.codename)+1,length(c1.value)-length(c2.codename))))
  from checking1 c1
    inner join checking c2 on substr(c1.value,1,length(c2.codename)) = c2.codename
0
 
LVL 14

Expert Comment

by:GGuzdziol
ID: 16936750
If so, try

select *
  from (
    select substr(c1.value,length(c2.codename)+1,length(c1.value)-length(c2.codename)) x
      from checking1 c1
        inner join checking c2 on substr(c1.value,1,length(c2.codename)) = c2.codename
  )
  where trim(translate(x, '12345676890', '          ')) is not null

to find rows that cause Your problems.
0
 
LVL 23

Expert Comment

by:paquicuba
ID: 16936779
Is checking.codename a "NUMBER type" column?
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:mahee999
ID: 16937029
no its the varchar type
0
 

Author Comment

by:mahee999
ID: 16937041
no i dont understand the solution and i m not joining any 2 tables and then why innerjoin
0
 
LVL 14

Accepted Solution

by:
GGuzdziol earned 1000 total points
ID: 16937075
In fact You join them - You loop with cursor through one of them and then select something from another based on value in cursor.

Have You tried this statement anyway?
0
 
LVL 16

Assisted Solution

by:MohanKNair
MohanKNair earned 1000 total points
ID: 16939784
The problem statement is to_number in select max(to_number(substr(value,length(checkcounter.codename)+1,length(value)-length(checkcounter.codename))))
using sql*plus issue this statement and see what error it gives

select to_number(value) from checking1;
0
 
LVL 1

Expert Comment

by:Computer101
ID: 20294471
Forced accept.

Computer101
EE Admin
0

Featured Post

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!

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…
This video shows syntax for various backup options while discussing how the different basic backup types work.  It explains how to take full backups, incremental level 0 backups, incremental level 1 backups in both differential and cumulative mode a…
This video explains what a user managed backup is and shows how to take one, providing a couple of simple example scripts.
Suggested Courses
Course of the Month7 days, 19 hours left to enroll

615 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