Sokrates, December 21, 2007 - 2:34 pm UTC
December 21, 2007 - 3:21 pm UTC
commit in distributed transactions from the remote site is not permitted.
Sokrates, December 21, 2007 - 3:40 pm UTC
A reader, December 21, 2007 - 5:34 pm UTC
Thanks a lot for the response.
DBMS_SQL works for me across a link for DDL
Ethan, February 22, 2008 - 6:06 pm UTC
The procedure below is working for me when I pass it a create table statement and many other DDL commands. This is going from a 9204 database to 10g r2.
I do get some distributed transaction errors when I try to run PLSQL which contains commits in it but the implicit commits in the DDL seem to work fine.
One other thing however, if I try to use an overloaded version of .parse and pass it an array it does not see that particular version of .parse. If I run the same code locally in the remote database it runs fine, it only fails going across the link. My guess is one of the data types is not being interpreted correctly. I am using dbms_sql.varchar2a (this particular procedure was written to parse very large PL/SQL packages).
procedure exe_remote_sql (p_sql in varchar2 ) as
exec_cursor integer default dbms_sql.open_cursor@dblink;
rows_processed number default 0;
dbms_sql.parse@dblink(exec_cursor, p_sql, dbms_sql.native);
rows_processed := dbms_sql.execute@dblink(exec_cursor);
when others then
debug('exe_remote_sql: ' || dbms_utility.format_error_stack);
if dbms_sql.is_open@dblink(exec_cursor) then
February 24, 2008 - 11:23 am UTC
first and foremost, your when others stinks, hate it, despise it, totally done wrong.
It is absolutely (not a opinion, hard fact) missing the word RAISE after the end if. It is wrong.
And if some ddl is working by accident, consider yourself warned: it is not supposed to - you might be encountering a bug that is permitting it, but you best stop.
You would have to declare your local variable of that remote type.
create synonym remote_dbms_sql for dbms_sql@remote;
then use remote_dbms_sql instead of the local dbms_sql everywhere, including the declaration.
Ethan, February 25, 2008 - 1:18 pm UTC
Thanks Tom. All good points. If you did want to run DDL in a remote database could you use Java from within Oracle to accomplish this? I am sure I could write something that does this from a program on the OS and have the local DB pass commands to it and await a response, but I would like to keep it all in Oracle if possible.
February 25, 2008 - 2:09 pm UTC
I would suggest using the job queues, the java stored procedure would have to open a new connection to Oracle (eg: load the thin drivers in and connect again - you'd need the username and password of course)
Just submit a job in the remote machine that does the DDL in a job.
Just like I described first time around?
Ethan, February 25, 2008 - 3:57 pm UTC
Yes I saw the way you originally suggested. I played around with it quickly and got errors regarding maximum length of a line that can be passed to the dbms_job.submit (I didn't this very thoroughly so I haven't totally written this off). One of my requirements is to compile large packages in the remote database or execute scripts. The path I am headed now is to install a minimal set of objects, a source table (much like DBA_SOURCE, and a procedure which can read it and execute the sql contained in it). This way I only need to deploy a few small objects into the remote database and then I will be able to use DBMS_JOB to tell the remote database to begin building/running the objects contained in the table. I will also look into the Java route for deploying the initial objects. Thanks for the help. See you at Hotsos!
February 25, 2008 - 4:19 pm UTC
the right way to call a job is to use a single parameter:
dbms_job.submit( l_job, 'your_proc( JOB );' );
insert into job_table (job, parameters ) values ( l_job, .... );
your job gets passed the unique job id, use that as a primary key to a job table and select out your parameters
very very very bind friendly...
Why I reached to do DDL on remore DB
massimo, October 16, 2009 - 8:35 am UTC
I'm wondering why this code works
and if it is dangerous to use it.
p_stmt VARCHAR2(200) := 'create table temp (key1 number, key2 varchar2(1))';
n_cursor := dbms_sql.open_cursor@mylink;
dbms_sql.parse@ mylink(n_cursor, p_stmt, dbms_sql.native) ;
num_rows_processed := dbms_sql.execute@mylink(n_cursor) ;
dbms_sql.close_cursor@ mylink(n_cursor) ;
massimo, October 16, 2009 - 8:57 am UTC
You replied before and it was very clear.
In Oracle 10g documentation is suggested to replace DBMS_JOB with DBMS_SCHEDULER: the answer is still the same?