Getting the error cx_Oracle.DatabaseError: ORA-01008: not all variables bound while trying to bind t-CodePudding

I am banging my head from last few hours but unable to find the root cause. went through multiple articles but no luck.

I am trying to merge the values which are stored inside a pandas dataframe. Below is the code where I am converting the dataframe into list

merchantinfo_lst = appenddata.values.tolist()

Through below code I am trying to merge data into the target table.

sql ='merge into bi.merchant_info_test ';
sql =' using dual';
sql ='   on ( ATRANS_ID = :2 )';
sql =' when matched then update set MERCHANT_INFO = :15, MESSAGE_PARAM = :16, PUBLISH_DATE = :13' ;  
sql =' when not matched then insert( ADDRESS,ATRANS_ID,CITY_NAME,COUNTRY_CODE,LATITUDE,LONGITUDE,MERCHANT_CATEGORY_CODE,MERCHANT_DESCRIPTOR,MERCHANT_ID,';
sql =' MERCHANT_NAME,PHONE_NUMBER,POSTAL_CODE,PUBLISH_DATE,STATE_PROVINCE_CODE,MERCHANT_INFO,MESSAGE_PARAM )'; 
sql ='  values( :1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16 )';
for i in range(len(merchantinfo_lst)):
    cur.execute(sql,merchantinfo_lst[i])

I am getting cx_Oracle.DatabaseError: ORA-01008: not all variables bound but all the value are present in the list and all the column are present in the target table. No matter what I try, I am not able to resolve the issue.

Python version: 2.7.5 cx_Oracle version: 7.3.0 Target database : Oracle Database 19c Enterprise Edition Release 19.0.0.0.0

Table structure

    Name                   Null?    Type           
---------------------- -------- -------------- 
ATRANS_ID              NOT NULL RAW(36 BYTE)   
MERCHANT_ID                     VARCHAR2(20)   
MERCHANT_DESCRIPTOR             VARCHAR2(100)  
MERCHANT_NAME                   VARCHAR2(100)  
ADDRESS                         VARCHAR2(250)  
CITY_NAME                       VARCHAR2(100)  
STATE_PROVINCE_CODE             VARCHAR2(4)    
POSTAL_CODE                     VARCHAR2(10)   
COUNTRY_CODE                    VARCHAR2(10)   
LATITUDE                        VARCHAR2(20)   
LONGITUDE                       VARCHAR2(20)   
PHONE_NUMBER                    VARCHAR2(15)   
MERCHANT_CATEGORY_CODE          VARCHAR2(6)    
MERCHANT_INFO                   VARCHAR2(250)  
MESSAGE_PARAM                   VARCHAR2(2000) 
PUBLISH_DATE                    DATE

Python code

def table_merge(data_frame,uname,passwd,oracle_dsn):
    merchantinfo = pd.DataFrame()
    transidinfo = pd.DataFrame()
    ora_con = oracle_conn(uname,passwd,oracle_dsn)
    cur = ora_con.cursor()
    now = datetime.now()
    dt_string = now.strftime("%d/%m/%Y %H:%M:%S")    
    appenddata['MERCHANT_INFO'] = appenddata['MERCHANT_NAME']  ' '  appenddata['CITY_NAME']  ' ' appenddata['STATE_PROVINCE_CODE']  ' '  appenddata['COUNTRY_CODE']  ' '  appenddata['POSTAL_CODE']
    appenddata['MESSAGE_PARAM'] = appenddata['MERCHANT_ID']  '|'   appenddata['MERCHANT_INFO']
    appenddata['PUBLISH_DATE'] = dt_string
    merchantinfo_lst = appenddata.values.tolist()
    print('printing list')
    print(merchantinfo_lst)
    sql ='merge into bi.merchant_info_test ';
    sql =' using dual';
    sql ='   on ( ATRANS_ID = :2 )';
    sql =' when matched then update set MERCHANT_INFO = :15, MESSAGE_PARAM = :16, PUBLISH_DATE = :13' ;  
    sql =' when not matched then insert( ADDRESS,ATRANS_ID,CITY_NAME,COUNTRY_CODE,LATITUDE,LONGITUDE,MERCHANT_CATEGORY_CODE,MERCHANT_DESCRIPTOR,MERCHANT_ID,';
    sql =' MERCHANT_NAME,PHONE_NUMBER,POSTAL_CODE,PUBLISH_DATE,STATE_PROVINCE_CODE,MERCHANT_INFO,MESSAGE_PARAM )'; 
    sql ='  values( :1, :2, :3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16 )';
    for i in range(len(merchantinfo_lst)):
        cur.execute(sql,merchantinfo_lst[i])
    cur.close()
    ora_con.commit()
    ora_con.close()

Output

    /home/kkant/microservice-poc % python consumer.py
printing list
[[u'dummy_address', u'123456789', u'dummy_city', u'91', u'', u'', u'2345', u'dummy', u'123', u'dummy_merchant', u'1234567890', u'123456', '27/05/2022 11:32:22', u'', u'dummy_merchant dummy_city  91 123456', u'123|dummy_merchant dummy_city  91 123456'], [u'dummy_address', u'1234567455', u'dummy_city', u'91', u'', u'', u'2345', u'dummy', u'456', u'dummy_merchant', u'1234567890', u'123456', '27/05/2022 11:32:22', u'', u'dummy_merchant dummy_city  91 123456', u'456|dummy_merchant dummy_city  91 123456']]
Traceback (most recent call last):
  File "consumer.py", line 102, in <module>
    table_merge(appenddata)       
  File "consumer.py", line 71, in table_merge
    cur.execute(sql,merchantinfo_lst[i])
cx_Oracle.DatabaseError: ORA-01008: not all variables bound

[enter image description here][1]

CodePudding user response：

The problem is that you are binding by position, not by name. So you need an entry for every bind variable position. The value :2 in the first part of your SQL statement needs a value and the value :2 in the last part of your SQL statement also needs a value! (And so on for every bind variable mentioned in your SQL statement -- 20 of them). If you don't want to do that you can bind by name instead (and supply a dictionary of parameters).

One other possibility that may work is to perform a "query" of all of the parameters, as in this:

merge into bi.merchant_info_test target
using select :1 as x, :2, as y, :3 as z, ... from dual source
on target.atrans_id = source.atrans_id
when matched then update set merchant_info = source.merchant_info ...
when not matched then insert (ADDRESS, ...) values (source.address, ...)

That way you only have one place where the bind variables are required instead of two (for some of them).

The new Python driver relesed a couple of days ago (python-oracledb) has better error messages for this sort of thing when using thin mode (see the documentation for more details). It may be worth trying your example with that driver to find out the source of the issue.

On a side note, it is better to use executemany() when inserting multiple rows instead of iterating through the list and calling execute() repeatedly. The difference in performance can be striking, especially for large numbers of rows!