calling a function over db link that creates a table in target db

on target db I have following function:
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';

EXCEPTION
    WHEN others THEN

        err_msg := SUBSTR(SQLERRM, 1, 100);
        return 'error when creating ' || tbl || '-' || err_msg || '-'

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 target DB: 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.

when I try to call this function from source DB: SQL> set serveroutput on;
SQL> declare
  2 result varchar2(300) := '';
  3 begin
  4 result := prepare_table_at_dblink_to_targetuser ('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.

The exception part was called, which means CREATE TABLE failed.

I found a workaround with a wrapper procedure on target DB:

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 source like this:
SQL> call prepare_table_proc_at_dblink_to_targetuser ('other_user.table_a');

Call completed.

And the table_a is created on target DB.

This is not very DRY, since the wrapper, does nothing more but calls the function with exactly the same parameter.

Is it possible to achieve the goal without that wrapper procedure (prepare_table_proc)?

thanks,
chris Received on Thu Mar 26 2009 - 07:29:53 CDT