To create dynamic sequence name

schrosource1

Here is my sql:

drop sequence archive_search_seq_41;

create or replace procedure sequence_create

as
tabname varchar2(100);
my_sql  varchar2(1000);

begin
select max(company_id) into tabname from company_tab;
my_sql := N'create sequence archive_search_seq_'||tabname;

execute immediate my_sql;
Commit;
end sequence_create;
/
execute sequence_create;
show errors;

But displays:

ERROR at line 1:
ORA-01031: insufficient privileges
ORA-06512: at "SEBASTIAN.SEQUENCE_CREATE", line 11
ORA-06512: at line 1

But if I use:  create sequence archive_search_seq_41;
It workls.
Is there any body could help me to solve it? That will be great.
Thanks a lot!

rollingpig

grant create sequence to <username> ;

and run the procedure again.

The resean is :
Oracle does not recognize the privilege grant by ROLE in PL/SQL.
And "create sequence " is grant to user by role "CONNECT"
So we need to grant "create sequence " to user directly ~~

remen

ORA-01031: insufficient privileges
提示很明显拉！

schrosource1

感谢二位，sequence 问题已解决。又遇到新问题：trigger

我的SQL 如下：

drop table archive_search_tab_41;
drop sequence archive_search_seq_41;

create or replace procedure sequence_create

as
tabname varchar2(100);
my_sql  varchar2(1000);

begin
select max(company_id) into tabname from company_tab;

my_sql := N'create table archive_search_'||tabname||'( Email_serial int primary key, company_id int, group_id int )';
Execute immediate my_sql;

my_sql := N'create sequence archive_search_seq_'||tabname;

execute immediate my_sql;
Commit;

my_sql := N'create or replace trigger archive_search_bri_' ||tabname||'before insert on archive_search_'||tabname||'for each row begin select archive_search_seq_'||tabname||'.nextval into:new.email_serial from dual;end;/';
execute immediate my_sql;
commit;

end sequence_create;
/

connect / as sysdba;
grant create sequence to sebastian;
grant create trigger to sebastian;
connect sebastian/aaaa;
execute sequence_create;
show errors;


出错信息：
ERROR at line 1:
ORA-04071: missing BEFORE, AFTER or INSTEAD OF keyword
ORA-06512: at SEBASTIAN.SEQUENCE_CREATE, line 19
ORA-06512: at line 1


PL/SQL 可以这样嵌套在另一个PL/SQL里吗？

再次感谢二位大侠。

schrosource1

对不起，第一行应为：

drop table archive_search_41;

schrosource1

做了改进后：

drop table archive_search_41;
drop sequence archive_search_seq_41;

create or replace procedure sequence_create

as
tabname varchar2(100);
my_sql  varchar2(1000);

begin
select max(company_id) into tabname from company_tab;

my_sql := N'create table archive_search_'||tabname||'( Email_serial int primary key, company_id int, group_id int )';
Execute immediate my_sql;

my_sql := N'create sequence archive_search_seq_'||tabname;

execute immediate my_sql;
Commit;

my_sql := N'create or replace trigger archive_search_bri_' ||tabname||' before insert on archive_search_'||tabname||' for each row begin select archive_search_seq_'||tabname||'.nextval into:new.email_serial from dual;end';
execute immediate my_sql;
commit;

end sequence_create;
/

connect / as sysdba;
grant create sequence to sebastian;
grant create trigger to sebastian;
connect sebastian/aaaa;
execute sequence_create;
show errors;


出错信息：
ERROR：
ORA-24344: success with compilation error
ORA-06512: at SEBASTIAN.SEQUENCE_CREATE, line 19
ORA-06512: at line 1

show errors 看不出compilation error.  真是没办法。还望各位大侠指点，多谢！