dear *,
ada yg dah tau cara konversi dari table type ke object type dgn simple...?
berikut kode dibawah...
sekali lagi pak bowo.... minta jampi2nya biar "*saucap nyata satuduh metu*"
:)
create type TYP_AUDITMESSAGES as object (....... );
create or replace PACKAGE BODY pkg_enq AS
procedure dyn_sel (tab_name in varchar2, field_name in varchar2, val in
number, crs in out t_crs)
is
stmt varchar2(4000);
begin
stmt := 'select value(h) from ' || tab_name || ' h where ' ||
field_name || ' between sysdate-(:b1/86400) and sysdate and rownum<=200';
open crs for stmt using val;
end dyn_sel;
procedure enq_set_opt is
begin
enqueue_options.visibility := dbms_aq.immediate;
enqueue_options.delivery_mode := dbms_aq.buffered;
end enq_set_opt;
procedure enq_mesg is
cursor c1 is select
tablename,typename,queuename,wherefield,wherevalue from aq_ctl;
tc t_crs;
type tt_TYP_AUDITMESSAGES is table of TYP_AUDITMESSAGES;
t_TYP_AUDITMESSAGES tt_TYP_AUDITMESSAGES;
l_TYP_AUDITMESSAGES TYP_AUDITMESSAGES;
begin
enq_set_opt;
for i in c1 loop
dyn_sel(i.tablename, i.WHEREFIELD,to_number(i.WHEREVALUE),tc);
loop
dbms_output.put_line('before fetch');
fetch tc bulk collect into t_TYP_AUDITMESSAGES;
-- error disini....pastinya dari collection multivalue ke single
record/object
l_TYP_AUDITMESSAGES := TREAT(t_TYP_AUDITMESSAGES as
TYP_AUDITMESSAGES);
dbms_output.put_line('after fetch');
exit when tc%notfound;
dbms_aq.enqueue(
queue_name => i.queuename,
enqueue_options => enqueue_options,
message_properties => message_properties,
payload => l_TYP_AUDITMESSAGES,
msgid => message_handle);
end loop;
end loop;
commit;
end enq_mesg;
END pkg_enq;
/
--
thanks and regards
ujang | oracle dba | mysql dba
jakarta - indonesia
[Non-text portions of this message have been removed]
