ORA-06512 error

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
mahee999Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
GGuzdziolConnect With a Mentor Commented:
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
 
GGuzdziolCommented:
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
 
GGuzdziolCommented:
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
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

 
paquicubaCommented:
Is checking.codename a "NUMBER type" column?
0
 
mahee999Author Commented:
no its the varchar type
0
 
mahee999Author Commented:
no i dont understand the solution and i m not joining any 2 tables and then why innerjoin
0
 
MohanKNairConnect With a Mentor Commented:
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
 
Computer101Commented:
Forced accept.

Computer101
EE Admin
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.