Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> Re: Creating a user if it does not already exists using one SQL scrip

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

From: Jared Still <jkstill_at_bcbso.com>
Date: Fri, 30 Jun 2000 11:52:28 -0700 (PDT)
Message-Id: <10544.110986@fatcity.com>


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

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US