Procedure not working

hI
Iam not able to compile procedure sucessfully . it says errors pls-00302 foo must be declared. But same thing working in different user (same instance,same database).

Scenario as follows

1) connect alumni/o@test

create table foo (id number);

insert into foo values(1);

commit;

grant all on foo to faismgr;


2) connect faismgr/o@test

create procedure as follows


create procedure bogus is
my_values alumni.foo.id%TYPE;
cursor c1 is
select id from alumni.foo;
begin
open c1;
fetch c1 into my_values;
close c1;
end;
/
show error



SQL> show error
Errors for PROCEDURE BOGUS:

LINE/COL ERROR
-------- -----------------------------------------------------------------
2/13 PL/SQL: Item ignored
2/20 PLS-00302: component 'FOO' must be declared
7/3 PL/SQL: SQL Statement ignored
7/17 PLS-00320: the declaration of the type of this expression is
incomplete or malformed


but same thing worked in some other user called SATURN.

What could be problem? Any privs or roles missing in FAISMGR schema

any help would be appreiciated

as you are trying to access a table of another user, try using the AUTHID clause, brother,

-- stand-alone procedure
CREATE [OR REPLACE] PROCEDURE [schema_name.]procedure_name
[(parameter_list)]
[AUTHID {CURRENT_USER | DEFINER}] {IS | AS}

hi
From another user , it is working perfectly fine.

As user faismgr what does this return?

Code:

---

SELECT object_name, object_type, owner
FROM all_objects
WHERE object_name IN ('FOO', 'ALUMNI')

---

John

Object_name Object_type Owner
------------------------------ ------------------ --------

Foo Table Alumni
Alumni Package Faismgr
Alumni Package Body Faismgr

So, does the package alumni in faismrg's schema contain a function or procedure foo?

This is your situation:

Code:

---

SQL> SHOW USER
USER is "OPS$ORACLE"
SQL> create user a identified by a
  2  /
 
User created.
 
SQL> grant connect, create procedure to a
  2  /
 
Grant succeeded.
 
SQL> grant select on t to a;
 
Grant succeeded.
 
SQL> connect a/a
Connected.
SQL> CREATE package ops$oracle AS
  2  procedure p;
  3  end;
  4  /
 
Package created.
 
SQL> CREATE PACKAGE BODY ops$oracle AS
  2  PROCEDURE p IS
  3  BEGIN
  4    NULL;
  5  END;
  6  END;
  7  /
 
Package body created.
 
SQL> DECLARE
  2  l_v ops$oracle.t.id%TYPE;
  3  BEGIN
  4    SELECT id INTO l_v FROM ops$oracle.t;
  5  END;
  6  /
l_v ops$oracle.t.id%TYPE;
              *
ERROR at line 2:
ORA-06550: line 2, column 16:
PLS-00302: component 'T' must be declared
ORA-06550: line 2, column 5:
PL/SQL: Item ignored
ORA-06550: line 4, column 19:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 4, column 23:
PL/SQL: ORA-00904: : invalid identifier
ORA-06550: line 4, column 4:
PL/SQL: SQL Statement ignored
 
SQL> CREATE SYNONYM t for ops$oracle.t;
 
Synonym created.
 
SQL> DECLARE
  2  l_v t.id%TYPE;
  3  BEGIN
  4    SELECT id INTO l_v FROM t;
  5  END;
  6  /

PL/SQL procedure successfully completed.

---

TTFN
John

Dear john
Thanks for the wonderful DEMO. (solved my problem)
BTW, why oracle is behaving like this?

It is a result of the way Oracle does name resolution when you access a database object. Oracle will first look at "real" database objects (tables, views, packages etc.) in the caller's schema. If it does not find an appropriate object it will look at the caller's private synonyms, it no private synonyms exist, Oracle will look for a public synonym. Only after all this, does Oracle check to see if it might be an object owned by another user.

HTH
John