1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45
| grant create sequence to <votre_user>;
create table suivi_envoi
( ref_envoi varchar2(10)
, num_envoi varchar2( 4)
);
create or replace procedure p_create_sequence(sequence_name varchar2)
authid current_user
is
pragma autonomous_transaction;
begin
execute immediate 'create sequence ' || p_create_sequence.sequence_name || ' nocache nocycle';
exception
when others then
raise;
end;
/
create or replace trigger tgbi_suivi_envoi
before insert on suivi_envoi
for each row
declare
c$_annee_envoi constant varchar2( 4) := substr(:new.ref_envoi, -4);
v$_sql varchar2(500);
v$_val_sequence number(10) := 0;
e$_sequence_not_exists exception;
pragma exception_init(e$_sequence_not_exists, -2289);
begin
v$_sql := 'select seq_suivi_envoi_' || c$_annee_envoi || '.nextval from dual';
execute immediate v$_sql into v$_val_sequence;
:new.num_envoi := to_char(v$_val_sequence, 'fm0000');
exception
when e$_sequence_not_exists then
p_create_sequence('seq_suivi_envoi_' || c$_annee_envoi);
v$_sql := 'select seq_suivi_envoi_' || c$_annee_envoi || '.nextval from dual';
execute immediate v$_sql into v$_val_sequence;
:new.num_envoi := to_char(v$_val_sequence, 'fm0000');
end;
/ |
Partager