Forum Stats

  • 3,816,029 Users
  • 2,259,131 Discussions
  • 7,893,366 Comments

Discussions

GRANT IN STORED DATABASE PROCEDURE

Ahamed Rafeeque
Ahamed Rafeeque Member Posts: 154 Bronze Badge
edited Feb 1, 2010 12:56AM in SQL & PL/SQL
Can i use GRANT IN STORED DATABASE PROCEDURE?

I got an error while i am trying this code

PROCEDURE PGRANT(FROMUSER Varchar2,TOUSER VARCHAR2,PCOL VARCHAR2) IS
CURSOR MTab IS
Select TABLE_NAME From SYS.DBA_TAB_COLUMNS Where OWNER=FROMUSER AND COLUMN_NAME=PCOL;
TYPE MTab_Type IS Table Of MTab%RowType Index By Binary_Integer;
MTable MTab_Type;
MSql Varchar2(4000);
BEGIN
Open MTab;
Loop
FETCH MTab BULK COLLECT INTO MTable LIMIT 10000;
Exit When MTab%NOTFOUND;
End Loop;

For i in 1..MTable.Count Loop
Msql:= 'Begin '||
' GRANT SELECT, UPDATE, INSERT, DELETE ON '||FROMUSER||'.'||MTable(i).Table_Name||' TO '||TOUSER||';'||
'End;';
EXECUTE IMMEDIATE MSQL;
End Loop;
END;

Error is

ERROR at line 1:
ORA-06550: line 1, column 8:
PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:
begin case declare exit for goto if loop mod null pragma
raise return select update while with <an identifier>
<a double-quoted delimited-identifier> <a bind variable> <<
close current delete fetch lock insert open rollback
savepoint set sql execute commit forall merge pipe
ORA-06512: at "TEMP.PGRANT", line 18
ORA-06512: at line 1

Any one please can help me?

Thanks,
Ahamed

Best Answer

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,329 Red Diamond
    Answer ✓
    Ahamed Rafeeque wrote:
    PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:
    GRANT is DDL and can not be executed from PL/SQL (dynamic or not). Change:
    Msql:= 'Begin '|| 
    ' GRANT SELECT, UPDATE, INSERT, DELETE ON '||FROMUSER||'.'||MTable(i).Table_Name||' TO '||TOUSER||';'|| 
    'End;';
    to
    Msql:= 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||FROMUSER||'.'||MTable(i).Table_Name||' TO '||TOUSER;
    SY.

Answers

  • Solomon Yakobson
    Solomon Yakobson Member Posts: 19,329 Red Diamond
    Answer ✓
    Ahamed Rafeeque wrote:
    PLS-00103: Encountered the symbol "GRANT" when expecting one of the following:
    GRANT is DDL and can not be executed from PL/SQL (dynamic or not). Change:
    Msql:= 'Begin '|| 
    ' GRANT SELECT, UPDATE, INSERT, DELETE ON '||FROMUSER||'.'||MTable(i).Table_Name||' TO '||TOUSER||';'|| 
    'End;';
    to
    Msql:= 'GRANT SELECT, UPDATE, INSERT, DELETE ON '||FROMUSER||'.'||MTable(i).Table_Name||' TO '||TOUSER;
    SY.
  • damorgan
    damorgan Member Posts: 14,464 Bronze Crown
    edited Feb 1, 2010 12:56AM
    You wrote:
    "GRANT is DDL and can not be executed from PL/SQL (dynamic or not)."

    Please reconsider your statement
    SQL*Plus: Release 10.2.0.2.0 - Production on Sun Jan 31 21:53:30 2010
    
    Copyright (c) 1982, 2005, Oracle.  All Rights Reserved.
    
    
    Connected to:
    Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - Production
    With the Partitioning, Oracle Label Security, OLAP, Data Mining,
    Oracle Database Vault and Real Application Testing options
    
    
    Session altered.
    
    
    Session altered.
    
    SQL> BEGIN
      2    execute immediate 'grant select on servers to scott';
      3  END;
      4  /
    
    PL/SQL procedure successfully completed.
    
    SQL> 
    It seems you know this ... but it is not what you wrote.
    damorgan
This discussion has been closed.