GRANT IN STORED DATABASE PROCEDURE

Ahamed Rafeeque

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

Solomon Yakobson

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.

Solomon Yakobson

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

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.