Utk kasus seperti ini, Anda mesti membuat dahulu
sequence utk semua tahun-bulan yg dibutuhkan.
Utk mengecek duplikasi, lebih baik di-handle dari
exception saja.
Contoh prosedurnya seperti ini:
--sequence utk bulan aug-oct 2006
create sequence EQT_HOLDER_ID_200608;
create sequence EQT_HOLDER_ID_200609;
create sequence EQT_HOLDER_ID_200610;
create table EQT_HOLDER_ID (id number primary key);
set serveroutput on
declare
v_id number;
v_yearmonth varchar2(6);
begin
--variabel ini diset dulu
v_yearmonth := to_char(sysdate,'yyyymm');
execute immediate
'select EQT_HOLDER_ID_' || v_yearmonth
|| '.nextval into :v_id from dual' into v_id;
dbms_output.put_line('id : ' || v_id);
insert into EQT_HOLDER_ID values (v_id);
commit;
exception
--utk handle duplikat
when dup_val_on_index then
dbms_output.put_line('error duplikat id: ' || v_id);
end;
/
--- Handy <[EMAIL PROTECTED]> wrote:
> Dear All,
>
> Saya ingin membuat SP. SP tersebut berupa perintah
> insert, tp sblm insert
> harus men-cek PK/ID yg exist dan increment by one
> dgn pola thn dan bulan
> (cth 200608XXX). Setiap terjadi perubahan bulan dan
> tahun PK/ID akan berubah
> sesuai thn dan bln.
>
> Saya sdh mencoba mengunakan CURRVAL tp ada error
> message "PL/SQL: ORA-02289:
> sequence does not exist". Saya sudah coba
> menggunakan sequence tp gk
> berhasil(kemungkinan besar sequence yg saya gunakan
> salah).
>
> Berikut contoh sp yg kira2 akan saya buat :
>
>
> CREATE OR REPLACE PROCEDURE sp_EQTHolder (eqt_name
> IN VARCHAR2, usr_lbl IN
> VARCHAR2,
> native_ems_name IN VARCHAR2,owner IN VARCHAR2,
> alrm_rep_indc IN NUMBER, me_id IN VARCHAR2,
> hold_state_id IN VARCHAR,
> ref_eqt_type IN VARCHAR, ref_holder_state_id IN
> VARCHAR2,
> prt_hold_id IN VARCHAR2, eqt_id OUT VARCHAR2)
> IS
> temp_id VARCHAR2(10);
> BEGIN
>
> /* Rencananya ini buat ngecek ID yg exist tp gagal,
> :D */
> SELECT EQT_HOLDER_ID.CURRVAL INTO temp_id FROM
> TBL_EQT_HOLDERS;
>
> /* Bagian utk insert */
> INSERT INTO TBL_EQT_HOLDERS(EQT_HOLDER_ID,
> EQTS_HOLDER_NAME, ME_ID,
> USER_LABEL,
> NATIVE_EMS_NAME, OWNER, ALARM_REPT_INDICATOR,
> HOLDER_TYPE_ID, EXP_OR_INST_EQT_TYPE_ID,
> HOLDER_STATE_ID, PARENT_HOLDER_ID )
> VALUES(temp_id, eqt_name, me_id, usr_lbl,
> native_ems_name, owner,
> alrm_rep_indc,
> ref_holder_state_id, ref_eqt_type,
> hold_state_id, prt_hold_id);
>
> eqt_id:=temp_id;
> END;
>
> Mohon bantuan dan masukan nya.
>
> BR
>
> HS
>
__________________________________________________
Do You Yahoo!?
Tired of spam? Yahoo! Mail has the best spam protection around
http://mail.yahoo.com
--
-----------I.N.D.O - O.R.A.C.L.E---------------
Keluar: [EMAIL PROTECTED]
Website: http://indo-oracle.blogspot.com
Mirror: http://indooracle.wordpress.com
-----------------------------------------------
Bergabung dengan Indonesia Thin Client User Groups,
Terminal Server, Citrix, New Moon Caneveral, di:
http://indo-thin.blogspot.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/indo-oracle/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
