如:
username varchar2(200);
passwordy varchar2(200);
begin
username:=:new.yonghuming;
passwordy:=:new.passwordy;
/*insert into temp values(username,passwordy);去掉下面过程调用结果正常*/
create_user(username,passwordy);/*如果参数是变量就出错*/
/*create_user('username','passwordy');如果参数这样指定为常量,側运行成功,*/
请高手指点
username varchar2(200);
passwordy varchar2(200);
begin
username:=:new.yonghuming;
passwordy:=:new.passwordy;
/*insert into temp values(username,passwordy);去掉下面过程调用结果正常*/
create_user(username,passwordy);/*如果参数是变量就出错*/
/*create_user('username','passwordy');如果参数这样指定为常量,側运行成功,*/
请高手指点
CREATE OR REPLACE PROCEDURE JGXT_ADMIN.yonghu_create_producetotigger(y_username varchar2,y_password varchar2) authid current_user IS
PRAGMA AUTONOMOUS_TRANSACTION;
username_y yonghu.YONGHU%type;
password_y yonghu.PASSWORDY%type;BEGIN
username_y := y_username;
password_y:=y_password;
execute immediate 'create user '||username_y ||' identified by '||password_y||' default tablespace jgxt ';
execute immediate 'grant connect,select any table,select any sequence,select any dictionary to '||username_y;
EXCEPTION
WHEN NO_DATA_FOUND THEN
NULL;
WHEN OTHERS THEN
RAISE;
END yonghu_create_producetotigger;
--给你改一下存贮过程,trigger也有错误,请看看这方面的书吧。 CREATE OR REPLACE PROCEDURE JGXT_ADMIN.yonghu_create_producetotigger(y_username in varchar2,
y_password in varchar2,
y_user in varchar2) IS
BEGIN
create user y_username identified by y_password default tablespace jgxt;
grant connect,
select any table,
select any sequence, select any dictionary to y_user;
END yonghu_create_producetotigger;
AFTER INSERT
ON JGXT_ADMIN.YONGHU
REFERENCING NEW AS NEW OLD AS OLD
FOR EACH ROW
DECLAREusername1 yonghu.yonghu%type;
passwordy1 yonghu.passwordy%type;
BEGIN username1 :=:NEW.YONGHU; /*改为常量运行通过*/
passwordy1:=:NEW.PASSWORDY; /*改为常量运行通过*/
/* insert into temp values(username,passwordy); */
yonghu_create_producetotigger(username1,passwordy1);
EXCEPTION
WHEN OTHERS THEN
-- Consider logging the error and then re-raise
RAISE;
END ;
/
转换时把它转为NUMBER型了所以出错,
请请BlueskyWide 了,但不懂不要装懂,会误导的,这理涉及到自主事务向真正的高手学习