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!!

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.