Issue with plsql declaration

From: Kumar Madduri <ksmadduri_at_gmail.com>
Date: Sun, 4 Jun 2017 21:51:49 -0700
Message-ID: <CAHDOOG5ocA2MSNEri8t0Z4fXWs5XxUPoVGxVkjwmWgNrE6u6jg_at_mail.gmail.com>



We have a database that is refreshed every week from production. After one of such refreshes, developers complained some packages were invalid. The pattern was that those packages had declarations such as this

v_end_date gl.gl_periods.end_date%type.

Package could not be compiled because you get invalid identifier g.gl_periods.

But the following would work.
select end_date from gl.gl_periods
select end_date from gl_periods (there was a synonym from apps schema to gl schema)
select end_date from apps.gl_periods;
It only failed when you called it as part of plsql variable declaration. A simple test case was
declare
v_end_date gl.gl_periods.end_date%type;
begin
null;
end;
/t

I compared the table privileges, role privileges and sysem privileges between production and this test and there was no difference. Checked init.ora,environment variables, flushed buffer cache, shared pool, bounced database too as last resort. Nothing helped Basically we could not find any pattern (finally refreshed again from production and it worked).

But what can cause this? Any clues?
We suspect a developer did something that caused this issue becuase all the developers have access to 'apps' schema in this development instance. But I am curious to know 'what was that something that they did'.. Also, this is not a refresh that we have been doing for the past 2 weeks. This was something that is running for more than 5 years now and we have not changed the scripts. Also after the incident, we refreshed atleast couple of times and have not faced this issue again.

Thanks for your time

Kumar

--
http://www.freelists.org/webpage/oracle-l
Received on Mon Jun 05 2017 - 06:51:49 CEST

Original text of this message