Dynamic SQL and ORA-00911

Question

I have a database named Tamaris, which contains a table User. I created a trigger to create a new user in database each time a row is inserted in my User table. Here's the PL/SQL code :

CREATE OR REPLACE
TRIGGER UTILISATEUR_CREATE_USER_TRG 
AFTER INSERT ON UTILISATEUR 
FOR EACH ROW
DECLARE
  nom_compte NVARCHAR2(20 CHAR);
  str_create VARCHAR2(300);
  str_grant VARCHAR(250);
  type_compte NUMBER;
  unauthorized_exception EXCEPTION;
BEGIN

  CASE
    WHEN :new.idtypecompte = 1 THEN
      nom_compte := :new.pseudoutilisateur;
      type_compte := 1;
    WHEN :new.idtypecompte = 2 THEN
      nom_compte := 'AC_'|| :new.pseudoutilisateur;
      type_compte := 2;
    WHEN :new.idtypecompte = 3 THEN
      RAISE unauthorized_exception;
  END CASE;

  str_create := 'CREATE USER '|| nom_compte ||' IDENTIFIED BY '|| :new.passwordutilisateur ||' DEFAULT TABLESPACE tamaris TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tamaris;' ;
  EXECUTE IMMEDIATE str_create;



  IF type_compte = 1 THEN
    str_grant := 'GRANT Base_User TO '|| nom_compte ||';' ;
    EXECUTE IMMEDIATE str_grant;
  ELSE
    str_grant := 'GRANT Adv_User TO '|| nom_compte ||';' ;
    EXECUTE IMMEDIATE str_grant;
  END IF;

EXCEPTION
  WHEN unauthorized_exception THEN
      dbms_output.put_line('Impossible de créer un autre gestionnaire');
END;

When I insert a row in table User, the trigger fires and I get this :

Error during saving of modifications on "TAMARIS"."UTILISATEUR" : Line 3 : ORA-00911: Invalid character ORA-06512: at "TAMARIS.UTILISATEUR_CREATE_USER_TRG", Line 22 ORA-04088: Error in the execution of 'TAMARIS.UTILISATEUR_CREATE_USER_TRG' ORA-06512: at Line 1

For the record, the request in str_create is working outside the trigger with random parameter (only if wrapped with BEGIN; END;). Therefore I tried :

str_create := 'BEGIN CREATE USER '|| nom_compte ||' IDENTIFIED BY '|| :new.passwordutilisateur ||' DEFAULT TABLESPACE tamaris TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tamaris; END;' ;

Still not working. I would appreciate any inputs on this, thanks.

EDIT :

Content of my procedure as suggested:

CREATE OR REPLACE
PROCEDURE CREATE_USER_IN_DB(p_username IN NVARCHAR2, p_password IN UTILISATEUR.passwordutilisateur%type, p_type IN NUMBER ) AS 
BEGIN
  EXECUTE IMMEDIATE 'CREATE USER '|| p_username ||' IDENTIFIED BY '|| p_password ||' DEFAULT TABLESPACE tamaris 
  TEMPORARY TABLESPACE temp QUOTA UNLIMITED ON tamaris';

  IF p_type = 1 THEN
    EXECUTE IMMEDIATE 'GRANT Base_User TO '|| p_username;
  ELSE
    EXECUTE IMMEDIATE 'GRANT Adv_User TO '|| p_username;
  END IF;
END CREATE_USER_IN_DB;

EDIT2 :

How I call the procedure outside of trigger :

BEGIN 
CREATE_USER_IN_DB('whatever','quickpass', 2); 
END;

I get

ORA-00900: 
Invalid SQL instruction
ORA-06512: at "TAMARIS.CREATE_USER_IN_DB", line 3
ORA-06512: at line 2
00900. 00000 -  "invalid SQL statement"
*Cause:    
*Action:

Get rid of the semicolon at the end of the CREATE USER statement concatenation and let us know if that helps. In other words, there should be no semicolon after QUOTA UNLIMITED ON tamaris. Also, the GRANT statements should probably not have the trailing '; END;' on them. — Bob Jarvis - Reinstate Monica
It did helped, however now I have "Impossible to COMMIT in a trigger". — LMeyer
When you're trying to debug code that executes dynamic SQL, it's often helpful to populate a local variable (i.e. l_sql_stmt) with the string and to print that string (or write it to a table) before executing it. Then, you can see the SQL statement that is getting generated when there is an error. And if you copy and paste the SQL statement into SQL*Plus (or your favorite tool) and execute it, you'll often get a more meaningful error message. — Justin Cave
I finally got it working for some reason... Last thing I did was put the query in a varchar and used it with Execute Immediate. Thanks. — LMeyer

Justin Cave Justin Cave · Accepted Answer · 2012-06-28T17:54:50

1) As @Bob Jarvis suggested, when you build a SQL statement that you intend to pass to EXECUTE IMMEDIATE, that SQL statement should not contain a trailing semicolon ;

2) Since CREATE USER and GRANT are DDL statements, they issue implicit commits before and after they are executed. That means that you cannot call DDL statements in a trigger since a trigger cannot cause a transaction to be ended. If you really want to do this (and creating users as a side effect of inserting a row in a table seems like a very problematic architecture), you would have to do it asynchronously. Your trigger can call DBMS_JOB to schedule a job to run after your current transaction completes and that job can execute DDL statements. For example, if you create a procedure that actually creates the user (this is where all your DDL would go)

CREATE PROCEDURE create_user( p_username IN NVARCHAR2, 
                              p_password IN UTILISATEUR.passwordutilisateur%type,
                              p_type     IN NUMBER )
AS
  <<implement procedure>>

then your trigger could do something like

CREATE OR REPLACE
TRIGGER UTILISATEUR_CREATE_USER_TRG 
AFTER INSERT ON UTILISATEUR 
FOR EACH ROW
DECLARE
  nom_compte NVARCHAR2(20 CHAR);
  str_create VARCHAR2(300);
  str_grant VARCHAR(250);
  type_compte NUMBER;
  l_jobno PLS_INTEGER;
  unauthorized_exception EXCEPTION;
BEGIN

  CASE
    WHEN :new.idtypecompte = 1 THEN
      nom_compte := :new.pseudoutilisateur;
      type_compte := 1;
    WHEN :new.idtypecompte = 2 THEN
      nom_compte := 'AC_'|| :new.pseudoutilisateur;
      type_compte := 2;
    WHEN :new.idtypecompte = 3 THEN
      RAISE unauthorized_exception;
  END CASE;

  dbms_job.submit( l_jobno,
                   'BEGIN create_user( ''' || nom_compte || ''', ' || 
                                       '''' || :new.passwordutilisateur || ''', ' || 
                                       type_compte || '); END;' );
END;

Dynamic SQL and ORA-00911

1 Answers