Re: Creating users conditionally on tablespace...

Steve Cummings wrote:

> 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.

"Oracle" complains here about an DDL-Statement within a PL/SQL block. But you can do it with dynamical SQL in your script:

begin
...
execute immediate 'create user ...';
...
end;

Best regards, Christoph. Received on Thu Mar 01 2001 - 16:26:58 CST