create or replace procedure pro as
v_sql varchar2(200);
begincreate or replace sequence SEQ_LT_id increment by 1 start with 1 maxvalue 99999999;
exec immediate seq_lt_id;
insert into 表A(id,centitycode,centityname,ilevel)
select seq_lt_id.nextval,code,cdescription,nvl(clevel,1) from 表B;
commit;
end;我是想在pro 中,直接调用SEQUENCE,总是出错.不知道为什么?如何处理?请高手给予指点.谢谢了.在SQL/PLUS中有一个SEQUENCE设置,可以使用.不知道在前端程序调用PRO时,SEQUENC是否还可用?
v_sql varchar2(200);
begincreate or replace sequence SEQ_LT_id increment by 1 start with 1 maxvalue 99999999;
exec immediate seq_lt_id;
insert into 表A(id,centitycode,centityname,ilevel)
select seq_lt_id.nextval,code,cdescription,nvl(clevel,1) from 表B;
commit;
end;我是想在pro 中,直接调用SEQUENCE,总是出错.不知道为什么?如何处理?请高手给予指点.谢谢了.在SQL/PLUS中有一个SEQUENCE设置,可以使用.不知道在前端程序调用PRO时,SEQUENC是否还可用?
execute immediate v_sql;这样。还有,在你建立sequence之前,oracle不知道有此sequence。所以你的INSERT语句也要用动态SQL来执行。最后,你的用户要显式的被赋予建立sequence的权限。在编译这个存储过程之前要先执行GRANT CREATE ANY SEQUENCE TO "UserName";
create or replace procedure prod_test2 ( V_ENTITYCODE in varchar2) as
v_sql_seq varchar(500);
v_sql varchar(5000);
begin
GRANT CREATE ANY SEQUENCE TO "USERNAME";
v_sql_seq:='create sequence SEQ_LT_id increment by 1 start with 1 maxvalue 99999999;';
execute immediate v_sql_seq;
v_sql:=' insert into tbl_basic_inter_asstype_apps( id ,centitycode,casstypecode,casstypename,ire)
values(1,1,'专业','uc_coa_sct',1);';
exec immediate v_sql;
commit;end;