Skip to Main Content
  • Questions
  • ORA-02070: database does not support in this context

Breadcrumb

Question and Answer

Connor McDonald

Thanks for the question.

Asked: December 08, 2016 - 5:41 am UTC

Last updated: April 19, 2021 - 5:36 am UTC

Version: 12.1.2.0

Viewed 10K+ times! This question is

You Asked

Hi,

I am facing the below exception -
ORA-02070: database does not support in this context

Here is the query I am using -

INSERT INTO table_1
(a,b,c)
SELECT
table_name
, partition_name
, subpartition_name
FROM user_subpartitions;


Table_1 is in Database_A
USER_SUBPARTITIONS is in Database_B

The connection is across the database link.

Here is the code -

Database_A

CREATE TABLE table_1 (a NUMBER, b VARCHAR2(100), c VARCHAR2(100));

CREATE SEQUENCE sq_test_1 START WITH 1 INCREMENT BY 1;


Database_B

CREATE SYNONYM table_1 for table_1@dblink;

CREATE SYNONYM sq_test_1 for sq_test_1@dblink;

INSERT INTO table_1
(a,b,c)
SELECT
sq_test_1.NEXTVAL
, table_name
, subpartition_name
FROM user_subpartitions;

Best Regards!!

and we said...

Yes, thats a limitation of synonyms to remote objects.


You can work around it with a little PLSQL

SQL> declare
  2    cursor c is
  3      select sq_test_1.NEXTVAL seq, table_name, subpartition_name
  4      FROM user_tab_subpartitions;
  5    type clist is table of c%rowtype;
  6    l_rows clist;
  7
  8  begin
  9    open c; fetch c bulk collect into l_rows; close c;
 10
 11    for i in 1 .. l_rows.count
 12    loop
 13     INSERT INTO table_1(a,b,c)
 14     values ( l_rows(i).seq, l_rows(i).table_name, l_rows(i).subpartition_name );
 15    end loop;
 16  end;
 17  /

PL/SQL procedure successfully completed.



Rating

  (1 rating)

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

Comments

Oracle to sql server date value insert error

Abhijit, April 18, 2021 - 8:05 am UTC

It will not work if i try to insert date value from oracle to SQL server
Connor McDonald
April 19, 2021 - 5:36 am UTC

And your full test case is ..... ?