create table emp(empid number,name varchar2(20));
empid name
----- ---
1 ss
2 s
3 sd
create table empbkp( name varchar2(20));
name
---
ff
rr
create sequence seq_empid start with 1 increment by 1;
create or replace package data_load
as
procedure emp_data_load;
end;
create or replace package body data_load
as
procedure emp_data_load
is
ln_id number;
begin
delete from empfinal;
insert into empfinal
(empid,name)
(select empid,name from emp);
execute immediate 'drop sequence seq_empid';
select max(empid)+1 into ln_id from empfinal;
execute immediate 'create sequence seq_empid start with ' ||ln_id|| 'increment by 1';
execute immediate 'GRANT SELECT ON seq_empid TO xy_role';
insert into empfinal
(empid,name)
(select seq_empid.nextval,name from empbkp);
commit;
exception
when OTHERS then
ROLLBACK;
end emp_data_load;
end data_load;
call data_load.emp_data_load();
When i am executing the package , sequence is dropped and but it is not creating and giving below error.
getting ORA-01031: insufficient privileges error
I have tried with AUTHID CURRENT_USER creating standalone stored proc then it is dropping and recreating ,but in side package i can't do.
Could you please hep any one.
Thanks in Advance