CODEWITHSUNDEEP
pythonoracle-databaseinsertsequencecx-oracle
RichardB
RichardB

Reputation: 13

Insert next value of sequence using cx_Oracle Cursor.executemany()

I am trying to use the cx_oracle library in python to insert rows, where one of the column values is the next value in a sequence called an_aj_s .

I have created a function to do this called insert_into_columns and the call to this function looks like this: 

self.insert_into_columns('AN_JOB',
                         ('AJ_ID','AJ_JOBNAME', 'AJ_DESCRIPTION', 'AJ_CD_JOB_TYPE', 'AJ_CD_DATABASE','AJ_CD_JOB_STATUS', 'AJ_USR_ID', 'AJ_CREATE_DATE', 'AJ_EQUIP_COMPS_FLAG'),
                         [ ('an_aj_s.nextval', 'QlikManual_3', 'First Manually entered Test Job', 2, 0, 1  ,6 , dt.today().date(),  1)])

where the arguments are: insert_into_columns({Table Name}, {Columns to insert}, {row of values to insert})

When I call the function I get an error invalid number: cx_Oracle.DatabaseError: ORA-01722: invalid number

I am sure that the problem is the sequence because if I replace the sequence with a number the insert is successful. Can anyone tell me how I can call my sequence when inserting rows into the table?

I have tried setting the input sizes with cur.setinputsizes() but it did not seem to work.

ADDITIONAL INFORMATION:

The SQL statement that my function generates looks like this:

INSERT INTO AN_JOB (AJ_ID,AJ_JOBNAME,AJ_DESCRIPTION,AJ_CD_JOB_TYPE,AJ_CD_DATABASE,AJ_CD_JOB_STATUS,AJ_USR_ID,AJ_CREATE_DATE,AJ_EQUIP_COMPS_FLAG) VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9 )

And actual python function to generate the statement looks like this like this:

 def insert_into_columns(self, table_name, columns, rows):
    sql = "INSERT INTO {table_name} ({column_names}) VALUES ({column_args} )"
    args_str = ''
    col_str  = ''
    for col_id in range(1, len(columns) + 1):
        args_str += ' :' + str(col_id)
        col_str += columns[col_id - 1]
        if col_id < len(columns):
            args_str += ','
            col_str += ','

    sql = sql.format(table_name=table_name, column_names =col_str,  column_args=args_str)
    print('Inserting rows into specified columns...')
    print(sql)

    with self.conn.cursor() as cur:
        cur.executemany(sql, rows)
        self.conn.commit()

Upvotes: 1

Views: 2103

Answers (1)

kfinity
kfinity

Reputation: 9091

In your SQL statement, you're passing all the values as bind variables. Which is normally great! But bind variables are not interpreted by the SQL engine. So for the AJ_ID column, you're not calling sequence pseudocolumn an_aj_s.nextval like you want to, you're passing a varchar2 string 'an_aj_s.nextval'. Oracle is expecting a number, not a string, so you get a ORA-01722 error.

You need to modify your function so that instead of this:

INSERT INTO AN_JOB (AJ_ID,AJ_JOBNAME,AJ_DESCRIPTION,AJ_CD_JOB_TYPE,AJ_CD_DATABASE,AJ_CD_JOB_STATUS,AJ_USR_ID,AJ_CREATE_DATE,AJ_EQUIP_COMPS_FLAG) 
VALUES ( :1, :2, :3, :4, :5, :6, :7, :8, :9 )

you're generating this:

INSERT INTO AN_JOB (AJ_ID,AJ_JOBNAME,AJ_DESCRIPTION,AJ_CD_JOB_TYPE,AJ_CD_DATABASE,AJ_CD_JOB_STATUS,AJ_USR_ID,AJ_CREATE_DATE,AJ_EQUIP_COMPS_FLAG) 
VALUES ( an_aj_s.nextval, :2, :3, :4, :5, :6, :7, :8, :9 )

Does that make sense? There's a lot of ways you could change your function to do that, but you haven't explained your other use cases, so I don't want to suggest any particular one.

Alternately, you'll probably find it easier to do this on the Oracle side by creating a sequence trigger. There's a lot of examples on SO (like this)

CREATE OR REPLACE TRIGGER an_job_id_trigger
BEFORE INSERT ON an_job
FOR EACH ROW
  WHEN (new.ID IS NULL)
BEGIN
  :new.ID := an_aj_s.nextval;
END;
/

That way, you can just leave out the ID column from your inserts altogether, and the trigger will handle it:

INSERT INTO AN_JOB (AJ_JOBNAME,AJ_DESCRIPTION,AJ_CD_JOB_TYPE,AJ_CD_DATABASE,AJ_CD_JOB_STATUS,AJ_USR_ID,AJ_CREATE_DATE,AJ_EQUIP_COMPS_FLAG) 
VALUES (:1, :2, :3, :4, :5, :6, :7, :8 )

Upvotes: 3

Related Questions