calling a function over db link that creates a table in target db
ciapecki
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';
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 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@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@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
sybr...@hccnet.nl
<ciap...@gmail.com> wrote:
>Is it possible to achieve the goal without that wrapper procedure
>(prepare_table_proc)?
>
>thanks,
>chris
Apart from the fact creating tables on the fly in pl/sql is just plain
evil, you could have gathered from the error message (which you
obviously didn't check in the online documentation), Oracle *doesn't
support distributed DDL*!!!!
So the answer is there are probably several ways to get you out of the
on-the-fly table creation mess, or get you furthe in it, but dumping
what you call the 'wrapper procedure' (IMO it is just a nightmare), is
not one of them.
--
George Rypysc
> Is it possible to achieve the goal without that wrapper procedure
> (prepare_table_proc)?
>
Chris,
Yes it is possible.
What you tried first will work if you rewrite it using the older DBMS_SQL
package instead of "EXECUTE IMMEDIATE".
See example 2 from the 10gR2 package documentation:
http://download.oracle.com/docs/cd/B19306_01/appdev.102/b14258/d_sql.htm#sthref6148
Lucky for me, remote DDL has been available since at least version 8.1.6.
I needed this ability to truncate a table on a remote 10gR1 database from
an 8.1.6 database without creating any code on the remote database.
-George
Randolf Geist
> 10gR2
>
> on target db I have following function:
> 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:
> SQL> call prepare_table_proc@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)?
Chris,
I haven't tested it myself, but if you check 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.
You'll notice that point 3 above might be the culprit: Your remote
procedure does perform a COMMIT but returns a value since it is a
function call. So the most obvious way to avoid the error might simply
be to turn your function into a procedure, which is what you've done
indirectly by using your remote wrapper procedure.
Regards,
Randolf
Oracle related stuff blog:
http://oracle-randolf.blogspot.com/
SQLTools++ for Oracle (Open source Oracle GUI for Windows):
http://www.sqltools-plusplus.org:7676/
http://sourceforge.net/projects/sqlt-pp/
George Rypysc
> Yes it is possible.
> What you tried first will work if you rewrite it using the older
> DBMS_SQL package instead of "EXECUTE IMMEDIATE".
I was wrong above (*if* you return a value).
I tested it using DBMS_SQL and it gives the same error (ORA-02064)
as your code.
I thought this was the same problem I had when trying to
truncate a table over a db_link using EXECUTE IMMEDIATE but that
was a different error: "ORA-02021: DDL operations are not allowed
on a remote database"
I think Randolf is right.
-George