Home » Server Options » Streams & AQ » SET_DML_HANDLER for apply process failing
SET_DML_HANDLER for apply process failing [message #75742] Sun, 12 September 2004 03:27 Go to next message
bw
Messages: 6
Registered: June 2002
Junior Member
I'm getting the following error when trying to set the user handler procedure for an apply process:

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'RT01.ORDERPDO_ORDER',
object_type => 'TABLE',
operation_name => 'UPDATE',
user_procedure => 'RT01.fbtest_enq_row_lcr');
END;
/
BEGIN
*
ERROR at line 1:
ORA-04047: object specified is incompatible with the flag specified
ORA-06512: at "SYS.DBMS_UTILITY", line 114
ORA-06512: at "SYS.DBMS_APPLY_ADM", line 283
ORA-06512: at line 2

This error started occuring recently on code that used to work OK. There must be something that I've changed in the database configuration but I have no idea what it is.

I have no idea what the error means, either the object or the flag meant in the message is a mystery to me. Can anyone shed light on this or tell me how I might find out more info about the error (debug flags or tracing that might be used?)

Running Oracle 9.2.0.5.0 on Linux (2.6.7 kernel, glibc 2.3.3, Gentoo distribution)

The sql leading up to this is below,

thanks,
brian wallis...

BEGIN
DBMS_STREAMS_ADM.SET_UP_QUEUE(
queue_table=>'RT01.fbtest_table',
queue_name=>'RT01.fbtest_queue',
queue_user=>'RT01');
END;
BEGIN
DBMS_RULE_ADM.CREATE_RULE_SET(
rule_set_name=>'fbtest_rule_set',
evaluation_context=>'SYS.STREAMS$_EVALUATION_CONTEXT');
END;
BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name=>'fbtest_rule',
condition=>':dml.get_command_type() = ''UPDATE''
AND :dml.get_object_owner() = ''RT01''
AND :dml.get_object_name() = ''ORDERPDO_ORDER''
');
END;
BEGIN
DBMS_RULE_ADM.ADD_RULE(
rule_name=>'fbtest_rule',
rule_set_name=>'fbtest_rule_set');
END;
BEGIN
DBMS_RULE_ADM.CREATE_RULE(
rule_name=>'fbtest_rule_x',
condition=>'(:dml.get_command_type() = ''INSERT''
OR :dml.get_command_type() = ''DELETE'')
AND :dml.get_object_owner() = ''RT01''
AND :dml.get_object_name() = ''ORDERPDO_ORDER'' ');
END;
BEGIN
DBMS_RULE_ADM.ADD_RULE(
rule_name=>'fbtest_rule_x',
rule_set_name=>'fbtest_rule_set');
END;
BEGIN
DBMS_CAPTURE_ADM.CREATE_CAPTURE(
queue_name => 'RT01.fbtest_queue',
capture_name => 'fbtest_capture',
rule_set_name => 'fbtest_rule_set');
END;
CREATE OR REPLACE PROCEDURE RT01.fbtest_enq_row_lcr(in_any IN SYS.ANYDATA) IS
enqopt DBMS_AQ.ENQUEUE_OPTIONS_T;
mprop DBMS_AQ.MESSAGE_PROPERTIES_T;
enq_eventid RAW(16);
lcr SYS.LCR$_ROW_RECORD;
rc PLS_INTEGER;
BEGIN
rc := in_any.GETOBJECT(lcr);
mprop.SENDER_ID := SYS.AQ$_AGENT(
name => 'fbtest_dq',
address => NULL,
protocol => NULL);
DBMS_AQ.ENQUEUE(
queue_name => 'RT01.NS_LCR_QUEUE',
enqueue_options => enqopt,
message_properties => mprop,
payload => sys.anydata.convertobject(lcr),
msgid => enq_eventid);
END;
BEGIN
DBMS_APPLY_ADM.CREATE_APPLY(queue_name=>'RT01.fbtest_queue',
apply_user => 'RT01',
apply_name=>'fbtest_apply',
apply_captured=>true);
END;
Re: SET_DML_HANDLER for apply process failing [message #75743 is a reply to message #75742] Sun, 12 September 2004 06:01 Go to previous messageGo to next message
bw
Messages: 6
Registered: June 2002
Junior Member
Having a play with this problem. If I modify the
failing set_dml_handler call and remove the schema
name from the user procedure name (ie
'rt01.fbtest_enq_row_lcr' becomes 'fbtest_enq_row_lcr'
then the dml handler sets without error. New call is:

BEGIN
DBMS_APPLY_ADM.SET_DML_HANDLER(
object_name => 'RT01.ORDERPDO_ORDER',
object_type => 'TABLE',
operation_name => 'UPDATE',
user_procedure => 'fbtest_enq_row_lcr');
END;

But now my apply process gets an error and aborts:

ORA-06550: line 1, column 12:
PLS-00302: component 'FBTEST_ENQ_ROW_LCR' must be
declared
ORA-06550: line 1, column 7:
PL/SQL: Statement ignored

Any help appreciated.

brian wallis...
Re: SET_DML_HANDLER for apply process failing [message #75744 is a reply to message #75743] Tue, 14 September 2004 03:32 Go to previous messageGo to next message
bw
Messages: 6
Registered: June 2002
Junior Member
OK, to talk to myself (which is what usually happens
when I use this forum, does anyone else read it?)

Looks like this might be an oracle
bug/issue/strangeness.

I had a queue in the schema I was running it (schema
name RT01) with the same name as the schema (ie: the
queue was called RT01 as well). I have now deleted
that queue and it is working again. I am not sure
exactly when this queue was created but it would be
around the time that I started having these problems
(there has been a lot of experimentation on this
system).
Re: SET_DML_HANDLER for apply process failing [message #221182 is a reply to message #75744] Sat, 24 February 2007 17:11 Go to previous message
memento
Messages: 1
Registered: February 2007
Junior Member
I also got this error. The cause was that I mistakenly called a function using the name of a table instead of the actual name of the function. Doh!

Database was Oracle 10g Express edition
Previous Topic: Cannot Create Queue Table with Custom Message Type
Next Topic: help me with AQ
Goto Forum:
  


Current Time: Wed Dec 07 03:13:22 CST 2016

Total time taken to generate the page: 0.05470 seconds