Thank you for your reply!
My oracle is 8.1.6 enterprise, and installed it using default setup.
Anyway, I solve it by avoiding using DDL inside local procedure.
I split it from stored procedure. And call DDL from remote procedure.
It works now.
Thank you for your help, Alex!
In article <9099eb$sq$1_at_nnrp1.deja.com>,
Alex Filonov <afilonov_at_pro-ns.net> wrote:
> You need to install distributed option. If you want to change data in
> one database from another, the local database has to have a distibuted
> option installed. For Oracle 8, 8.1 it means you need an enterprise
> edition.
>
> In article <908l9d$eqo$1_at_nnrp1.deja.com>,
> chenliang_at_my-deja.com wrote:
> > 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.
> >
>
> Sent via Deja.com http://www.deja.com/
> Before you buy.
>
Sent via Deja.com http://www.deja.com/
Before you buy.
Received on Sun Dec 03 2000 - 16:29:47 CST
