How to execute a SQL script with cx_oracle

Question

I am trying to execute a SQL script (read from a file) on an Oracle database. I have tried many methods but none are working.

I have the following method:

def connect_cx_oracle():

    dns_tns = cx_Oracle.makedsn(config.DB_HOST, config.DB_PORT, service_name=config.DB_DBASE)
    con = cx_Oracle.connect(user=config.DB_USERNAME, password=config.DB_PASSWORD, dsn=dns_tns)
    cur = con.cursor()

    fd = open('PurgeProcess_2.sql')
    full_sql = fd.read()
    cur.prepare(full_sql)
    cur.executemany(None, full_sql)
    con.commit()

Using cur.execute on the file results in an error. I can't split the file with semi colons since it's a script.

The code for the sql file is something like this (not posting entire file)

SET SERVEROUTPUT ON SIZE UNLIMITED;
DECLARE

    CURSOR c_task IS SELECT job_id, task_info_id FROM task WHERE job_id <= myjob_id;

BEGIN

  dbms_output.ENABLE(NULL);
  dbms_output.put_line('*******************************');
  dbms_output.put_line('* Purge job started.......     ');
  dbms_output.put_line('*******************************');

        IF (FROM_date < 30) THEN
            dbms_output.put_line(' ');

        end IF;
    EXCEPTION
        WHEN NO_DATA_FOUND THEN
            dbms_output.put_line(' ');
            dbms_output.put_line('*******************************');
            dbms_output.put_line('* Purge job completed.......   ');
            dbms_output.put_line('*******************************');
            RAISE NO_ITEMS_TO_PROCESS;
    END;

    FOR i IN 1..myjobIDnumber.COUNT
    LOOP
        BEGIN
            DELETE FROM job WHERE id = myjobIDnumber(i);
            IF SQL%ROWCOUNT > 0 THEN
                deletecount4 := deletecount4 + SQL%ROWCOUNT;
            END IF;
        END;
    END LOOP;

    dbms_output.put_line('Job table records deleted. Number of records deleted is ' || deletecount4);
    dbms_output.put_line(' ');

    ROLLBACK;
    --COMMIT;

    dbms_output.put_line('*******************************');
    dbms_output.put_line('* Purge job completed.......  *');
    dbms_output.put_line('*******************************');
EXCEPTION
    WHEN NO_ITEMS_TO_PROCESS THEN
    NULL;
    WHEN DATE_LIMIT_APPROACHED THEN
    NULL;
END;

If I use cur.execute(fileName) I get

cx_Oracle.DatabaseError: ORA-00922: missing or invalid option

With executemany I get

TypeError: parameters should be a list of sequences/dictionaries or an integer specifying the number of times to execute the statement

I am not really a sql guy, any help would be appreciate. Regards

Answer 1

After playing around with a lot of libraries I ended up using Python's sub-process to solve this issue. Posting code, as it might help someone in the future.

def connect_to_db_and_execute(timeout):
    proc = subprocess.Popen(f'sqlplus {config.DB_USERNAME}/{config.DB_PASSWORD}@{config.DB_HOST}:{config.DB_PORT}/'
                            f'{config.DB_DBASE} @C:/pathtoSql/PurgeProcess_2.sql', stdout=PIPE, stderr=PIPE, universal_newlines=True)
    timer = Timer(timeout, proc.kill)
    try:
        timer.start()
        stdout,stderr = proc.communicate()
    finally:
        timer.cancel()
    (output, err) = proc.communicate()
    print('output is ', output)
    if err:
        # iF there is an error, print and sent it via email
        print(err)
        send_email(email, pword, to_recipients, subject, err)```