Re: Creating a user if it does not already exists using one SQL scrip

You can't directly do this with SQL. There are no conditions on the 'CREATE USER' statement.

The way to do this is be using SQL to spool out to a file.

e.g.

set feed off pagesize 0 linesize 200 trimspool on

spool _cu.sql

select 'create user hoser identified by tiger;' from dba_users
where not exists (

   select username
   from dba_users
   where username = 'HOSER'
)
and rownum = 1
/

spool off
@_cu

That said, I think this is a poor way to do it if you plan on making extensive use of a script to create users, assign grants, etc.

PL/SQL would be cleaner, Perl DBI:DBD would be better yet.

Jared

On Thu, 29 Jun 2000, ALEMU Abiy wrote:

> How can I create a user using a sql script not PL/SQL by testing if it
> already exists before issuing the CREATE USER command. Am I obliged to use a
> dynamic sql statements in a PL/SQL block or is there a simple method only by
> using SQL ?
>
> ----------------------------------------------------------------------------
> --------
> @biy @lemu
> Cril Telecom Software
> France
>
> --
> Author: ALEMU Abiy
> INET: abiy.alemu_at_criltechnology.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access / Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
> (or the name of mailing list you want to be removed from). You may
> also send the HELP command for other information (like subscribing).
>

Jared Still
Certified Oracle DBA and Part Time Perl Evangelist ;-) Regence BlueCross BlueShield of Oregon
jkstill_at_bcbso.com - Work - preferred address Received on Fri Jun 30 2000 - 13:52:28 CDT