Hi,
This is in version 12.1.0.2.0.
In my packaged procedure I have two cursors. They follow this pattern:
cursor curOne( cpParam varchar2 ) IS
select distinct a.name, a.code
from table_a a, table_b b
where a.code = b.code
and a.type = cpParam
order by a.name;
cursor curTwo( cpParam varchar2 ) IS
select distinct a.name, a.code
from table_a a, table_b b, table_c c
where a.code = b.code
and a.type = cpParam
and b.code = c.code
order by a.name;
Both cursors are used by simple "for record in cursor() loop" statements.
My problem is that the second cursor gives me ORA-00942, "table or view does not exist" (on the row calling "for recTwo in curTwo('param') loop").
All tables and the package are owned by schema X. The package is granted to schema Y that calls it. Table_a, table_b and table_c are actually editioning views and there are no synonyms for any of them.
When Y executes the procedure the first cursor works fine but the second raises the exception. The second cursor is just like the first but with one extra table involved.
I have found that the solution is to add the schema name (X) to the first two tables in cursor two. Then the cursor works fine.
So this works:
cursor curTwo( cpParam varchar2 ) IS
select distinct a.name, a.code
from X.table_a a, X.table_b b, table_c c
where a.code = b.code
and a.type = cpParam
and b.code = c.code
order by a.name;
I find this really strange. I don't see why this would be needed and the first cursor doesn't qualify with schema name.
So I have a solution but I'm concerned that this might indicate some other problem in the database or my schemas.
Anyone with a guess on the cause of the error?
Kind regards
Tomas Albinsson