C
Christian Long
Hi
I'm having trouble with some stored procedure calls on DCOracle2. I think
the trouble is with dynamic binding.
See version & environment info at end of message
I have 2 stored procedures - call them M4 and M5. They have similar (but not
the same) parameters, and are called from python in a similar manner. M4
succeds and M5 fails. M5 fails because a null string termination character
\x00 has been appended to certain argument, so the argument value does not
match the value in the database table.
Both stored procedures succeed when called from Oracle SQL*Plus
My Python code and the stored procedure signatures are all at the end of the
message
------
M4 results viewed from Python - Succeeds
Before procedure call
('1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', None, 'NA', 'M4 Inv Adjust', None, 'Adjust', 1, 'Y', None, None,
None)
After procedure call
['1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', None, 'NA', 'M4 Inv Adjust', None, 'Adjust', 1, 'Y',
'AAAKfBAAGAAAfXBAAd', 'N', None]
Updated inventory
------
M5 results viewed from Python - Fails
Before procedure call
('1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', 'A', 'Q26FB', None,
'M5 Bin Move', 76746, 'RC', None, None, None)
After procedure call
['1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA\x00',
'A\x00', 'Q26BB\x00', 'A\x00', 'Q26FB\x00', None,
'M5 Bin Move', 76746, 'RC', None, 'Y', 'Area/Bin does not exist in location
master, please enter a valid location.']
An error occurred while saving changes. Changes were not saved.
^--- My error message ----------------------------------------^
Area/Bin does not exist in location master, please enter a valid location.
^--- Oracle stored procedure error message ------------------------------^
-------
As we can see, a null string termination character \x00 has been appended to
five arguments in the returned values form the M5 procedure call.
Thanks very much for your help.
Christian Long
----------------------------------------------------------------------------
My Research
----------------------------------------------------------------------------
I did a diff between the tracelog files. Here are some interesting
snippets. Let me know if you want the full dump files. ~1MB.
It looks like the failing call is using dynamic binding , and the successful
one is using static.
Here we see the calls to two different stored procedures
<snip>
< 33, OCIStmtPrepare, Ox########, Ox######## ?= BEGIN
PROCIAPI.ADJUST_INVENTORY
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15
,:16,:17); END;, 98, *
---1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16);
END;, 87, *
</snip>
This looks like a trouble spot:
< 64, bindObject, SQLT_STR, Ox######## ?= A, *
< 18, bindObject, 0, 5, Ox########, 2, *
< 33, OCIBindByPos, Ox########, 6, Ox########, 2, 5, 0, *
< 97, OCIBindByPos, supplemental, 0, Ox########, 0, 2, *
---
</snip>
And later, we see datatype conflict in the dynamic binding of the failing
stored procedure. Note the points of failure correspond to the parameters
that come back with \x00 appended to them. EA, A, Q26BB, A Q26FB
----------------------------------------------------------------------------
My Python code
----------------------------------------------------------------------------
---
M4
t_empno = utility.userId
t_item_no = dataBucket['K_itemNumber']
t_trans_dt = mra_data_m4.getTodayAsOracleDate()
t_trans_qty = dataBucket['K_adjustQuantity']
t_trans_um = dataBucket['K_stockUM']
t_stock_area = dataBucket['K_stockArea']
t_bin_loc = dataBucket['K_binLoc']
t_control_no = None
t_reason_cd = dataBucket['K_reasonCode']
t_reference = HEADER_TEXT
t_trans_type = None
t_ref_order = "Adjust"
t_rtv_no = 1
t_insert_log = "Y"
t_rowid = None
t_failed = None
t_message = None
# Pack the parameters into a tuple
parameters = (t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_stock_area,
t_bin_loc,
t_control_no,
t_reason_cd,
t_reference,
t_trans_type,
t_ref_order,
t_rtv_no,
t_insert_log,
t_rowid,
t_failed,
t_message)
if __debug__:
print "Before procedure call"
print parameters
parameters = mra_data_m4.callAdjustInventoryProcedure(parameters) <---
see function code below
if __debug__:
print "After procedure call"
print parameters
# Unpack the returned list
(t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_stock_area,
t_bin_loc,
t_control_no,
t_reason_cd,
t_reference,
t_trans_type,
t_ref_order,
t_rtv_no,
t_insert_log,
t_rowid,
t_failed,
t_message) = parameters
---
M5
t_empno = utility.userId
t_item_no = dataBucket['K_itemNumber']
t_trans_dt = mra_data_m5.getTodayAsOracleDate()
t_trans_qty = dataBucket['K_adjustQuantity']
t_trans_um = dataBucket['K_stockUM']
t_from_area = dataBucket['K_stockArea']
t_from_bin = dataBucket['K_binLoc']
t_to_area = dataBucket['K_destinationStockArea']
t_to_bin = dataBucket['K_destinationBinLoc']
t_control_no = None
t_reference = HEADER_TEXT
t_batch_no = mra_data_m5.getBatchNumber()
t_reason_code = "RC"
t_rowid = None
t_failed = None
t_message = None
# Pack the parameters into a tuple
parameters = (t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_from_area,
t_from_bin,
t_to_area,
t_to_bin,
t_control_no,
t_reference,
t_batch_no,
t_reason_code,
t_rowid,
t_failed,
t_message)
if __debug__:
print "Before procedure call"
print parameters
parameters = mra_data_m5.callBinMoveProcedure(parameters) <--- see
function code below
if __debug__:
print "After procedure call"
print parameters
# Unpack the returned list
(t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_from_area,
t_from_bin,
t_to_area,
t_to_bin,
t_control_no,
t_reference,
t_batch_no,
t_reason_code,
t_rowid,
t_failed,
t_message) = parameters
---
The mra_data_m*.call*Procedure(parameters) functions are very simple, as
follows
<snip>
PROC_NAME = "prociapi.icbm_move"
c = cn.cursor()
spParams = c.callproc(PROC_NAME, *spParams)
return spParams
</snip>
All the data access functions share one DCOracle2.connection object, and
each function declres its own cursor. M5 fails consistently, i.e. it
doesn't matter if M4 is run first or not, M5 fails.
----------------------------------------------------------------------------
Oracle Stored Procedure signatures
----------------------------------------------------------------------------
M4
procedure adjust_inventory(t_empno in varchar2, -- required
t_item_no in varchar2, -- required
t_trans_dt in date, -- can default to sysdate
t_trans_qty in number, -- required
t_trans_um in varchar2, -- can default
t_stock_area in varchar2, -- can default (ICEM)
t_bin_loc in varchar2, -- can default (ICEM)
t_control_no in varchar2, -- conditional null
t_reason_cd in varchar2, -- required
t_reference in varchar2, -- can default
t_trans_type in varchar2, -- can default
t_ref_order in varchar2, -- can default
t_rtv_no in number, -- can be null
t_insert_log in varchar2, -- required
t_rowid in out varchar2,
t_failed in out varchar2,
t_message in out varchar2);
M5
procedure icbm_move(t_empno in varchar2, -- required
t_item_no in varchar2, -- required
t_trans_dt in out date, -- can default to sysdate
t_trans_qty in number, -- required
t_trans_um in out varchar2, -- can default from
t_from_area in out varchar2, -- can default from
t_from_bin in out varchar2, -- can default from
t_to_area in out varchar2, -- can default from
t_to_bin in out varchar2, -- can default from
t_control_no in out varchar2, -- conditional null
t_reference in varchar2, -- can default
t_batch_no in number, -- can default
t_reason_code in varchar2, -- conditional null
t_rowid in out varchar2,
t_failed in out varchar2,
t_message in out varchar2);
----------------------------------------------------------------------------
Version & config info:
----------------------------------------------------------------------------
HP-UX B 11.00
PL/SQL 8.1.7.4.0 (Production)
Oracle8i Enterprise Edition 8.1.7.4.0 (Production)
TNS for HPUX: 8.1.7.4.0 (Production)
NLSRTL 3.4.1.0.0 (Production)
DCOracle2 1.2 -- DCOracle2 1.95 (dco2:
1.120 -DORACLE8i -DUSEOBJECT -D_REENTRANT )
Environment:
PATH=$PATH:/usr/local/bin
PYTHONPATH=/usr/local/bin/mra
LD_PRELOAD=/usr/lib/libcl.2
ORACLE_HOME=/u01/app/oracle/product/8.1.7
ORACLE_SID=blabla
DCO2TRACELOG=dco2.tracelog
DCO2TRACEDUMP=dco2.tracedump
DCO2TRACEFLAGS=255
I'm having trouble with some stored procedure calls on DCOracle2. I think
the trouble is with dynamic binding.
See version & environment info at end of message
I have 2 stored procedures - call them M4 and M5. They have similar (but not
the same) parameters, and are called from python in a similar manner. M4
succeds and M5 fails. M5 fails because a null string termination character
\x00 has been appended to certain argument, so the argument value does not
match the value in the database table.
Both stored procedures succeed when called from Oracle SQL*Plus
My Python code and the stored procedure signatures are all at the end of the
message
------
M4 results viewed from Python - Succeeds
Before procedure call
('1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', None, 'NA', 'M4 Inv Adjust', None, 'Adjust', 1, 'Y', None, None,
None)
After procedure call
['1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', None, 'NA', 'M4 Inv Adjust', None, 'Adjust', 1, 'Y',
'AAAKfBAAGAAAfXBAAd', 'N', None]
Updated inventory
------
M5 results viewed from Python - Fails
Before procedure call
('1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA', 'A',
'Q26BB', 'A', 'Q26FB', None,
'M5 Bin Move', 76746, 'RC', None, None, None)
After procedure call
['1779', '199318', OracleDate("2003-08-07 00:00:00"), 1.0, 'EA\x00',
'A\x00', 'Q26BB\x00', 'A\x00', 'Q26FB\x00', None,
'M5 Bin Move', 76746, 'RC', None, 'Y', 'Area/Bin does not exist in location
master, please enter a valid location.']
An error occurred while saving changes. Changes were not saved.
^--- My error message ----------------------------------------^
Area/Bin does not exist in location master, please enter a valid location.
^--- Oracle stored procedure error message ------------------------------^
-------
As we can see, a null string termination character \x00 has been appended to
five arguments in the returned values form the M5 procedure call.
Thanks very much for your help.
Christian Long
----------------------------------------------------------------------------
My Research
----------------------------------------------------------------------------
I did a diff between the tracelog files. Here are some interesting
snippets. Let me know if you want the full dump files. ~1MB.
It looks like the failing call is using dynamic binding , and the successful
one is using static.
Here we see the calls to two different stored procedures
<snip>
< 33, OCIStmtPrepare, Ox########, Ox######## ?= BEGIN
PROCIAPI.ADJUST_INVENTORY
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16,:17); END;, 98, *
---
PROCIAPI.ICBM_MOVE
1,:2,:3,:4,:5,:6,:7,:8,:9,:10,:11,:12,:13,:14,:15,:16);END;, 87, *
</snip>
This looks like a trouble spot:
7253,7256c7669,7672
< 64, bindObject, SQLT_STR, Ox######## ?= A, *
< 18, bindObject, 0, 5, Ox########, 2, *
< 33, OCIBindByPos, Ox########, 6, Ox########, 2, 5, 0, *
< 97, OCIBindByPos, supplemental, 0, Ox########, 0, 2, *
---
</snip>
And later, we see datatype conflict in the dynamic binding of the failing
stored procedure. Note the points of failure correspond to the parameters
that come back with \x00 appended to them. EA, A, Q26BB, A Q26FB
</snip>
----------------------------------------------------------------------------
My Python code
----------------------------------------------------------------------------
---
M4
t_empno = utility.userId
t_item_no = dataBucket['K_itemNumber']
t_trans_dt = mra_data_m4.getTodayAsOracleDate()
t_trans_qty = dataBucket['K_adjustQuantity']
t_trans_um = dataBucket['K_stockUM']
t_stock_area = dataBucket['K_stockArea']
t_bin_loc = dataBucket['K_binLoc']
t_control_no = None
t_reason_cd = dataBucket['K_reasonCode']
t_reference = HEADER_TEXT
t_trans_type = None
t_ref_order = "Adjust"
t_rtv_no = 1
t_insert_log = "Y"
t_rowid = None
t_failed = None
t_message = None
# Pack the parameters into a tuple
parameters = (t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_stock_area,
t_bin_loc,
t_control_no,
t_reason_cd,
t_reference,
t_trans_type,
t_ref_order,
t_rtv_no,
t_insert_log,
t_rowid,
t_failed,
t_message)
if __debug__:
print "Before procedure call"
print parameters
parameters = mra_data_m4.callAdjustInventoryProcedure(parameters) <---
see function code below
if __debug__:
print "After procedure call"
print parameters
# Unpack the returned list
(t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_stock_area,
t_bin_loc,
t_control_no,
t_reason_cd,
t_reference,
t_trans_type,
t_ref_order,
t_rtv_no,
t_insert_log,
t_rowid,
t_failed,
t_message) = parameters
---
M5
t_empno = utility.userId
t_item_no = dataBucket['K_itemNumber']
t_trans_dt = mra_data_m5.getTodayAsOracleDate()
t_trans_qty = dataBucket['K_adjustQuantity']
t_trans_um = dataBucket['K_stockUM']
t_from_area = dataBucket['K_stockArea']
t_from_bin = dataBucket['K_binLoc']
t_to_area = dataBucket['K_destinationStockArea']
t_to_bin = dataBucket['K_destinationBinLoc']
t_control_no = None
t_reference = HEADER_TEXT
t_batch_no = mra_data_m5.getBatchNumber()
t_reason_code = "RC"
t_rowid = None
t_failed = None
t_message = None
# Pack the parameters into a tuple
parameters = (t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_from_area,
t_from_bin,
t_to_area,
t_to_bin,
t_control_no,
t_reference,
t_batch_no,
t_reason_code,
t_rowid,
t_failed,
t_message)
if __debug__:
print "Before procedure call"
print parameters
parameters = mra_data_m5.callBinMoveProcedure(parameters) <--- see
function code below
if __debug__:
print "After procedure call"
print parameters
# Unpack the returned list
(t_empno,
t_item_no,
t_trans_dt,
t_trans_qty,
t_trans_um,
t_from_area,
t_from_bin,
t_to_area,
t_to_bin,
t_control_no,
t_reference,
t_batch_no,
t_reason_code,
t_rowid,
t_failed,
t_message) = parameters
---
The mra_data_m*.call*Procedure(parameters) functions are very simple, as
follows
<snip>
PROC_NAME = "prociapi.icbm_move"
c = cn.cursor()
spParams = c.callproc(PROC_NAME, *spParams)
return spParams
</snip>
All the data access functions share one DCOracle2.connection object, and
each function declres its own cursor. M5 fails consistently, i.e. it
doesn't matter if M4 is run first or not, M5 fails.
----------------------------------------------------------------------------
Oracle Stored Procedure signatures
----------------------------------------------------------------------------
M4
procedure adjust_inventory(t_empno in varchar2, -- required
t_item_no in varchar2, -- required
t_trans_dt in date, -- can default to sysdate
t_trans_qty in number, -- required
t_trans_um in varchar2, -- can default
t_stock_area in varchar2, -- can default (ICEM)
t_bin_loc in varchar2, -- can default (ICEM)
t_control_no in varchar2, -- conditional null
t_reason_cd in varchar2, -- required
t_reference in varchar2, -- can default
t_trans_type in varchar2, -- can default
t_ref_order in varchar2, -- can default
t_rtv_no in number, -- can be null
t_insert_log in varchar2, -- required
t_rowid in out varchar2,
t_failed in out varchar2,
t_message in out varchar2);
M5
procedure icbm_move(t_empno in varchar2, -- required
t_item_no in varchar2, -- required
t_trans_dt in out date, -- can default to sysdate
t_trans_qty in number, -- required
t_trans_um in out varchar2, -- can default from
t_from_area in out varchar2, -- can default from
t_from_bin in out varchar2, -- can default from
t_to_area in out varchar2, -- can default from
t_to_bin in out varchar2, -- can default from
t_control_no in out varchar2, -- conditional null
t_reference in varchar2, -- can default
t_batch_no in number, -- can default
t_reason_code in varchar2, -- conditional null
t_rowid in out varchar2,
t_failed in out varchar2,
t_message in out varchar2);
----------------------------------------------------------------------------
Version & config info:
----------------------------------------------------------------------------
HP-UX B 11.00
PL/SQL 8.1.7.4.0 (Production)
Oracle8i Enterprise Edition 8.1.7.4.0 (Production)
TNS for HPUX: 8.1.7.4.0 (Production)
NLSRTL 3.4.1.0.0 (Production)
DCOracle2 1.2 -- DCOracle2 1.95 (dco2:
1.120 -DORACLE8i -DUSEOBJECT -D_REENTRANT )
Environment:
PATH=$PATH:/usr/local/bin
PYTHONPATH=/usr/local/bin/mra
LD_PRELOAD=/usr/lib/libcl.2
ORACLE_HOME=/u01/app/oracle/product/8.1.7
ORACLE_SID=blabla
DCO2TRACELOG=dco2.tracelog
DCO2TRACEDUMP=dco2.tracedump
DCO2TRACEFLAGS=255