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;
/

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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

Featured Post

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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 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…