Re: Stored procs for adding, dropping, granting, revoking

From: Fernando Nantes De Souza <ndcdba_at_mindspring.com>
Date: 1997/12/30
Message-ID: <68a5ei$td9@camel12.mindspring.com>#1/1

You may want to try the DBMS_SQL package. It allows you to execute dynamic SQL from a stored procedure and to execute DDL statements too.

Check the example, grants DBA privileges to the user SCOTT.

CREATE OR REPLACE PROCEDURE MYPROC AS
mycursor INTEGER;
retcode INTEGER;
BEGIN
    mycursor := DBMS_SQL.OPEN_CURSOR;
    DBMS_SQL.PARSE(mycursor, 'GRANT dba TO scott', DBMS_SQL.V7);     retcode := DBMS_SQL.EXECUTE(mycursor);     DBMS_SQL.CLOSE_CURSOR(mycursor);
END; Remember that there is an implied commit when you execute DDL.

Regards,

-fernando

---

Fernando Nantes de Souza
Oracle DBA
Atlanta GA, USA
National Data Corporation

nelsonb_at_mail.vii.com wrote in message <883452864.355729411_at_dejanews.com>...
>Does anyone know how to get a stored procedure to
>accept a drop user, grant, and revoke.
>
>If you try these directly there is an error
>saying that it is illegal and the documentation
>backs this up, but is there a work around or something.
>
>I had looked at SQL_STANDARD and it had one promising
>routine in it, but it did not seem to work.
>
>Any other ideas?
>
>Thanks
>
>Bert Nelson
>nelsonb_at_mail.vii.com
>
>-------------------==== Posted via Deja News ====-----------------------
> http://www.dejanews.com/ Search, Read, Post to Usenet
Received on Tue Dec 30 1997 - 00:00:00 CST