Oracle error: PLS-00201: identifier 'UP_GETINDEPID' must be declared

  • KM01094131
  • 15-Aug-2014
  • 15-Aug-2014

This document has not been formally reviewed for accuracy and is provided "as is" for your convenience.

Summary

The UP_GETINDEPID stored procedure is not present in the database, while Asset MAnager expects it to be there.

Question

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)

Answer

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;