Binding variables in xml string to execute PL/SQL statement

[urosdigital]

I have PL/SQL statement which accepts xml string as parameter as shown below:

Exec my_user.AQ_UTIL.ENQUEUE('my_xml_string', 'my_user.Q_A_TO_B', NULL);

where my_xml_string is in form like this:

...
	<subscriberInfo>
		<msisdn>123456789</msisdn>
		<iccId>998877665544332211</iccId>
		<imsi>11223344556677</imsi>
		<serviceProviderId>my_service_provider</serviceProviderId>
		<paymentType>POSTPAID</paymentType>
	</subscriberInfo>
...

I tried to bind variables by position like this (https://python-oracledb.readthedocs.io/en/latest/user_guide/bind.html#binding-by-name-or-position)

query ='''
begin
my_user.AQ_UTIL.ENQUEUE('...<subscriberInfo><msisdn>:1</msisdn><iccId>:2</iccId><imsi>:3</imsi><serviceProviderId>:4</serviceProviderId><paymentType>:5</paymentType></subscriberInfo>', 'my_user.Q_A_TO_B', NULL);
end;
'''

a small function to test execution:

def execute_statement(in_msisdn, in_icc, in_imsi, in_carrier, in_payment_type):
    cursor.execute(query, [in_msisdn, in_icc, in_imsi, in_carrier, in_payment_type])
    connection.commit()
    return

when this function is executed i always get the following error:

code: 1036
full code: ORA-01036
error message: ORA-01036: illegal variable name/number

if i try to use bind_names instead, i also get the same error

query ='''
begin
my_user.AQ_UTIL.ENQUEUE('...<subscriberInfo><msisdn>:phone_number</msisdn><iccId>:icc</iccId><imsi>:imsi</imsi><serviceProviderId>:carrier</serviceProviderId><paymentType>:payment_type</paymentType></subscriberInfo>', 'my_user.Q_A_TO_B', NULL);
end;
'''

def execute_statement(in_msisdn, in_icc, in_imsi, in_carrier, in_payment_type):
    cursor.execute(query, phone_number=in_msisdn, icc=in_icc, imsi=in_imsi, carrier=in_carrier, paymnet_type=in_payment_type)
    connection.commit()
    return

the same error appears when using dictionary:

def execute_statement(in_msisdn, in_icc, in_imsi, in_carrier, in_payment_type):
    data = dict(phone_number=in_msisdn, icc=in_icc, imsi=in_imsi, carrier=in_carrier, paymnet_type=in_payment_type)
    cursor.execute(query, data)
    connection.commit()
    return

in the end if i do string interpolation and assign value to the query, there is no errors but i do not want to use string interpolation in my queries

query ='''
begin
my_user.AQ_UTIL.ENQUEUE(:1, 'my_user.Q_A_TO_B', NULL);
end;
'''

def execute_statement(in_msisdn, in_icc, in_imsi, in_carrier, in_payment_type):
	formated_xml = xml.format(phone_number=in_msisdn, input_icc=in_icc, input_imsi=in_imsi, carrier=in_carrier, payment_type=in_payment_type)
	cursor.execute(query, [formated_xml])
	connection.commit()
	return

I can not figure out where is the mistake in my code. Can someone help to overcome this issue?

[anthony-tuininga]

Bind variables cannot be placed inside strings -- which is what you are doing. You would have to do something like this instead:

query ='''
begin
my_user.AQ_UTIL.ENQUEUE('...<subscriberInfo><msisdn>' || :1 || '</msisdn><iccId>' || :2 || '</iccId><imsi>' || :3 || '</imsi><serviceProviderId>' || :4 || '</serviceProviderId><paymentType>' || :5 || '</paymentType></subscriberInfo>', 'my_user.Q_A_TO_B', NULL);
end;

As noted, the other option is to use Python to create the string and pass that in as a single bind variable.

[urosdigital]

Thanks a million @anthony-tuininga!