| ORA-02064: distributed operation not supported [message #370036] |
Fri, 01 December 2000 10:55 |
New
Messages: 10
Registered: December 2000
|
Junior Member |
|
|
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;
|
|
|
|
] 
Blog
Search
Help
Members
Register
Login
Home
