create or replace procedure create_person is
str varchar2(200);
begin
str := 'create user A0000009
identified by aaaaaa
default tablespace TEMPORARY
temporary tablespace TEMPORARY
profile DEFAULT';
-- Grant/Revoke role privileges
-- || 'grant connect to A0000009;'
-- || 'grant sfgroup to A0000009;'
execute immediate str;
end create_person;这个存储过程 报 immediate 关键字错误?
怎么解决?
str varchar2(200);
begin
str := 'create user A0000009
identified by aaaaaa
default tablespace TEMPORARY
temporary tablespace TEMPORARY
profile DEFAULT';
-- Grant/Revoke role privileges
-- || 'grant connect to A0000009;'
-- || 'grant sfgroup to A0000009;'
execute immediate str;
end create_person;这个存储过程 报 immediate 关键字错误?
怎么解决?
v_sql varchar2(200);v_sql := 'create user A0000009';execute immediate v_sql;这样就可以了
用dbms_sql包吧
在805中,可以使用dbms_sql包,来解决动态sql的问题。当然,如果,你要在过程中使用create语句,首先要显示授权给这个用户。
这样写好象还是不行?create or replace procedure create_person
is
str varchar2(200);
begin
str := 'create user A0000009
identified by aaaaaa
default tablespace TEMPORARY
temporary tablespace TEMPORARY
profile DEFAULT'; dbms_sql.execute immediate str;
end create_person;
cursor_name varchar2(400);
begin
cursor_name := dbms_sql.open_cursor;
DBMS_SQL.PARSE(cursor_name, 'create user A0000009
identified by aaaaaa
default tablespace TEMPORARY
temporary tablespace TEMPORARY
profile DEFAULT',
dbms_sql.native);
dbms_sql.execute(cursor_name);
DBMS_SQL.close_cursor(cursor_name);
EXCEPTION
WHEN OTHERS THEN
DBMS_SQL.CLOSE_CURSOR(cursor_name);
end create_person;