Database Link - Error ORA-02021

Hi All,

I am getting "ORA-02021: DDL operations are not allowed on a remote database" when I try to execute a procedure in remore database which creates table, indexess aft the process is successfullty completed it drops those tables created.

this is workin in our production env.

NO i need to know wht all the privs required.

Thanks,
Jegan

hi

how about having a procedure like this...

create or replace procedure
trunc_tableinremotedb(p_tablename varchar2) as

begin
execute immediate 'truncate table' || p_tablename

exception when others then.

....
...

end trunc_tableinremotedb;

begin
trunc_tableinremotedb@db_link('remote_tablename');
end;

hope i m making some sense here...

let me know if this is what u r looking for.

Rgds

Srini

Thanks.
sur will try and revert.

Is there some other method?

AFAIK theres no other way to achieve it.....

S

Thanks guys

Thru this procedure I am able to truncate the table in remote DB using DB LINK

16:15:10 SQL> create or replace procedure db_link_trunc_table_procedure as
16:15:29 2 /* Local Variables */
16:15:29 3 c_handle INTEGER;
16:15:29 4 feedback INTEGER;
16:15:29 5 BEGIN
16:15:29 6 c_handle := DBMS_SQL.open_cursor@edwd01.h3guk.com;
16:15:29 7
16:15:29 8 DBMS_SQL.parse@edwd01.h3guk.com( c_handle
16:15:30 9 , 'TRUNCATE table test_dblink_trunc@edwd01.h3guk.com'
16:15:30 10 , DBMS_SQL.V7);
16:15:30 11
16:15:30 12 feedback := DBMS_SQL.execute@edwd01.h3guk.com(c_handle);
16:15:30 13
16:15:30 14 DBMS_SQL.close_cursor@edwd01.h3guk.com(c_handle);
16:15:30 15
16:15:30 16 END;