Oracle: problem with constructing JMS message

Question

After some struggle with Oracle Advanced Queuing and dbms_aq package I encountered another issue. I copied code from Oracle tutorials but when I compile this code:

create or replace
procedure jms_test(msg varchar2)
is
    id                 pls_integer;
    message            sys.aq$_jms_stream_message;
    enqueue_options    dbms_aq.enqueue_options_t;
    message_properties dbms_aq.message_properties_t;
begin
    message := sys.aq$_jms_stream_message.construct(0);
    message.set_string_property('FROM', 'TEST');
    id := message.clear_body(-1);
end;

it complains with:

Error(9,40): PLS-00302: component 'CONSTRUCT' must be declared
Error(10,10): PLS-00302: component 'SET_STRING_PROPERTY' must be declared
Error(11,16): PLS-00302: component 'CLEAR_BODY' must be declared

I think this code works out of procedure body, because I tried with success recipes from What's in my JMS queue?

My Oracle version is: Oracle9i Enterprise Edition Release 9.2.0.1.0 - Production

Any idea what can be wrong?

Answer 1

Looks like grants again

GRANT EXECUTE ON SYS.aq$_jms_stream_message To <your-user>;

Does:

desc sys.aq$_jms_stream_message

work in SQL*Plus from both the SYS + your schema?

Note that SYS.AQ$_JMS_STREAM_MESSAGE is a datbase object/type, whereas SYS.DBMS_AQ is a package

EDIT

Ok... maybe the TYPE body is missing / invalid. What does:

SELECT owner, object_name, object_type, status
FROM   dba_OBJECTS
WHERE  OBJECT_NAME = 'AQ$_JMS_STREAM_MESSAGE'

return?

Answer 2

Looks like a database version problem. AQ$_JMS_STREAM_MESSAGE has a construct method in 10G but not in 9i. What version of Oracle Server are you using?