CODEWITHSUNDEEP
oracle-databasedblink
vicsz
vicsz

Reputation: 9764

INSERT ALL INTO a table over a dblink .. Is it possible?

When I execute the following:

INSERT ALL INTO table@database_link(columnName) VALUES (columnValue)
SELECT columnValue FROM localTable;

I get an error

SQL Error: ORA-02021: DDL operations are not allowed on a remote database 02021. 00000 - "DDL operations are not allowed on a remote database" *Cause: An attempt was made to use a DDL operation on a remote database. For example, "CREATE TABLE tablename@remotedbname ...". *Action: To alter the remote database structure, you must connect to the remote database with the appropriate privileges.

Note that when I do a regular (not an insert ALL into) ... the insert works over the database link. (Grants are valid).

There are also NO triggers on either tables.

And I explicitly need the INSERT ALL INTO, to allow for the ability to insert into multiple tables.

Are INSERT ALL INTO operations not allowed into database link tables?

Upvotes: 4

Views: 7377

Answers (2)

Marcin Wroblewski
Marcin Wroblewski

Reputation: 3571

This message is a little misleading but anyway according to the Oracle SQL Reference

You cannot perform a multitable insert into a remote table.

Upvotes: 6

Igby Largeman
Igby Largeman

Reputation: 16747

Are INSERT ALL INTO operations not allowed into database link tables?

Unfortunately not. Note the second item in this list:

Restrictions on Multitable Inserts

You can perform multitable inserts only on tables, not on views or materialized views.

You cannot perform a multitable insert into a remote table.

You cannot specify a table collection expression when performing a multitable insert.

In a multitable insert, all of the insert_into_clauses cannot combine to specify more than 999 target columns.

Multitable inserts are not parallelized in a Real Application Clusters environment, or if any target table is index organized, or if any target table has a bitmap index defined on it.

Plan stability is not supported for multitable insert statements.

The subquery of the multitable insert statement cannot use a sequence.

Source: Oracle 9i documentation

Upvotes: 1

Related Questions