-1

I have a problem.

CREATE TABLE accounts(
  id INTEGER, 
  name VARCHAR2(100)
)
/

CREATE OR REPLACE FUNCTION account_balance(account_id_in IN accounts.id%TYPE)
RETURN NUMBER
IS
BEGIN
  RETURN 0;
END;
/

Error:

Error starting at line : 1 in command -
CREATE OR REPLACE FUNCTION account_balance(account_id_in IN accounts.id%TYPE)
RETURN NUMBER
IS
BEGIN
  RETURN 0;
END;
Error report -
ORA-01031: insufficient privileges
01031. 00000 -  "insufficient privileges"
*Cause:    An attempt was made to perform a database operation without
           the necessary privileges.
*Action:   Ask your database administrator or designated security
           administrator to grant you the necessary privileges

enter image description here

Please help me resolve above error, thank you!

Vy Do
  • 46,709
  • 59
  • 215
  • 313
  • http://stackoverflow.com/questions/20988572/getting-ora-01031-insufficient-privileges-while-querying-a-table-instead-of-ora – shubhamagiwal92 Jul 18 '15 at 02:56
  • dude, no accepted answer exist. – Vy Do Jul 18 '15 at 02:58
  • 3
    The issue is pretty clear. Talk to the DBA to get the permissions to create a function. – Gordon Linoff Jul 18 '15 at 03:04
  • In this case, How DBA set permission to create function(s)? – Vy Do Jul 18 '15 at 03:05
  • 1
    @dovy: Are you the DBA? – sstan Jul 18 '15 at 03:10
  • I am DBA (when I use other account), this is my localhost. I have full permissions. I need creating function, procedures. – Vy Do Jul 18 '15 at 03:12
  • 1
    I believe the syntax is `grant create procedure to your_user` or something close to that., where you would replace `your_user` with the user you are logged in as that doesn't have the required privileges. Make sure you run this from the account that does have full permissions. – sstan Jul 18 '15 at 03:16

3 Answers3

3

As pointed out in the comments, you are missing the required permissions to create the function from whatever user account you are currently using.

Let's assume your less privileged login is called some_user. To fix your problem, login as your more privileged account, and apply the following GRANT statement:

grant create procedure to some_user;

Documentation: GRANT

CREATE PROCEDURE: Create stored procedures, functions, and packages in the grantee's schema.

Vy Do
  • 46,709
  • 59
  • 215
  • 313
sstan
  • 35,425
  • 6
  • 48
  • 66
0

Login as

sys as sysdba

then execute the following command after changing username to your username

GRANT CREATE PROCEDURE TO username;
Bu Saeed
  • 1,173
  • 1
  • 16
  • 27
0

Do like this

sqlplus /nolog
connect sys/"123456a@"@xe as sysdba

SQl> alter session set "_ORACLE_SCRIPT"=true;
SQL>grant create table to donhuvy;
Vy Do
  • 46,709
  • 59
  • 215
  • 313