recently I encountered a requirement to create a table on remote database DB_TARGET (working in DB_SOURCE)
for example:
create table table_a@dblink_to_db_target; --> does not work
-- ORA-02021: DDL operations are not allowed on a remote database
whereas
select 'hello' from dual@dblink_to_db_target; --> works
This is a workaround I used:
on TARGET_DB:
CREATE OR REPLACE
FUNCTION prepare_table
( table_name in varchar2) return varchar2
IS
err_num NUMBER;
err_msg VARCHAR2(100);
sqlstmt varchar2(4000) := '';
tbl varchar2(30) := '';
BEGIN
if instr(table_name,'.') > 0 then
tbl := substr(table_name,instr(table_name,'.')+1);
sqlstmt := 'CREATE TABLE ' || tbl || ' as select * from ' ||
table_name;
execute immediate sqlstmt;
return 'table ' || tbl || ' successfully created';
else
return 'table in owners'' schema';
end if;
EXCEPTION
WHEN others THEN
err_msg := SUBSTR(SQLERRM, 1, 100);
return 'error when creating ' || tbl || '-' || err_msg || '-'
|| sqlstmt;
END;
/
this function creates a table specified as a table_name parameter only
if the "." is in the name => goal is to create a table in my_schema
from other user's schema.
this function works fine when calling this on DB_TARGET:
SQL> set serveroutput on;
SQL> declare
2 result varchar2(300) := '';
3 begin
4 result := prepare_table('other_user.table_a');
5 dbms_output.put_line(result);
6 end;
7 /
table table_a successfully created
PL/SQL procedure successfully completed.
but then surprisingly to me:
when I try to call this function from DB_SOURCE:
SQL> set serveroutput on;
SQL> declare
2 result varchar2(300) := '';
3 begin
4 result := prepare_table@dblink_to_db_target('other_user.table_a');
5 dbms_output.put_line(result);
6 end;
7 /
error when creating gcd_countries-ORA-02064: distributed operation not
supported-CREATE TABLE table_a as select * from other_user.table_a
PL/SQL procedure successfully completed.
I found a workaround with a wrapper procedure on DB_TARGET:
CREATE OR REPLACE
PROCEDURE prepare_table_proc(table_name in varchar2)
IS
result varchar2(300) := '';
BEGIN
result := prepare_table(table_name);
dbms_output.put_line('success');
EXCEPTION
WHEN others THEN
dbms_output.put_line('err');
END;
/
when I call it from DB_SOURCE like this:
SQL> call prepare_table_proc@dblink_to_db_target('other_user.table_a');
Call completed. And the table_a is created on DB_TARGET.
when we look closer why the function itself did not work we see in the description of the error message:
ORA-02064: distributed operation not supported
Cause: One of the following unsupported operations was attempted:
1. array execute of a remote update with a subquery that
references a dblink, or
2. an update of a long column with bind variable and an update of
a second column with a subquery that both references a dblink and a
bind variable, or
3. a commit is issued in a coordinated session from an RPC
procedure call with OUT parameters or function call.
in my case point 3. yields and hence the error message.
Oracle documentation comes with help in:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6148it works only as a procedure and we cannot return a value to the invoking DB_SOURCE anyway.
maybe there's a better way...
right now I am happy with the workaround
Posts
