Cursor Error

[organickarma]

I have a package specification where I have defined two cursor types

type curtype1 is ref cursor return temp_table%rowtype
and similarly curtype2

I have 2 stored procedures that make use of these cursors

create or replace procedure1(p1,p2....,cur1 in out packagename.curtype1)

then the procedure body

When i execute this procedure this is the error i get

ORA-06550: line 6, column 18:
PLS-00201: identifier 'EIA_TYPES.EIA_920_CURSOR_TYPE' must be declared
ORA-06550: line 6, column 18:
PL/SQL: Item ignored
ORA-06550: line 15, column 71:
PLS-00320: the declaration of the type of this expression is incomplete or malformed
ORA-06550: line 15, column 3:
PL/SQL: Statement ignored

When i execute this procedure logged into the schema as user1 it works fine

when i login as user2 which is the login for the QA and Production databases the error occurs. I have set all possible grants.

Any ideas would be appreciated greatly

 

[Turkbear]

Hi,
Just to confirm..You granted user2 execute rights to that SP directly and not through a role?





To Paraphrase:"The Help you get is proportional to the Help you give.."

 

[organickarma]

I just checked the list of grants for the package and SP's in TOAD and under type it says role...so i am assuming it has been granted through a role

 

[jimirvine]

privs granted via a role do not work, you need to grant the privs explicitly

 

[organickarma]

I run a command like this

grant execute on package1 to xyz_user

Is this not a explicit grant?

 

[Dagon]

Do you need a synonym so that the second user can access the first user's package without having to prefix it with the schema name ?

 

[organickarma]

Well folks I feel real stupid writing this post but this thread needs closure. Turns out that I do not have execute rights on our QA database. But the reports that run off the stored procedures work just fine. The error happens only when I try to run the sprocs directly.

God save us from the DBA's *rolls eyes*