This content has been marked as final. Show 2 replies
You might misunderstand how sequence work.
I deleted ROW number 12(as Primary Key)...AFTER i creating new record it taking Sequence number 13???Do you have any special reason why next value in your case need to be again 12?
It should be coming last number as 12?
My Blog: http://dbswh.webhop.net/htmldb/f?p=BLOG:HOME:0
@Jari...thank you for your reply..i understand and clear from your guidelines ..
my Special Reason is
I have bulk data uploading entry at one time.Reference # based on Seq number generating by the trigger.so i need both of numbers should be same.
P2_DOC_REF_NO with default value as pl/sql fn body
Please if u have any special root for solving the issue!
Declare Ref_val_ varchar2(16):= Null; Seq_Val_ Number; begin if :P2_DOC_ID is not null and :P3_BTA_REF is null then Select DOC_ID into Seq_Val_ from DMS_MASTER where doc_id=:p2_doc_id; select 'DOC'||'/'||lpad(Seq_Val_,7,0)||'/'||to_char(sysdate,'YY') into Ref_val_ from dual; -- Populate ref # return Ref_val_; elsif :P3_BTA_REF is null and :P2_DOC_ID is null then Select Nvl(count(DOC_ID),0) + 1 into Seq_Val_ from DMS_MASTER; select 'DOC'||'/'||lpad(Seq_Val_,7,0)||'/'||to_char(sysdate,'YY') into Ref_val_ from dual; -- ref number return Ref_val_; else return Ref_val_; end if; end;