Re: Use of GRANT,CREATE within a PL/SQL Block

(R) Monisha Tope (monisha_at_pt8404.livonia) wrote:
: Hello oracle experts,
 

: I'm sorry if this is a FAQ.
: I'm using Oracle7 Server Release 7.1.6.2.0 and PL/SQL Release 2.1.6.2.0
 

: Does anyone know why I cannot use a GRANT statement within a PL/SQL block??
 

: example

: create trigger grnt_ppn_app_roles_trig
: before insert on tapme.ppn_app_roles for each row
: begin
: if inserting
: and (:new.username is not null)
: and (:new.role is not null)
: then
: grant connect,resource,:new.role to :new.username identified by :new.username;
: end if;
: end;
 

: I get the following error message:
: *
: ERROR at line 1:
: ORA-06550: line 8, column 3:
: PLS-00103: Encountered the symbol "GRANT" which is an unsupported reserved
: word.
: Resuming parse at line 8, column 80.
: ORA-06550: line 9, column 6:
: PLS-00103: Encountered the symbol "IF" when expecting one of the following:
: a PL/SQL variable or double-quoted string
: a simple name
: an optional simple name
: Resuming parse at line 9, column 8.
 

: Any info would be appreciated.
: Thanx in advance,
 

: Monisha Tope.
: monisha_at_tc1225.pto.ford.com

Grant is a DCL statement. DCLs and DDLS are not allowed in a PL/SQL block. However, there is a workaround, since you are using Oracle 7.1.xx. There is a package called DBMS_SQL which would allow you to execute DCL and DDL statements. But, since these statements would issue a COMMIT, you cannot issue them in a trigger.(Using them would result in a ORA-5092 error)

Hope this helps.

--
---------------------------------------------------------------------------
Kannan 
Email: skannan_at_fs.com
Mastech Systems Corporation

The above are my own comments and opinion. They do not purport that of 
anybody else.
       ** Different is Not Better. Better is not Different. **
---------------------------------------------------------------------------