Re: Creating users conditionally on tablespace...

On Thu, 1 Mar 2001 10:44:17 -0500, "Steve Cummings" <scummings_at_comshare.com> wrote:

>I have a SQL script that does the following:
>
>create tablespace TESTTMP datafile 'D:\orcl_data\TESTTMP.DAT' size 50M reuse
>AUTOEXTEND ON NEXT 50M MAXSIZE UNLIMITED;
>
>create user TESTTMP identified by password
> default tablespace TESTTMP
> temporary tablespace TEMP;
>
>create user TESTTMP_A identified by password
> default tablespace TESTTMP
> temporary tablespace TEMP;
>
>However, the script needs to also be run against versions of Oracle that
>uses the temporary tablespace called: TEMPORARY_DATA.
>I don't want to have two separate scripts, but would like a single script
>that is "smart enough" to know which tablespace to use.
>
>What I need to do is something like this:
>
> if (temporary tablespace name is TEMP) then
> create user and specify the temporary tablespace as TEMP
> elseif (temporary tablespace name is TEMPORARY_DATA) then
> create user and specify the temporary tablespace as TEMPORARY_DATA
> endif
>
>How can I do this in a PL/SQL script?
>
>I've tried doing something like this:
>
>var cnt number
>begin
>select count(*) into :cnt from user_tablespaces where tablespace_name =
>'TEMP'
>
>if (cnt > 0) then
> create user TESTTMP identified by password default tablespace TESTTMP
>temporary tablespace TEMP;
>else
> create user TESTTMP identified by password default tablespace TESTTMP
>temporary tablespace TEMPORARY_DATA;
>end if;
>end;
>
>Unfortunately, Oracle complains about the CREATE USER statement.
>
>Thank you for your suggestions/recommendations.
>
>Steve
>

First of all : create user is DDL, and DDL can't be executed in pl/sql without the aid of dbms_sql (8.0 and before) or execute immediate (8i and beyond)
secondly, I would make sure the temporary tablespace has the temporary attribute set, so you could use
select tablespace_name
from user_tablespaces
where contents = 'TEMPORARY'
instead of the far less dependable method you are using, as your method would force you to query for every individual variant.

Hth,

Sybrand Bakker, Oracle DBA Received on Thu Mar 01 2001 - 16:05:49 CST