Re: Issue with plsql declaration

A long shot, but could it be that at the time of the refresh a release happened in the source database? that could at that very moment have lost the grants between schemas, before regranting them. This is something that happens from time to time in my environment where refreshes.are done with impdp over several hours.

Cheers,
Tony

On 5 June 2017 2:51:49 PM AEST, Kumar Madduri <ksmadduri_at_gmail.com> wrote:
>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

-- 
Sent from my Android device with K-9 Mail. Please excuse my brevity.
--
http://www.freelists.org/webpage/oracle-l