| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Package DBMS_% Problem
A copy of this was sent to Thierry Valle <tvalle_at_fr.oracle.com>
(if that email address didn't require changing)
On Wed, 24 Feb 1999 14:21:14 +0100, you wrote:
>Hi ,
>
>I've a little problem for a developper but big for a dba .
>I try to distribute modifications on tables to others databases .
>My actual solution is :
>
>Detect modifications => Trigger on table (Ok)
>Action => PLSQL code with two parts :
> Connect remote database => DBMS_% (??)
> Alter table commande => DBMS_SQL (??)
>
can't do DDL over a database link. alter table = ddl.
you can do dbms_job however over a database link and dbms_job can do ddl....
for example use something like this:
exec dbms_job.submit_at_b.world( 'begin .... end;' );
(read about dbms_job). It'll put the job in the queue on the other end of the link and execute it later. I've done this to do DDL through links. I write a pl/sql routine on the remote machine that can execute any sql statement and then execute something like:
exec dbms_job.submit_at_b.world(
'begin execute_immediate(''create table foo (x int)''); end' );
you need to make sure that the procedure:
create or replace procedure execute_immediate( sql_stmt in varchar2 )
as
exec_cursor integer default dbms_sql.open_cursor;
rows_processed number default 0;
begin
dbms_sql.parse(exec_cursor, sql_stmt, dbms_sql.native );
rows_processed := dbms_sql.execute(exec_cursor);
dbms_output.put_line( 'rows processed = ' || rows_processed );
dbms_sql.close_cursor( exec_cursor );
end;
is installed on the remote node and that the owner of the procedure has the appropriate priveleges (eg: grant create table for this example) granted directly TO THEM, not to a role they have....
>Databases Oracle V7.3.3 on Unix Sun systems .
>For ideas or example scripts , please email me at tvalle_at_fr.oracle.com
>
>Thanks
Thomas Kyte
tkyte_at_us.oracle.com
Oracle Service Industries
Reston, VA USA
--
http://govt.us.oracle.com/ -- downloadable utilities
 |
 |