Skip to Main Content

Breadcrumb

May 4th

Question and Answer

Tom Kyte

Thanks for the question.

Asked: December 20, 2007 - 7:09 pm UTC

Last updated: February 25, 2008 - 4:19 pm UTC

Version: 8.1.7

Viewed 10K+ times! This question is

You Asked

Is there any way I can create a table over DB Link?

and Tom said...

you do not, you log into the remote system to perform DDL.

you can use the job queues - so that a transaction is performed on the remote system, eg:

dbms_job@remote.submit( l_job, 'execute immediate ''create table t ( x int )''' );
commit;

and if job queues are set up
and if you have the ability to create the table without any roles enabled
and if you are willing to have the create table happen SOMETIME AFTER you commit

that can be used.


Rating

  (8 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

why not

Sokrates, December 21, 2007 - 2:34 pm UTC

using
dbms_sql@remote ?
Tom Kyte
December 21, 2007 - 3:21 pm UTC

commit in distributed transactions from the remote site is not permitted.

DDL commits.

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

Tom,

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;
begin
dbms_sql.parse@dblink(exec_cursor, p_sql, dbms_sql.native);
rows_processed := dbms_sql.execute@dblink(exec_cursor);
dbms_sql.close_cursor@dblink(exec_cursor);
exception
when others then
debug('exe_remote_sql: ' || dbms_utility.format_error_stack);
debug(p_sql);
if dbms_sql.is_open@dblink(exec_cursor) then
dbms_sql.close_cursor@dblink(exec_cursor);
end if;
end;
Tom Kyte
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.

Good points

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.
Tom Kyte
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!
Tom Kyte
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, .... );
commit;

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

Hello Tom,
I'm wondering why this code works
and if it is dangerous to use it.
Thanks
Massimo
/**************************************/
DECLARE
n_cursor INTEGER;
num_rows_processed integer;
p_stmt VARCHAR2(200) := 'create table temp (key1 number, key2 varchar2(1))';
BEGIN
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) ;
END;
/**************************************/

Sorry

massimo, October 16, 2009 - 8:57 am UTC

I'm sorry.
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?

Bye
Massimo

More to Explore

DBMS_JOB

More on PL/SQL routine DBMS_JOB here