While my task is relatively simple: take data from one table in schema A, and insert in into another table in schema B, I'm stuck on how to do this in cx_Oracle.
In PL/SQL I would do something along the lines of:
INSERT INTO Schema_B.Table ... FROM Schema_A.Table.
However, in cx_Oracle I have a separate cursor for each connection, and have no clue how to connect to both schemas in one SQL statement. Can attach my code if needed.
Essentialy, I have something of the sort:
connect_db_1 = cx_Oracle_database.connect(db 1 credentials)
connect_db_2 = cx_Oracle_database.connect(db 2 credentials)
schema 1 =...
schema 2=...
cursor_db_1 = connect_db_1.cursor()
cursor_db_2 = connect_db_2.cursor()
#sample statement that works on one database
cursor_db_1.execute(F'select * from {schema_1}.table.a)
However, if I need to select something from one database, then put it into the other with one statement, I have no clue how to do that, since it required both cursors to be used
cursor_db_1.execute(F'insert into {schema_2}.table_1
SELECT * from {schema_1}.table.a )
This doesn't work, since once of the schemas is not in this cursor
Are you trying to insert into another DB, or another user in the same DB?
If it's another DB, then something will have to connect to both DBs: either in cx_Oracle with two connections, or from one database to the other using a DB link. If you use cx_Oracle with two connections, then tune arraysize when querying, and use executemany() when inserting.
If your users are in the same DB, then you can use GRANT to give appropriate permissions to the writing user so it can read the other user's table.