| SET_DML_HANDLER for apply process failing [message #75742] |
Sun, 12 September 2004 03:27  |
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  |
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 |
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).
|
|
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
