PLS-00201 Error

[cdm1221]

I'm writing a simple stored function:

CREATE OR REPLACE FUNCTION GET_GMTOFFSET
RETURN number
AS
DBoffset number(10);
UserOffset number(10);

BEGIN
DBOffset:=0;
UserOffset:=0;

select "VALUE" into DBOffset from "_SMSYSADMIN_"."SMSYSFLAGS"
where "NAME" = 'namDBTimeZone';

select "GMT_OFFSET" into UserOffset
from "_SMDBA_"."_GMT_"
where "SEQUENCE" = (select "SEQ_GMT" from "_SMDBA_"."_PERSONNEL_"
where "CODE" = user);

return DBOffset - UserOffset;

END;

I keep getting the error (PLS-00201: identifier '_SMDBA_._GMT_' must be declared). However, _SMDBA_._GMT_ is a table. Any idea what I'm doing wrong?

 

[SantaMufasa]

CDM,

What happens when (from the same prompt from which you try running this PL/SQL), you do either a
desc "_SMDBA_"."_GMT_"
or
SELECT * from "_SMDBA_"."_GMT_";
?

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:36 (09Feb04) UTC (aka "GMT" and "Zulu&quot, 11:36 (09Feb04) Mountain Time)

 

[cdm1221]

Same error. Does it have something to do with permissions?

 

[SantaMufasa]

CDM,

If the table, in fact, exists in the "_SMDBA_" schema, then yes, check your GRANTs on the table(s) that you need.

Also, I don't know what overriding coding standards you have within your organization, but generally we try to avoid coding Oracle object names with characters that require double-quote (&quot qualification.

Mufasa
(aka Dave of Sandy, Utah, USA @ 18:50 (09Feb04) UTC (aka "GMT" and "Zulu&quot, 11:50 (09Feb04) Mountain Time)