首页 文章

动态SQL和ORA-00911

提问于
浏览 次
2

我有一个名为Tamaris的数据库，其中包含一个表User . 每次在User表中插入行时，我都创建了一个触发器来在数据库中创建新用户 . 这是PL / SQL代码：

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;

当我在表User中插入一行时，触发器会触发，我得到这个：

在“TAMARIS”上保存修改时出错 . “UTILISATEUR”：第3行：ORA-00911：无效字符ORA-06512：在“TAMARIS.UTILISATEUR_CREATE_USER_TRG”第22行ORA-04088：执行'TAMARIS.UTILISATEUR_CREATE_USER_TRG'时出错ORA-06512：在第1行

对于记录，str_create中的请求在随机参数的触发器外工作（仅当包含BEGIN; END;）时 . 因此我试过：

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

还是行不通 . 我很感激对此的任何意见，谢谢 .

EDIT ：

我建议的程序内容：

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 ：

我如何在触发器之外调用该过程：

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

我明白了

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:
oracle plsql

1 回答

  • 2

    1）正如@Bob Jarvis建议的那样，当你构建一个你打算传递给 EXECUTE IMMEDIATE 的SQL语句时，该SQL语句不应该包含一个尾随的分号 ;

    2）由于 CREATE USER 和 GRANT 是DDL语句，它们在执行之前和之后发出隐式提交 . 这意味着您无法在触发器中调用DDL语句，因为触发器不会导致事务结束 . 如果你真的想这样做（并且创建用户作为在表中插入行的副作用似乎是一个非常有问题的架构），你将不得不异步执行 . 您的触发器可以调用 DBMS_JOB 来安排在当前事务完成后运行的作业，并且该作业可以执行DDL语句 . 例如，如果您创建一个实际创建用户的过程（这是您的所有DDL将去的地方）

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

    然后你的触发器可以做类似的事情

    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;
    回复于

相关问题