Re: Create user via stored proc/package ?

You can use DBMS_JOB to schedule a job to perform your DDL at a later time. This is the best way to do DDL in-line, without causing a commit statement each time you want to do it. The DDL will be performed after- the-fact, but it's a viable solution.

In Oracle8i, however, you can use Autonomous Transactions to perform DDL as a separate transaction, which will NOT cause an implicit commit in your current session. Keep in mind, however, using Autonomous Transactions may not be the way to do DDL each time. As an example:

Case 1. You have a session which issues a DBMS_JOB to create a set of objects for a particular database user. Your session rolls back in the end, which also rolls back the DBMS_JOB. This can be done today in version 7 and 8.

Case 2. You use Autonomous Transactions to create the set of objects for your database user. Although it's performed immediately and does not cause a commit in your transaction, let's suppose your transaction rolls back. Then the database objects are still there. This might not be your desired effect.

For more information regarding Autonomous Transactions, check out Tom Kyte's Autonomous Transactions overview @ http://govt.us.oracle.com/~tkyte/autonomous/index.html.

• Sean

Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't. Received on Thu Jul 01 1999 - 07:25:06 CDT