There's a user in the database to whom CREATE PROCEDURE privelege is granted. But when that user tries to create a simple procedure the following error is thrown: ORA-01031: insufficient privileges 01031. 00000 - "insufficient privileges" *Cause: An attempt was made to change the current username or password without the appropriate privilege. This error also occurs if attempting to install a database without the necessary operating system privileges.
here's the DDL for the procedure:
CREATE OR REPLACE PROCEDURE TOTALBASE.ROUNDUP
(CUR OUT SYS_REFCURSOR )
AS
BEGIN
OPEN CUR FOR
SELECT * FROM TOTALBASE.ABONENT;
END ROUNDUP;
What else should I consider to do to make this work? I'm suspecting that even if the privelege is granted anyone who's not in the administrators or ORA_DBA group can't create a procedure. but I'm not sure.
To create a procedure in a schema other than your own, you'll need CREATE ANY PROCEDURE
privilege.
As a general rule, this privilege should not be granted lightly, as it could easily be used to circumvent database security.
Hope that helps.