Create Oracle users in a loop with default tablespace

Question

I try to execute this but it gives an error at the default tablespace. I want to give a default tablespace to the users I create but how?

BEGIN
  FOR USERNAME IN (SELECT studentname from students)
  LOOP
    EXECUTE IMMEDIATE 'CREATE USER ' || USERNAME.studentname  || ' IDENTIFIED BY ' || USERNAME.studentname;
    EXECUTE IMMEDIATE 'DEFAULT TABLESPACE "USERS"'; 
    EXECUTE IMMEDIATE 'TEMPORARY TABLESPACE "TEMP"';
    EXECUTE IMMEDIATE 'GRANT STUDENT TO ' || USERNAME.studentname ;                                    
  END LOOP;
END;


Error report - ORA-00900: invalid SQL statement ORA-06512: at line 5 00900. 00000 - "invalid SQL

Specifying the error is useful. Your first three statements look like they should all be part of a single statement. You'd want a single EXECUTE IMMEDIATE that did the whole CREATE USER including the default and temporary tablespace. Of course, you'd want to ensure that you have appropriate whitespace in your DDL statement (spaces before DEFAULT and TEMPORARY for example). — Justin Cave
– Justin Cave, Commented Nov 26, 2016 at 10:54
The first three lines should be a single statement. There is no valid SQL statement default tablespace — user330315
– user330315, Commented Nov 26, 2016 at 10:58
Error report - ORA-00900: invalid SQL statement ORA-06512: at line 5 00900. 00000 - "invalid SQL statement" *Cause: *Action: — Jonas Cristens
– Jonas Cristens, Commented Nov 26, 2016 at 12:16
When I make one statement of the first three, I get the following error Error report - ORA-00922: missing or invalid option ORA-06512: at line 4 00922. 00000 - "missing or invalid option" *Cause: *Action: — Jonas Cristens
– Jonas Cristens, Commented Nov 26, 2016 at 12:17

Jonas Cristens · Accepted Answer · 2016-11-26 17:51:57Z

1

You need to combine the first three statements into one and add in appropriate spaces. You don't need the double-quotes around the tablespace names since you're using case-insensitive identifiers.

BEGIN
  FOR USERNAME IN (SELECT studentname from students)
  LOOP
    EXECUTE IMMEDIATE 'CREATE USER ' || USERNAME.studentname  || ' IDENTIFIED BY ' || USERNAME.studentname ||
                      '  DEFAULT TABLESPACE USERS  
                        TEMPORARY TABLESPACE TEMP';
    EXECUTE IMMEDIATE 'GRANT UNLIMITED TABLESPACE TO ' || USERNAME.studentname;
    EXECUTE IMMEDIATE 'GRANT STUDENT TO ' || USERNAME.studentname ;                                    
  END LOOP;
END;

Personally, I'd always generate a string with the SQL statement and pass that string to EXECUTE IMMEDIATE. That makes it easy to do things like log the SQL statements that are executed or to log which statement failed. That's going to make debugging far easier.

edited Nov 26, 2016 at 17:51

Jonas Cristens

3161 gold badge2 silver badges13 bronze badges

answered Nov 26, 2016 at 12:16

Justin Cave

233k25 gold badges378 silver badges395 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Create Oracle users in a loop with default tablespace

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related