Self-Solve Knowledge Search

Minimize Maximize
Oracle error: PLS-00201: identifier 'UP_GETINDEPID' must be declared
This document has not been formally reviewed for accuracy and is provided as is
Title :
Oracle error: PLS-00201: identifier 'UP_GETINDEPID' must be declared
Document ID :
KM01094131
Product - Version:
asset manager ;
OS :
Updated :
Fri Aug 15 11:03:38 GMT 2014
Summary :
The UP_GETINDEPID stored procedure is not present in the database, while Asset MAnager expects it to be there.

The following Oracle error may appear if patches or hotfixes do not get installed neatly:


2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) Oracle error: ORA-06550: line 2, column 2:
2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) Oracle error: PLS-00201: identifier 'UP_GETINDEPID' must be declared
2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) Oracle error: ORA-06550: line 2, column 2:
2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) Oracle error: PL/SQL: Statement ignored
2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) SQL statement 'BEGIN \\n UP_GETINDEPID(:1);\\n END;' could not be executed
2014/08/13 10:33:50.000 0 1 [Thrd#:5836](0) Internal error while generating internal identifier (GenId)

Solution

after running the following stored procedure the issue will disappear:


create or replace procedure UP_GETINDEPID(lSeedId OUT NUMBER) AS PRAGMA AUTONOMOUS_TRANSACTION;
v_id NUMBER DEFAULT 0;
v_count INT DEFAULT 0;
BEGIN
select count(*) into v_count from IndependentLastId where lRemain = 0;
IF(v_count > 20) THEN
delete from IndependentLastId where lRemain = 0;
END IF;
select Min(IdSeed) into v_id from IndependentLastId where lRemain > 0 and lInUse = 0;
if (v_id is null or v_id = 0) then
select IndependentLastId_autoid.nextval into lSeedId from dual;
insert into IndependentLastId(IdSeed, value, lRemain, lInUse) values (lSeedId, 1, 32, 1);
else
update IndependentLastId set lInUse = 1 where IdSeed = v_id;
lSeedId := v_id;
end if;
commit;