title: Create users with pl/sql procedures
body: <p>I need help with pl/sql procedures. I created a procedure that will automatically generate username and password for new clients when the clients table is inserted into. The procedure is initialised via a trigger which executes the procedure. Problem is the insert into client table happens ok but username and password are not generated. I have have to run procedure after the insert for that to happen. </p>
  
  <pre><code>CREATE PROCEDURE procedure_new_clients 
  var_assign_user clients VARCHAR2(1000);
  BEGIN
  FOR my_c IN (SELECT client_fname, client_lname FROM clients) 
  LOOP
  var_assign_user := 'CREATE USER "' || my_c.client_fname || '" IDENTIFIED BY "' || my_c.client_lname|| '"';
  EXECUTE IMMEDIATE var_assign_user; COMMIT;
  END LOOP;
  END procedure_new_clients;
  
  
  CREATE TRIGGER trigger_new_clients
  AFTER INSERT ON clients
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  WHEN (NEW.client_id NOT NULL)
  
  DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  
  BEGIN
  procedure_new_clients();
  
  END;
  </code></pre>