Creating users conditionally on tablespace...

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 Received on Thu Mar 01 2001 - 09:44:17 CST