PL SQL 101 1

[stephenwales]

I have a task I need to do that requires me to create several objects in a database after I do a copy of production to test or training. Basically I drop some prod objects and recreate them for test.

I'd like to know how to code the following in PL/SQL, since I've never had to use PL/SQL before:

select username from dba_users where username = 'FRED';

if no rows returned
CREATE USER FRED
else
message: Fred already created

I'll keep looking through manuals to try to work it out, but if someone has a specific example, I'd appreciate it.

Thanks
Steve

 

[SantaMufasa]

Stephen,

Here is code that you could use (and modify to your heart's content) to do what you want:

accept target_user prompt "Enter the name of the user you would like to create: "
prompt If &target_user does not exist, enter the tablespace name to use for...
accept ts prompt "&target_user's DEFAULT tablespace: "
accept tmp prompt "&target_user's TEMPORARY tablespace: "
set serveroutput on format wrap
declare
    user_cnt  number;
    sql_stm   varchar2(200);
begin
    select count(*) into user_cnt
      from dba_users
     where username = upper('&target_user');
    if user_cnt <> 0 then
        dbms_output.put_line('"&target_user" already created.');
    else
        sql_stm := 'create user &target_user identified by &target_user '||
                   'default tablespace &ts temporary tablespace &tmp '||
                   'quota unlimited on &ts';
        execute immediate sql_stm;
        dbms_output.put_line('Executed: "'||sql_stm||'".');
        sql_stm := 'grant connect, resource to &target_user';
        execute immediate sql_stm;
        dbms_output.put_line('Executed: "'||sql_stm||'".');
    end if;
end;
/

You must save the above code to a script in order to run it since there are ACCEPT commands that will not work properly if you simply copy from here and paste to your SQL> prompt.

Here is invocation of the script that I saved (as UserCheckThenSetup.sql) as the code from above:

SQL> @UserCheckThenSetup
Enter the name of the user you would like to create: Fred
If Fred does not exist, enter the tablespace name to use for...
Fred's DEFAULT tablespace: data1
Fred's TEMPORARY tablespace: temp
Executed: "create user Fred identified by Fred default tablespace data1 temporary tablespace temp quota unlimited on data1".
Executed: "grant connect, resource to Fred".
SQL> @UserCheckThenSetup
Enter the name of the user you would like to create: Fred
If Fred does not exist, enter the tablespace name to use for...
Fred's DEFAULT tablespace: data1
Fred's TEMPORARY tablespace: temp
"Fred" already created.
SQL>

Let us know if this takes care of your need.

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.

 

[stephenwales]

As always, Dave, your advice is superlative. That's exactly what I needed.

Enjoy another star!

Steve

 

[sem]

Another option is to make an attempt to create it and then gracefully process an error in case that user already exists.

Regards, Dima

http://seminihin.narod.ru

 

[stephenwales]

Yes, well, the problem there is that until about 4pm yesterday, I'd never even had the opportunity to use the programmatic aspects of PL/SQL - it had all been just plain SQL and nothing else.

So "gracefully processing an error" may sound easy enough, except I haven't got a clue on how to do it.

So Mufasa's method will work fine for me for now, but thanks for the reply

Steve

 

[SantaMufasa]

Stephen,

Dima's method is a great alternative...Although the two alternatives are similar in number of statements (Original: 16, Dima's: 15), Dima's method seems simpler in logic. I want to post Dima's logic so that you can begin to become familiar with the beauties of an EXCEPTION handler. Here is the code that Dima proposes:

*********************************************************************************
set verify off
accept target_user prompt "Enter the name of the user you would like to create: "
prompt If &target_user does not exist, enter the tablespace name to use for...
accept ts prompt "&target_user's DEFAULT tablespace: "
accept tmp prompt "&target_user's TEMPORARY tablespace: "
set serveroutput on format wrap
declare
    sql_stm               varchar2(200);
    user_already_exists   exception;
    pragma exception_init(user_already_exists,-1920);
begin
    sql_stm := 'create user &target_user identified by &target_user '||chr(10)||
               'default tablespace &ts temporary tablespace &tmp '||chr(10)||
               'quota unlimited on &ts';
    execute immediate sql_stm;
    dbms_output.put_line(' ');
    dbms_output.put_line('Executed:'||chr(10)||'"'||sql_stm||'".');
    sql_stm := 'grant connect, resource to &target_user';
    execute immediate sql_stm;
    dbms_output.put_line(chr(10)||'Executed: "'||sql_stm||'".');
exception
    when user_already_exists then
        dbms_output.put_line(' ');
        dbms_output.put_line('"&target_user" already exists.');
end;
/

SQL> @usercheckthensetup2
Enter the name of the user you would like to create: fred
If fred does not exist, enter the tablespace name to use for...
fred's DEFAULT tablespace: data1
fred's TEMPORARY tablespace: temp

Executed:
"create user fred identified by fred 
default tablespace data1 temporary tablespace temp 
quota unlimited on data1".

Executed: "grant connect, resource to fred".

PL/SQL procedure successfully completed.

SQL> @usercheckthensetup2
Enter the name of the user you would like to create: fred
If fred does not exist, enter the tablespace name to use for...
fred's DEFAULT tablespace: data1
fred's TEMPORARY tablespace: temp

"fred" already exists.

PL/SQL procedure successfully completed.

SQL>

Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.