import cx_Oracle

def fetch_column_names(cursor, table_name, owner_name):

    query = """

    SELECT column_name

    FROM sys.all_tab_cols

    WHERE table_name = :table_name AND owner = :owner_name

    ORDER BY column_id

    """

    cursor.execute(query, table_name=table_name.upper(), owner_name=owner_name.upper())

    columns = [row[0] for row in cursor.fetchall()]

    return columns

def transfer_data(src_conn, dest_conn, table_name, owner_name):

    # Create cursors

    src_cursor = src_conn.cursor()

    dest_cursor = dest_conn.cursor()

    try:

        # Fetch column names

        columns = fetch_column_names(src_cursor, table_name, owner_name)

        cols_list = ', '.join(columns)

        # Generate SELECT query for source

        select_query = f"SELECT {cols_list} FROM {owner_name}.{table_name}"

        # Fetch data from source

        src_cursor.execute(select_query)

        data = src_cursor.fetchall()

        # Generate INSERT query for destination

        bind_vars = ', '.join([':' + str(i + 1) for i in range(len(columns))])

        insert_query = f"INSERT INTO {owner_name}.{table_name} ({cols_list}) VALUES ({bind_vars})"

        # Insert data to destination

        dest_cursor.executemany(insert_query, data)

        dest_conn.commit()

    except cx_Oracle.DatabaseError as e:

        print("Error occurred:", e)

    finally:

        src_cursor.close()

        dest_cursor.close()

# Main Execution

if __name__ == "__main__":

    src_connection = cx_Oracle.connect('user/password@production_db')

    dest_connection = cx_Oracle.connect('user/password@dev_db')

    table_name = 'YOUR_TABLE_NAME'

    owner_name = 'YOUR_OWNER_NAME'

    transfer_data(src_connection, dest_connection, table_name, owner_name)

    src_connection.close()

    dest_connection.close()

ORACLE PROD TO DEV

EXPORT

expdp username/password@prod_db schemas=your_schema directory=your_directory dumpfile=exportprod_conn_str = 'username/password@prod_host:port/prod_service_name'_renevue.dmp logfile=export.log query=tbl_name:\"WHERE column = 123\"

expdp username/password@prod_db schemas=your_schema directory=your_directory dumpfile=export_renevue.dmp logfile=export.log query=\"bb_renevue:\"\"WHERE gfcid = 123\"\"\"

IMPORT

impdp username/password@dev_db schemas=your_schema directory=your_directory dumpfile=export_renevue.dmp logfile=import.log remap_table=bb_renevue:bb_revenue_lite

impdp username/password@dev_db schemas=our_schema directory=your_directory dumpfile=export_renevue.dmp logfile=import.log remap_table=bb_renevue:bb_revenue_lite 

OR

import cx_Oracle

# Database connection parameters

prod_conn_str = 'username/password@prod_host:port/prod_service_name'

dev_conn_str = 'username/password@dev_host:port/dev_service_name'

# SQL queries

select_query = "SELECT * FROM bb_renevue WHERE gfcid = 123"

insert_query = "INSERT INTO bb_revenue_lite (columns) VALUES (:1, :2, ...)"

# Connect to the production database

prod_conn = cx_Oracle.connect(prod_conn_str)

prod_cursor = prod_conn.cursor()

# Connect to the development database

dev_conn = cx_Oracle.connect(dev_conn_str)

dev_cursor = dev_conn.cursor()

try:

    # Fetch data from production

    prod_cursor.execute(select_query)

    rows = prod_cursor.fetchall()

    # Insert data into development

    dev_cursor.executemany(insert_query, rows)

    dev_conn.commit()

except cx_Oracle.DatabaseError as e:

    print(f"Database error occurred: {e}")

finally:

    # Close the cursors and connections

    prod_cursor.close()

    dev_cursor.close()

    prod_conn.close()

    dev_conn.close()