dbms_aq.enqueue_array and type somewhere in package

From: Remigiusz Sokolowski <rems_at_wp-sa.pl>
Date: Wed, 27 Feb 2008 15:12:46 +0100
Message-id: <47C56FDE.5030207@wp-sa.pl>

hi!

today I tried to refactor some piece of pl/sql and the main concept was to replace
dbms_aq.enqueue calls within a loop with one dbms_aq.enqueue_array call.

During the development phase I made a mistake - I defined an array type within a package body and while compilation was ok, a call to dbms_aq.enqueue_array was not.
The same went with moving the type to package header. I could have been able to run this code, no errors reported during the call to
dbms_aq.enqueue_array, control went fine further, but I could not have got a proper result - msgid array was simply empty. When I made a type seen within a schema,
all troubles went away.

And a question - does anyone know, how I should trace such case? No messages in udump, no errors on screen - is that a correct behaviour of a database or simply such errors are not traceable?

A case was performed on 10gR2/Solaris10SPARC.

Thanks in advance
Remigiusz

here short snippet of the wrong code, for working one needs to move anydata_array_t
to schema as a standalone type

---

create or replace
package aq_batch_pkg as
  type anydata_array_t is varray(1000) of anydata;   g_payloads anydata_array_t := anydata_array_t();   function add_to_buffer(p_msg in anydata) return pls_integer;   function enqueue_buffer(p_qname in varchar2, p_agname in varchar2) return pls_integer;
  procedure init_buffer;
end aq_batch_pkg;

create or replace
package body aq_batch_pkg as

function add_to_buffer(p_msg in anydata) return pls_integer as   l_retval pls_integer := 1;
  l_cnt pls_integer := 0;
begin
  l_cnt := g_payloads.count;
  dbms_output.put_line('l_cnt:'||l_cnt);   if l_cnt < 1000 then
    g_payloads.extend;
    g_payloads(l_cnt+1) := p_msg;
  else
    l_retval := 0;
  end if;
  return l_retval;
end;

function enqueue_buffer(p_qname in varchar2, p_agname in varchar2) return pls_integer as

  l_enq_opts       DBMS_AQ.enqueue_options_t;
  l_msg_props      DBMS_AQ.message_properties_array_t;
  l_msg_prop       DBMS_AQ.message_properties_t;
  l_msgids         DBMS_AQ.msgid_array_t;
  l_retval         PLS_INTEGER;

BEGIN

  l_msg_prop.sender_id := sys.aq$_agent(p_agname,   NULL,   NULL);
  l_msg_props := DBMS_AQ.message_properties_array_t(l_msg_prop); 
  l_msg_props.extend(g_payloads.count-1, 1);

  dbms_output.PUT_LINE('[enq] l_msg_props cnt:'||l_msg_props.count);   dbms_output.PUT_LINE('[enq] g_payloads cnt:'||g_payloads.count);   l_retval := DBMS_AQ.ENQUEUE_ARRAY(

       queue_name               => p_qname,
       enqueue_options          => l_enq_opts,
       array_size               => g_payloads.count,
       message_properties_array => l_msg_props,
       payload_array            => g_payloads,
       msgid_array              => l_msgids);

  COMMIT;

• czyszczenie bufora g_payloads.delete; if l_msgids.count>0 then for i in l_msgids.first..l_msgids.last loop dbms_output.PUT_LINE('[enqueue] raw '||i||':'||rawtohex(l_msgids(i))); end loop; else dbms_output.PUT_LINE('[enqueue] l_msgids is empty'); end if; return l_retval; END;

procedure init_buffer as
begin
  g_payloads.delete;
end;

end aq_batch_pkg;

--

---

Remigiusz Sokolowski <rems_at_wp-sa.pl>
WP/PTI/DIP/ZAB (+04858) 52 15 770
MySQL v04.x,05.x; Oracle v10.x

Zastrzezenia:
1. Wylaczenie danej funkcjonalnosci oznacza, ze niezwlocznie przystapimy

   lub juz pracujemy nad jej uruchomieniem 2. Niniejsza wiadomosc stanowi jedynie wyraz prywatnych pogladow autora

   i nie jest w zadnym wypadku zwiazana ze stanowiskiem przedsiebiorstwa    Wirtualna Polska S.A.

---

"WIRTUALNA POLSKA" Spolka Akcyjna z siedziba w Gdansku przy ul. Traugutta 115 C, wpisana do Krajowego Rejestru Sadowego - Rejestru Przedsiebiorcow prowadzonego przez Sad Rejonowy Gdansk - Polnoc w Gdansku pod numerem KRS 0000068548, o kapitale zakladowym 67.980.024,00 zlotych oplaconym w calosci oraz Numerze Identyfikacji Podatkowej 957-07-51-216.
--

http://www.freelists.org/webpage/oracle-l Received on Wed Feb 27 2008 - 08:12:46 CST