Grant permission to stored procedure in sql script

Question

I have a .sql file with create procedure statements followed by a grant statement as shown below.

    create or replace
    PROCEDURE PROCEDURE_NAME()
    AS 
      BEGIN
      .....................
      END;

GRANT EXECUTE ON PROCEDURE_NAME TO ROLE_NAME;

If I remove the grant statement everything works fine. However with grant statement it gives the following error.

Error(23,1): PLS-00103: Encountered the symbol "GRANT"

What am I missing here ? Can grant statement be not included in script as it does when we use it with create statements ?

score 5 · Accepted Answer · answered Feb 15 '18 at 10:10

5

You're missing a slash, I presume, behind the END;

create or replace
    PROCEDURE PROCEDURE_NAME()
    AS 
      BEGIN
      .....................
      END;
/                --> this

GRANT EXECUTE ON PROCEDURE_NAME TO ROLE_NAME;

answered Feb 15 '18 at 10:10

Littlefoot

131,892
15
35
57

score 1 · Answer 2 · answered Feb 15 '18 at 10:21

In sqlplus, for normal sql statements like select, insert, delete DDLs (ALTER,CREATE TABLE etc, either a / , or a ; at the end of the query, works fine.

However, for PL/SQL codes such as those which include CREATE FUNCTION, CREATE PROCEDURE, CREATE PACKAGE, CREATE TYPE, or anonymous blocks (DECLARE/BEGIN/END), a semicolon at the end is not sufficient. We need a / to execute the command.

So, Include a / after END; and it should work fine.

oracle SQL plus how to end command in SQL file?

Grant permission to stored procedure in sql script

2 Answers2