hmm..coba cek query nya , sepertinya ada implicit casting dari kolom tertentu dan sedikit masukan dari saya coba waktu open cursor nggak usah pake quote langsung aja begini : open cur for select * from bla bla..
regards, pandu wp --- On Wed, 12/3/08, Agustinus Vindy <[EMAIL PROTECTED]> wrote: From: Agustinus Vindy <[EMAIL PROTECTED]> Subject: Re: [indo-oracle] pemanggil package yang mengandung cursor To: [email protected] Date: Wednesday, December 3, 2008, 12:38 AM thanks mas Wadi, sudah saya coba pake report_ap_0043a. ap_0043a( to_date(' 01/11/2008' ,'dd/mm/yyyy' ),to_date( '05/11/2008' ,'dd/mm/yyyy' ),'POF',cur) ; masih saja muncul error yang sama --- On Wed, 12/3/08, Wadi Achmed <[EMAIL PROTECTED] com> wrote: From: Wadi Achmed <[EMAIL PROTECTED] com> Subject: Re: [indo-oracle] pemanggil package yang mengandung cursor To: indo-oracle@ yahoogroups. com Date: Wednesday, December 3, 2008, 6:48 AM Hi, pemanggilnya harusnya seperti ini report_ap_0043a. ap_0043a( to_date(' 01/11/2008' ,'dd/mm/yyyy' ),to_date( '05/11/2008' ,'dd/mm/yyyy' ),'POF',cur) ; Cheers, Wadi Achmed Agustinus Vindy wrote: > Dear all, > > saya buat package dan prosedure pemanggilnya seperti ini.. > > =======package head======== === > > create or replace package report_ap_0043a is > > -- Author : ADMINISTRATOR > -- Created : 25/03/2004 10:09:05 AM > -- Purpose : > > -- Public type declarations > type CursorDummy is REF CURSOR; > > Procedure ap_0043a(TglAwal IN DATE,TglAkhir IN DATE,NamaSchema IN > Varchar2,CurOut OUT CursorDummy) ; > > end report_ap_0043a; > > > > > ======package body======== === > > create or replace package body report_ap_0043a is > > Procedure ap_0043a(TglAwal IN DATE,TglAkhir IN DATE,NamaSchema IN > Varchar2,CurOut OUT CursorDummy) > Is > Begin > Open CurOut For > 'select > a.mata_uang, > a.tanggal_lpb tgl_lpb, > a.nomor_lpb no_lpb, > b.nomor_spp no_spp, > (select g.nama from finance.relasi g where g.kode = a.kode_supplier) supplier, > e.nama_barang spesifikasi_ barang, > c.kode_rekening, > b.qty jumlah_barang, > e.satuan, > c.harga harga_satuan, > --c.potongan diskon, > 0 diskon, > decode(a.mata_ uang,''IDR' ',0,c.harga * b.qty) jumlah_valas, > decode(a.mata_ uang,''IDR' ',c.harga * b.qty,c.harga * b.qty * decode((select > kurs_mkeu from finance.kurs where kurs.kode_mata_ uang = a.mata_uang and > kurs.tanggal = a.tanggal_kurs) ,null,0,( select kurs_mkeu from finance.kurs > where kurs.kode_mata_ uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs) > )) jumlah_rupiah, > (select max(f.tanggal_ kirim) from '||NamaSchema| |'.spp_rencana_ kirim f > where f.nomor_spp = b.nomor_spp and f.nomor_sip = b.nomor_sip and f.item_sip > = b.item_sip) tanggal_kiriman, > a.jenis_ppn ppn, > decode(a.jenis_ ppn,''I'' , > (decode(a.mata_ uang,''IDR' ',0,c.harga * b.qty) - (decode(a.mata_ > uang,''IDR' ',0,c.harga * b.qty) * 0.1)), > --decode(a.mata_ uang,''IDR' ',0,c.harga * c.qty) + (decode(a.mata_ > uang,''IDR' ',0,c.harga * c.qty)/11) > decode(a.mata_ uang,''IDR' ',0,c.harga * b.qty ) > ) total_valas, > decode(a.jenis_ ppn,''I'' , > decode( > a.mata_uang, > ''IDR'', > ((c.harga * b.qty) - ((c.harga * b.qty) * 0.1)), > ( ((c.harga * b.qty) * ((decode((select kurs_mkeu from finance.kurs where > kurs.kode_mata_ uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs) > ,null,0,( select kurs_mkeu from finance.kurs where kurs.kode_mata_ uang = > a.mata_uang and kurs.tanggal = a.tanggal_kurs) )))) - (((c.harga * b.qty) * > ((decode((select kurs_mkeu from finance.kurs where kurs.kode_mata_ uang = > a.mata_uang and kurs.tanggal = a.tanggal_kurs) ,null,0,( select kurs_mkeu > from finance.kurs where kurs.kode_mata_ uang = a.mata_uang and kurs.tanggal = > a.tanggal_kurs) )))) * 0.1) ) ), > --(c.harga * b.qty * decode((select kurs_tengah_ bi from finance.kurs where > kurs.kode_mata_ uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs) > ,null,0,( select kurs_tengah_ bi from finance.kurs where kurs.kode_mata_ uang > = a.mata_uang and kurs.tanggal = a.tanggal_kurs) ))) + > --(decode(a. mata_uang, ''IDR'',c. harga * b.qty,c.harga * b.qty * > decode((select kurs_tengah_ bi from finance.kurs where kurs.kode_mata_ uang = > a.mata_uang and kurs.tanggal = a.tanggal_kurs) ,null,0,( select kurs_tengah_ > bi from finance.kurs where kurs.kode_mata_ uang = a.mata_uang and > kurs.tanggal = a.tanggal_kurs) )) * 0.1), > decode( > a.mata_uang, > ''IDR'', > c.harga * b.qty, > c.harga * b.qty * decode((select kurs_mkeu from finance.kurs where > kurs.kode_mata_ uang = a.mata_uang and kurs.tanggal = a.tanggal_kurs) > ,null,0,( select kurs_mkeu from finance.kurs where kurs.kode_mata_ uang = > a.mata_uang and kurs.tanggal = a.tanggal_kurs) )) > ) total_rupiah > > from '||NamaSchema| |'.lpb_head a > join '||NamaSchema| |'.lpb_item b on (a.nomor_lpb = b.nomor_lpb) > left join '||NamaSchema| |'.spp_item c on (b.nomor_spp = c.nomor_spp and > b.nomor_sip = c.nomor_sip and b.item_sip = c.item_sip) > left join '||NamaSchema| |'.sip_item e on (b.nomor_sip = e.nomor_sip and > b.item_sip = e.item_sip) > where a.barang_jasa = ''B'' and a.tanggal_lpb >= '''||TglAwal| |''' and > a.tanggal_lpb <= '''||TglAkhir| |''''; > > End ap_0043a; > > end report_ap_0043a; > > > > > -> aku bikin prosedure pemanggil dari package itu seperti ini > ============ ========= ========= ========= ========= ========= ========= === > create or replace procedure pemanggil_package_ AP is > > type curOut is REF CURSOR; > cur curOut; > table_ap TEMP_AP_0043A% rowtype; > --TEMP_AP_0043A = table temporary yang field nya sama dengan hasil kursor > > begin > > report_ap_0043a. ap_0043a( '01/11/2008' ,'05/11/2008' ,'POF',cur) ; > > > loop > fetch cur into table_ap; > dbms_output. put_line( cur%rowcount) ; > dbms_output. put_line( table_ap. no_lpb); > exit when cur%notfound; > end loop; > > end pemanggil_package_ AP; > ============ ========= ========= ========= ========= ========= ========= === > > tapi pas aku jalankan lewat muncul error seperti ini ... > > exec pemanggil_package_ AP; > > begin pemanggil_package_ AP; end; > > ORA-06502: PL/SQL: numeric or value error > ORA-06512: at "FINANCE.PEMANGGIL_ PACKAGE_AP" , line 13 > ORA-06512: at line 1 > > > kira2 apa ya? > mohon bantuannya.. > > > Makasih..:) > > > > [Non-text portions of this message have been removed] [Non-text portions of this message have been removed]
