CODEWITHSUNDEEP
oracle-databaseplsqloracle11g
Jonas Cristens
Jonas Cristens

Reputation: 316

Create Oracle users in a loop with default tablespace

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

Upvotes: 0

Views: 582

Answers (1)

Justin Cave
Justin Cave

Reputation: 231671

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.

Upvotes: 1

Related Questions