Thx, akan segera di coba
On 8/28/06, Tomi Wijanto <[EMAIL PROTECTED]> wrote:
>
> 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
>
>
>
>
>
>
>
--
Handy Sanjaya ST.
Multimedia Services
PT Indonesia Comnets Plus (ICONPLN)
Indonesia Power Building 9th Floor
Jl Jendral Gatot Subroto Kav. 18
Jakarta 12950
email : [EMAIL PROTECTED]
web : www.iconpln.net.id
telp : 021 - 7532488 - ext : 156
[Non-text portions of this message have been removed]
--
-----------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/
