Oracle Stored Procedure Permissions Error?

[mikecs]

Is someone able to help, please? I can't run a procedure from Visual Basic if I use any log-in to Oracle other than the one which created it.

I log into Oracle from VB as follows (not as the package / procedure creator, not in that schema):

Set OraSession = CreateObject("OracleInProcServer.XOraSession")
Set OraDatabase = OraSession.OpenDatabase("CPAYT", "myname/mypassword", ORADB_DEFAULT)
OraDatabase.Parameters.Add "pDept", "Init", ORAPARM_INPUT
OraDatabase.Parameters("pDept").serverType = ORATYPE_VARCHAR
OraDatabase.Parameters("pDept").Value = strDepartment

OraDatabase.Parameters.Add "pSurname", "Init", ORAPARM_INPUT
OraDatabase.Parameters("pSurname").serverType = ORATYPE_VARCHAR
OraDatabase.Parameters("pSurname").Value = UCase(Me.txtSurname.Text)

Set OraDynaset = OraDatabase.CreatePlsqlDynaset _
("BEGIN Pkg9.spPersonSearchpDept, Surname, :Surret); End;", _
"SurRet", 0&)
and so on.

On Oracle I have created the following package / procedure:

CREATE OR REPLACE PACKAGE Pkg9 AS
TYPE empcur IS REF CURSOR;

PROCEDURE spPersonSearch
(
pDept IN varchar2,
pSurname IN varchar2,
SurRet IN OUT empcur
) ;

END Pkg9;
/


CREATE OR REPLACE PACKAGE BODY Pkg9
AS

PROCEDURE spPersonSearch
( pDept IN varchar2,
pSurname IN varchar2,
SurRet IN OUT empcur )

IS

BEGIN
if SurRet%ISOPEN
then
close SurRet;
end if;

open SurRet for
SELECT DISTINCT TUER_PEOPLE_V.PERSONAL_REFERENCE, TUER_PEOPLE_V.SURNAME Surname, TUER_PEOPLE_V.FORENAME1 Forename
FROM
TUNIT_STRUCT_V TUV, TPARTY_LNK RDL,
TPARTY RDN, TPARTY POSITION_NAME, TUER_PEOPLE_V TUER_PEOPLE_V, TPER_PARTY TPER_PARTY, TPARTY CONTRACT_NAME, TPARTY_LNK TPARTY_LNK
WHERE
RDL.PARTY_ID2 = RDN.PARTY_ID
AND TUV.LEAF = RDN.PARTY_ID
AND RDL.PARTY_ID = POSITION_NAME.PARTY_ID
AND TUER_PEOPLE_V.PERSON_ID = TPER_PARTY.PERSON_ID
AND TPER_PARTY.PARTY_ID = CONTRACT_NAME.PARTY_ID
AND CONTRACT_NAME.PARTY_ID = TPARTY_LNK.PARTY_ID
AND POSITION_NAME.PARTY_ID = TPARTY_LNK.PARTY_ID2
AND (TPARTY_LNK.PARTY_LNK_D <=sysdate OR TPARTY_LNK.PARTY_LNK_D Is Null) AND TUV.UNIT_NM_01 = 'COL'
AND TUV.UNIT_ID_02= pDept AND TPARTY_LNK.LINK_TYPE_ID = 'CONT' AND
TPARTY_LNK.TEAM_PARTY_ID Is Null AND RDN.OBJECT_TYPE = 'UNIT'
AND TUER_PEOPLE_V.SURNAME = pSurname
ORDER BY TUER_PEOPLE_V.SURNAME, TUER_PEOPLE_V.FORENAME1;

EXCEPTION
when NO_DATA_FOUND then
Null;

END spPersonSearch;

END Pkg9;
/


I received the following error message:

ORA-06550: line 1, column 7: PLS-00201: identifier 'PKG9.SPPERSONSEARCH' must be declared


I've tried granting permissions on the package and the procedure, in many ways, but without success. e.g.

SQL> grant execute on spPersonSearch to PUBLIC;
grant execute on spPersonSearch to PUBLIC
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


SQL> grant execute on Pkg9.spPersonSearch TO PUBLIC
2 /
grant execute on Pkg9.spPersonSearch TO PUBLIC
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist


SQL> grant execute on Pkg9 TO PUBLIC
2 /

Grant succeeded.

SQL> grant execute on spPersonSearch To PUBLIC
2 /
grant execute on spPersonSearch To PUBLIC
*
ERROR at line 1:
ORA-04042: procedure, function, package, or package body does not exist

If anyone can help, I'd be very grateful.

Thanks

Mike

 

[LKBrwnDBA]

Before you can "grant" execution, you have to either login with a DBA account and specify the procedure owner in the "grant" -- or -- login as the owner and do the grant.


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb

 

[ddiamond]

LKBrwnDBA,

I would agree with your assessment, but according to Mike's output, his grant execute on pkg9 was successful. That part confuses me, becuase if Mike successfully granted execute on pkg9 to public, than everyone should have access to all of the stored procedures within pkg9. What am I missing?

- Dan

 

[LKBrwnDBA]

Maybe he does not have a PUBLIC synonym? -- or -- added the name of the package owner:

Set OraDynaset = OraDatabase.CreatePlsqlDynaset _
("BEGIN <schema>.Pkg9.spPersonSearchpDept, Surname, :Surret); End;", _
"SurRet", 0&)


----------------------------------------------------------------------------
The person who says it can't be done should not interrupt the person doing it. -- Chinese proverb