How to issue CREATE USER command from a stored proc?
Date: 1996/05/31
Message-ID: <1996May31.203226.873_at_cgate.sait.ab.ca>#1/1
In article <31AB159D.1438_at_tapestry.com>, Jason Wells <jason.wells_at_tapestry.com> writes:
>Craig Harper wrote:
>>
>> I am trying to figure out how to execute a CREATE USER and
>> GRANT commands from a stored proceedure. I would like to
>> create the username (user_id) from a sequence generator, then
>> GRANT create session to the new user. Does anyone know if this
>> is possible? If so how I keep getting errors when I try.
>>
>> Thanks
>
><snip>
>
>You can't do DDL commands (like CREATE USER) from a PL/SQL procedure. Oracle
doesn't
>support it.
>--
>==============================================================================
>Jason Wells Tapestry Computing, Inc. Voice: 314.344.0066
>Senior Software Developer Email:jason.wells_at_tapestry.com Fax: 314.344.0990
>==============================================================================
Jason is correct in saying that you can't use DDL in PL/SQL. However, if you want to cheat look at the DBMS_SQL package to create dynamic SQL. I believe there was an article in Oracle Magazine in the past year explaining this. I have used dynamic SQL to create tables from within a stored procedure.
Jock Rutherford
Programmer/Analyst
Southern Alberta Institute of Technology
Email: jock.rutherford_at_sait.ab.ca Received on Fri May 31 1996 - 00:00:00 CEST