ORA-02064: distributed operation not supported

From: <chenliang_at_my-deja.com>
Date: Fri, 01 Dec 2000 16:57:20 GMT
Message-ID: <908l9d$eqo$1@nnrp1.deja.com>

Hi, experts:
I need help!

I have a dynamic store procedure in a remote oracle server.

Now I call it from local server.
I encoutered this error.
Any expert can help me? Thanks a lot!!!!

ORA-02064 distributed operation not supported

Cause: One of the following unsupported operations was attempted:

Array execute of a remote update with a subquery that references a database link, or

An update of a long column with bind variable and an update of a second column with a subquery that both references a database link and a bind variable, or

A commit is issued in a coordinated session from an RPC with OUT parameters.

Action: Simplify the remote update statement.

source code of remote procedure:
create or replace package body READ as

---

procedure sp_ftrack_data2(v_in_keys IN my_type,v_out_keys out my_type) IS

v_row number(10) := 1;
cursor temp_cursor is
select id from temp1;

• dynamical PL/SQL ----------- v_ddl varchar2(100); v_ddl_cursor number; v_ddl_return integer;

  ---

begin

• DDL------------------------------------------ v_ddl_cursor := DBMS_SQL.OPEN_CURSOR;

v_ddl := 'truncate table temp1';
DBMS_SQL.PARSE(v_ddl_cursor,v_ddl,DBMS_SQL.V7); v_ddl_return := DBMS_SQL.EXECUTE(v_ddl_cursor);

v_ddl := 'drop index idx_temp1';
DBMS_SQL.PARSE(v_ddl_cursor,v_ddl,DBMS_SQL.V7); v_ddl_return := DBMS_SQL.EXECUTE(v_ddl_cursor);

v_ddl := 'truncate table temp2';
DBMS_SQL.PARSE(v_ddl_cursor,v_ddl,DBMS_SQL.V7); v_ddl_return := DBMS_SQL.EXECUTE(v_ddl_cursor);

---

---

• insert all data from in keys to temp table while v_row <= v_in_keys.COUNT loop insert into temp1 values(v_in_keys(v_row)); v_row := v_row+1; end loop;

  ---

  ---

• create index on temp1 v_ddl := 'create index idx_temp1 on temp1(id)'; DBMS_SQL.PARSE(v_ddl_cursor,v_ddl,DBMS_SQL.V7); v_ddl_return := DBMS_SQL.EXECUTE(v_ddl_cursor);

  ---

• small table keys should on right side , if rule_base
• cost_base is another concept

insert into temp2 (select a.id from test_sp a , temp1 b where a.id = b.id);
delete from temp1 where id in (select id from temp2);

---

open temp_cursor;
v_row := 1;
loop
exit when temp_cursor%NOTFOUND;
fetch temp_cursor into v_out_keys(v_row); v_row := v_row+1;
end loop;
close temp_cursor;

---

---

• close cursor of ddl DBMS_SQL.CLOSE_CURSOR(v_ddl_cursor);

  ---

end sp_ftrack_data2;

end READ;

Sent via Deja.com http://www.deja.com/
Before you buy. Received on Fri Dec 01 2000 - 10:57:20 CST