[Zope-DB] Another problem with DCOracle2 stored procedure calls
Christian Long
clong at info-advan.com
Sun Sep 28 04:35:26 EDT 2003
Hi
I'm having trouble with stored procedure calls on DCOracle2.
See version & environment info at end of message
I tried to report it on the DCOracle2 issue tracker, but I get "Insufficient
Privileges
You do not have sufficient privileges to view this page."
I'm getting the following error
DatabaseError: (6502, 'ORA-06502: PL/SQL: numeric or value error: character
to number conversion error\nORA-06512: at line 1')
This occurs when I'm trying to pass in a NULL (python None) as the value of
a number IN/OUT parameter. I can pass an integer on a number IN/OUT
parameter with no problem, and I can also pass a NULL on an IN parameter.
The problem occurs only when I'm passing a NULL on an IN/OUT number
parameter.
When I pass in NULL on an IN/OUT number, it fails when doing the dynamic
bind on the IN. I'm not sure why it's doing a dynamicBindIn for a NULL
parameter.
Dynamic binding (lines 347-357 of fails_w_null_inout.tracelog)
* 1064727811.791, 33, OCIStmtExecute, 1, *
* 1064727811.792, 17, dynamicBindIn, 0x401d0940, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e7b80 ?= 120631, 6, 0x401858a0,
0, 0, *
* 1064727811.792, 17, dynamicBindIn, 0x401d0990, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e3c80 ?= 1, 2, 0x401922b8, 0, 0,
*
* 1064727811.792, 17, dynamicBindIn, 0x401d0a08, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e4e40 ?= None, 5, 0x40198670, 0,
0, *
* 1064727811.840, 34, OCIStmtExecute, -1, *
* 1064727811.840, 36, RaiseOCIError, 6502, 0x7f7f2788 ?= ORA-06502: PL/SQL:
numeric or value error: character to number conversion error
ORA-06512: at line 1
I have identified three different cases:
1) integer IN/OUT - succeeds
2) NULL IN/OUT - fails
3) NULL IN - succeeds.
I can send the relevant tracelog entries for each case, and the tracedump
from the failed call if you are interested.
I have included a digest of the most interesting parts of the tracelogs -
see below.
Thanks very much for your help.
Christian Long
----------------------------------------------------------------------------
----------------------------------------------------
Research
----------------------------------------------------------------------------
----------------------------------------------------
Here is how the procedure is declared in PL/SQL
procedure create_recv_detail(t_recv_no in out varchar2, --
required
t_po_no in out
varchar2, --conditionally required
t_po_line in out
number,--conditionally required
t_item_no in out
varchar2,--conditionally required
t_control_no in out
varchar2, --conditionally required
t_dist_seq in out number, -- can be
null
t_date_expires in out date, -- can be null
t_qty in number, -- required
t_Recv_init in varchar2, --
conditionally required
t_message in out varchar2) is
Here is how DCOracle2 connection.describe() sees the procedure
['', 'CREATE_RECV _DETAIL', 'procedure',
[
['', 'T_RECV_NO', 'argument', (1, 0, 0, 0, 0, ('IN', 'OU T'))],
['', 'T_PO_NO', 'argument', (1, 0, 0, 0, 0, ('IN', 'OUT'))],
['', 'T_PO_L INE', 'argument', (2, 22, 0, 0, 0, ('IN', 'OUT'))],
['', 'T_ITEM_NO', 'argument' , (1, 0, 0, 0, 0, ('IN', 'OUT'))],
['', 'T_CONTROL_NO', 'argument', (1, 0, 0, 0, 0, ('IN', 'OUT'))],
['', 'T_DIST_SEQ', 'argument', (2, 22, 0, 0, 0, ('IN', 'OUT '))],
['', 'T_DATE_EXPIRES', 'argument', (12, 0, 0, 0, 0, ('IN', 'OUT'))],
['', 'T_QTY', 'argument', (2, 22, 0, 0, 0, ('IN',))],
['', 'T_RECV_INIT', 'argument', (1, 0, 0, 0, 0, ('IN',))],
['', 'T_MESSAGE', 'argument', (1, 0, 0, 0, 0, ('IN', 'OUT'))]
]
],
Side note cn.describe("myschema.mypackage.myprocedure") causes a Bus error &
coredump, but I was able to snip this description out of
cn.collapsedesc(cn.describe("myschema.mypackage"))
-----------------------------------------------------
Type 1 - Succeeds
-----------------------------------------------------
dist_seq is an IN OUT number parameter. When I call the procedure with the
dist_seq parameter as 0 (zero), the procedure gets called properly.
t_recv_no = '120630'
t_po_no = None
t_po_line = 1
t_item_no = None
t_control_no = None
t_dist_seq = 0 # Procedure called OK when this is 0
t_date_expires = None
t_qty = 45
t_Recv_init = None
t_message = None
Interesting lines from tracelog file: works_wo_nulls_dco2.tracelog
Going in: t_recv_no in out varchar2
10:* 1064725364.383, 64, bindObject, SQLT_STR, 0x401dfb24 ?= 120630, *
12:* 1064725364.383, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 5, 1, *
Going in: t_po_line in out number
42:* 1064725364.387, 64, bindObject, SQLT_INT, 1, *
44:* 1064725364.388, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
62:* 1064725364.390, 17, CONVERTOUTF(SQLT_INT), *
63:* 1064725364.390, 18, CONVERTOUTF(SQLT_INT), 1, 0x40025588, *
67:* 1064725364.391, 64, bindObject, SQLT_STR, 0x4005170c ?= 1, *
Going in: t_dist_seq in out number
113:* 1064725364.397, 64, bindObject, SQLT_INT, 0, *
115:* 1064725364.398, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
133:* 1064725364.400, 17, CONVERTOUTF(SQLT_INT), *
134:* 1064725364.400, 18, CONVERTOUTF(SQLT_INT), 0, 0x40025564, *
138:* 1064725364.400, 64, bindObject, SQLT_STR, 0x400516dc ?= 0, *
Procedure prep:
178:* 1064725364.406, 33, OCIStmtPrepare, 0x401cfb00, 0x401e6014 ?= BEGIN
PROCRAPI.CREATE_RECV_DETAIL(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;, 72, *
Going in: t_qty in number
313:* 1064725364.424, 64, bindObject, SQLT_INT, 45, *
Dynamic binding (lines 350-389)
* 1064725364.429, 33, OCIStmtExecute, 1, *
* 1064725364.430, 17, dynamicBindIn, 0x401d04c0, 0, 0, *
* 1064725364.430, 18, dynamicBindIn, 0x401e6240 ?= 120630, 6, 0x4015f138,
0, 0, *
* 1064725364.430, 17, dynamicBindIn, 0x401d0510, 0, 0, *
* 1064725364.430, 18, dynamicBindIn, 0x401e3288 ?= 1, 2, 0x4018dcf8, 0, 0,
*
* 1064725364.430, 17, dynamicBindIn, 0x401d0588, 0, 0, *
* 1064725364.430, 18, dynamicBindIn, 0x401e5510 ?= 0, 2, 0x401979a8, 0, 0,
*
* 1064725364.498, 17, dynamicBindOut, 0x401d04c0, 0, 0, 0, *
* 1064725364.498, 161, OCIAttrGet, 0x401d2d48, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.498, 162, OCIAttrGet, 0, 0, *
* 1064725364.499, 18, dynamicBindOut, 0x401e6240, 0x40160cc8, 256,
0x4015f138, 0x40161070, *
* 1064725364.499, 17, dynamicBindOut, 0x401d04e8, 0, 0, 0, *
* 1064725364.499, 161, OCIAttrGet, 0x401cfa90, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.499, 162, OCIAttrGet, 0, 0, *
* 1064725364.499, 18, dynamicBindOut, 0x401e64b0, 0x401660d8, 256,
0x40163aa8, 0x40166fa8, *
* 1064725364.499, 17, dynamicBindOut, 0x401d0510, 0, 0, 0, *
* 1064725364.499, 161, OCIAttrGet, 0x401cfa20, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.500, 162, OCIAttrGet, 0, 0, *
* 1064725364.500, 18, dynamicBindOut, 0x401e3288, 0x4018e730, 48,
0x4018dcf8, 0x401917a0, *
* 1064725364.500, 17, dynamicBindOut, 0x401d0538, 0, 0, 0, *
* 1064725364.500, 161, OCIAttrGet, 0x401cf9b0, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.500, 162, OCIAttrGet, 0, 0, *
* 1064725364.500, 18, dynamicBindOut, 0x401e6720, 0x4018ae50, 256,
0x40187bb0, 0x4018b430, *
* 1064725364.500, 17, dynamicBindOut, 0x401d0560, 0, 0, 0, *
* 1064725364.501, 161, OCIAttrGet, 0x401cf940, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.501, 162, OCIAttrGet, 0, 0, *
* 1064725364.501, 18, dynamicBindOut, 0x401e6998, 0x40195768, 256,
0x40195540, 0x40195918, *
* 1064725364.501, 17, dynamicBindOut, 0x401d0588, 0, 0, 0, *
* 1064725364.501, 161, OCIAttrGet, 0x401cf8d0, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.501, 162, OCIAttrGet, 0, 0, *
* 1064725364.501, 18, dynamicBindOut, 0x401e5510, 0x40197fa8, 48,
0x401979a8, 0x40198340, *
* 1064725364.501, 17, dynamicBindOut, 0x401d05b0, 0, 0, 0, *
* 1064725364.502, 161, OCIAttrGet, 0x401cf860, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.502, 162, OCIAttrGet, 0, 0, *
* 1064725364.502, 18, dynamicBindOut, 0x401e6c08, 0x40196860, 256,
0x40196690, 0x40196bc8, *
* 1064725364.502, 17, dynamicBindOut, 0x401d0628, 0, 0, 0, *
* 1064725364.502, 161, OCIAttrGet, 0x401cf700, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064725364.502, 162, OCIAttrGet, 0, 0, *
* 1064725364.502, 18, dynamicBindOut, 0x401e7098, 0x4019c878, 256,
0x4019c1a8, 0x4019ca18, *
* 1064725364.503, 34, OCIStmtExecute, 0, *
Coming out: t_recv_no in out varchar2
405:* 1064725364.507, 17, CONVERTOUTF(SQLT_CHR), 0x401e6240 ?= 120630, 6, *
406:* 1064725364.507, 18, CONVERTOUTF(SQLT_CHR), 0x401df390, *
Coming out: t_po_no in out varchar2
411:* 1064725364.507, 17, CONVERTOUTF(SQLT_CHR), 0x401e64b0 ?= 66123, 5, *
412:* 1064725364.508, 18, CONVERTOUTF(SQLT_CHR), 0x401e2c90, *
Coming out: t_po_line in out number
434:* 1064725364.510, 17, CONVERTOUTF(SQLT_STR), 0x401e3288 ?= 1, 1, *
435:* 1064725364.510, 18, CONVERTOUTF(SQLT_STR), 0x400516f8, *
441:* 1064725364.512, 64, bindObject, SQLT_INT, 1, *
443:* 1064725364.512, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
448:* 1064725364.513, 17, CONVERTOUTF(SQLT_INT), *
449:* 1064725364.513, 18, CONVERTOUTF(SQLT_INT), 1, 0x40025588, *
Coming out: t_item_no in out varchar2
454:* 1064725364.516, 17, CONVERTOUTF(SQLT_CHR), 0x401e6720 ?= 195729, 6, *
455:* 1064725364.516, 18, CONVERTOUTF(SQLT_CHR), 0x40046340, *
Coming out: t_dist_seq in out number
481:* 1064725364.519, 17, CONVERTOUTF(SQLT_STR), 0x401e5510 ?= 0, 1, *
482:* 1064725364.519, 18, CONVERTOUTF(SQLT_STR), 0x400516c8, *
488:* 1064725364.520, 64, bindObject, SQLT_INT, 0, *
490:* 1064725364.520, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
495:* 1064725364.521, 17, CONVERTOUTF(SQLT_INT), *
496:* 1064725364.521, 18, CONVERTOUTF(SQLT_INT), 0, 0x40025564, *
-----------------------------------------------------
Type 2 - Fails
-----------------------------------------------------
dist_seq is an IN OUT number parameter. When I call the procedure with
the dist_seq parameter as None, I get the following error.
DatabaseError: (6502, 'ORA-06502: PL/SQL: numeric or value error: character
to number conversion error\nORA-06512: at line 1')
t_recv_no = '120631'
t_po_no = None
t_po_line = 1
t_item_no = None
t_control_no = None
t_dist_seq = None # Procedure fails when this is None
t_date_expires = None
t_qty = 45
t_Recv_init = None
t_message = None
Interesting lines from tracelog file: fails_w_null_inout.tracelog
Going in: t_recv_no in out varchar2
10:* 1064727811.745, 64, bindObject, SQLT_STR, 0x401d666c ?= 120631, *
12:* 1064727811.745, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 5, 1, *
Going in: t_po_line in out number
42:* 1064727811.750, 64, bindObject, SQLT_INT, 1, *
44:* 1064727811.750, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
62:* 1064727811.752, 17, CONVERTOUTF(SQLT_INT), *
63:* 1064727811.752, 18, CONVERTOUTF(SQLT_INT), 1, 0x40025588, *
67:* 1064727811.753, 64, bindObject, SQLT_STR, 0x4005170c ?= 1, *
Going in: t_dist_seq in out number
134:* 1064727811.762, 64, bindObject, SQLT_STR, 0x401e43a4 ?= None, *
Procedure prep:
174* 1064727811.768, 33, OCIStmtPrepare, 0x401cff80, 0x401e79e4 ?= BEGIN
PROCRAPI.CREATE_RECV_DETAIL(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;, 72, *
Going in: t_qty in number
309:* 1064727811.786, 64, bindObject, SQLT_INT, 45, *
Dynamic binding (lines 347-357)
* 1064727811.791, 33, OCIStmtExecute, 1, *
* 1064727811.792, 17, dynamicBindIn, 0x401d0940, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e7b80 ?= 120631, 6, 0x401858a0,
0, 0, *
* 1064727811.792, 17, dynamicBindIn, 0x401d0990, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e3c80 ?= 1, 2, 0x401922b8, 0, 0,
*
* 1064727811.792, 17, dynamicBindIn, 0x401d0a08, 0, 0, *
* 1064727811.792, 18, dynamicBindIn, 0x401e4e40 ?= None, 5, 0x40198670, 0,
0, *
* 1064727811.840, 34, OCIStmtExecute, -1, *
* 1064727811.840, 36, RaiseOCIError, 6502, 0x7f7f2788 ?= ORA-06502: PL/SQL:
numeric or value error: character to number conversion error
ORA-06512: at line 1
, *
-----------------------------------------------------
Type 3 - Succeeds
-----------------------------------------------------
quantity is an IN number parameter. When I call the procedure with the
quantity parameter as None, the procedure gets called properly, and I get
the appropriate error back from the business logic in the stored procedure
t_recv_no = '120632'
t_po_no = None
t_po_line = 1
t_item_no = None
t_control_no = None
t_dist_seq = 0
t_date_expires = None
t_qty = None # Procedure called OK when this is 0, and
also when it is None
t_Recv_init = None
t_message = None
Interesting lines from tracelog file: works_w_null_in_dco2.tracelog
Going in: t_recv_no in out varchar2
10:* 1064729034.487, 64, bindObject, SQLT_STR, 0x401d3c3c ?= 120632, *
12:* 1064729034.488, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 5, 1, *
Going in: t_po_line in out number
42:* 1064729034.493, 64, bindObject, SQLT_INT, 1, *
44:* 1064729034.493, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
62:* 1064729034.495, 17, CONVERTOUTF(SQLT_INT), *
63:* 1064729034.495, 18, CONVERTOUTF(SQLT_INT), 1, 0x40025588, *
67:* 1064729034.496, 64, bindObject, SQLT_STR, 0x4005170c ?= 1, *
Going in: t_dist_seq in out number
113:* 1064729034.503, 64, bindObject, SQLT_INT, 0, *
115:* 1064729034.503, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
133:* 1064729034.505, 17, CONVERTOUTF(SQLT_INT), *
134:* 1064729034.505, 18, CONVERTOUTF(SQLT_INT), 0, 0x40025564, *
138:* 1064729034.506, 64, bindObject, SQLT_STR, 0x400516dc ?= 0, *
Procedure prep:
178:* 1064729034.511, 33, OCIStmtPrepare, 0x401cef18, 0x401e695c ?= BEGIN
PROCRAPI.CREATE_RECV_DETAIL(:1,:2,:3,:4,:5,:6,:7,:8,:9,:10); END;, 72, *
Dynamic binding (lines 350-389)
* 1064729034.534, 33, OCIStmtExecute, 1, *
* 1064729034.535, 17, dynamicBindIn, 0x401cf8d8, 0, 0, *
* 1064729034.535, 18, dynamicBindIn, 0x401e6af8 ?= 120632, 6, 0x4018be30,
0, 0, *
* 1064729034.535, 17, dynamicBindIn, 0x401cf928, 0, 0, *
* 1064729034.535, 18, dynamicBindIn, 0x401e5dd0 ?= 1, 2, 0x401994c0, 0, 0,
*
* 1064729034.536, 17, dynamicBindIn, 0x401cf9a0, 0, 0, *
* 1064729034.536, 18, dynamicBindIn, 0x401e5f48 ?= 0, 2, 0x401a4018, 0, 0,
*
* 1064729034.561, 17, dynamicBindOut, 0x401cf8d8, 0, 0, 0, *
* 1064729034.561, 161, OCIAttrGet, 0x401d24b0, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.561, 162, OCIAttrGet, 0, 0, *
* 1064729034.562, 18, dynamicBindOut, 0x401e6af8, 0x4018da90, 256,
0x4018be30, 0x4018e6f8, *
* 1064729034.562, 17, dynamicBindOut, 0x401cf900, 0, 0, 0, *
* 1064729034.562, 161, OCIAttrGet, 0x401ceea8, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.562, 162, OCIAttrGet, 0, 0, *
* 1064729034.562, 18, dynamicBindOut, 0x401e6fd8, 0x40195400, 256,
0x401950f0, 0x40195a28, *
* 1064729034.562, 17, dynamicBindOut, 0x401cf928, 0, 0, 0, *
* 1064729034.562, 161, OCIAttrGet, 0x401cee38, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.563, 162, OCIAttrGet, 0, 0, *
* 1064729034.563, 18, dynamicBindOut, 0x401e5dd0, 0x40199b18, 48,
0x401994c0, 0x4019b758, *
* 1064729034.563, 17, dynamicBindOut, 0x401cf950, 0, 0, 0, *
* 1064729034.563, 161, OCIAttrGet, 0x401cedc8, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.563, 162, OCIAttrGet, 0, 0, *
* 1064729034.563, 18, dynamicBindOut, 0x401e7250, 0x40197ed8, 256,
0x40197400, 0x40198078, *
* 1064729034.563, 17, dynamicBindOut, 0x401cf978, 0, 0, 0, *
* 1064729034.563, 161, OCIAttrGet, 0x401ced58, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.564, 162, OCIAttrGet, 0, 0, *
* 1064729034.564, 18, dynamicBindOut, 0x401e6d68, 0x4019dbf8, 256,
0x4019db08, 0x4019e3a0, *
* 1064729034.564, 17, dynamicBindOut, 0x401cf9a0, 0, 0, 0, *
* 1064729034.564, 161, OCIAttrGet, 0x401cece8, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.564, 162, OCIAttrGet, 0, 0, *
* 1064729034.564, 18, dynamicBindOut, 0x401e5f48, 0x401a41e8, 48,
0x401a4018, 0x401a8eb0, *
* 1064729034.564, 17, dynamicBindOut, 0x401cf9c8, 0, 0, 0, *
* 1064729034.564, 161, OCIAttrGet, 0x401cec78, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.565, 162, OCIAttrGet, 0, 0, *
* 1064729034.565, 18, dynamicBindOut, 0x401e74c0, 0x401a0bc8, 256,
0x401a0610, 0x401a1218, *
* 1064729034.565, 17, dynamicBindOut, 0x401cfa40, 0, 0, 0, *
* 1064729034.565, 161, OCIAttrGet, 0x401ceb18, OCI_HTYPE_BIND, 0x7f7f4548,
OCI_ATTR_ROWS_RETURNED, *
* 1064729034.565, 162, OCIAttrGet, 0, 0, *
* 1064729034.565, 18, dynamicBindOut, 0x401e7950, 0x401abec8, 256,
0x401abd78, 0x401abf18, *
* 1064729034.565, 34, OCIStmtExecute, 0, *
Coming out: t_recv_no in out varchar2
404:* 1064729034.569, 17, CONVERTOUTF(SQLT_CHR), 0x401e6af8 ?= 120632, 6, *
405:* 1064729034.569, 18, CONVERTOUTF(SQLT_CHR), 0x401d36e0, *
Coming out: t_po_line in out number
431:* 1064729034.572, 17, CONVERTOUTF(SQLT_STR), 0x401e5dd0 ?= 1, 1, *
432:* 1064729034.572, 18, CONVERTOUTF(SQLT_STR), 0x400516f8, *
438:* 1064729034.574, 64, bindObject, SQLT_INT, 1, *
440:* 1064729034.574, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
445:* 1064729034.575, 17, CONVERTOUTF(SQLT_INT), *
446:* 1064729034.575, 18, CONVERTOUTF(SQLT_INT), 1, 0x40025588, *
Coming out: t_dist_seq in out number
476:* 1064729034.581, 17, CONVERTOUTF(SQLT_STR), 0x401e5f48 ?= 0, 1, *
477:* 1064729034.581, 18, CONVERTOUTF(SQLT_STR), 0x400516c8, *
483:* 1064729034.582, 64, bindObject, SQLT_INT, 0, *
485:* 1064729034.582, 20, BindingArrayObject_ass_item, Type mismatch on
assignment, 3, 2, *
490:* 1064729034.583, 17, CONVERTOUTF(SQLT_INT), *
491:* 1064729034.583, 18, CONVERTOUTF(SQLT_INT), 0, 0x40025564, *
Coming out: t_message in out varchar2 - Reporting a business logic
error as it should.
500:* 1064729034.584, 17, CONVERTOUTF(SQLT_CHR), 0x401e7950 ?= Qty received
must be greater than ze
ro., 39, *
501:* 1064729034.584, 18, CONVERTOUTF(SQLT_CHR), 0x401e7fc0, *
----------------------------------------------------------------------------
----------------------------------------------------
My Python code
----------------------------------------------------------------------------
----------------------------------------------------
All the data access functions share one DCOracle2.connection object, and
each function declres its own cursor.
def callCreateRecvDetail(conn, receivingHeaderNumber, poLine, quantity):
"""
Calls the NDS stored procedure CREATE_RECV_DETAIL.
"""
PROC_NAME = "procrapi.create_recv_detail"
if __debug__: mra_error.debugPrint(None, *(receivingHeaderNumber,
poLine, quantity))
# Build a number of variables, representing the parameters
# needed for the stored procedure call
t_recv_no = receivingHeaderNumber
t_po_no = None
t_po_line = poLine
t_item_no = None
t_control_no = None
t_dist_seq = 0
t_date_expires = None
t_qty = None
t_Recv_init = None
t_message = None
# Pack the parameters into a tuple
parameters = (t_recv_no,
t_po_no,
t_po_line,
t_item_no,
t_control_no,
t_dist_seq,
t_date_expires,
t_qty,
t_Recv_init,
t_message)
# Call the stored procedure. All the paramters (modified and
unmodified)
# are returned as a list from the procedure call. We don't commit these
# changes until all stored procedures have completed sucesssfully.
parameters = mra_data_utility.callProcedure(conn,
PROC_NAME,
parameters,
commit = False)
# Unpack the returned list
(t_recv_no,
t_po_no,
t_po_line,
t_item_no,
t_control_no,
t_dist_seq,
t_date_expires,
t_qty,
t_Recv_init,
t_message) = parameters
if t_message is None:
# Success. Stored procedure ran OK
success = True
else:
# An error occured in the procedure call
mra_screen.printWrap("An error occurred in the create_recv_detail "
\
"stored procedure. Changes " \
"were not saved.", pretty = True)
mra_screen.printWrap(t_message)
mra_error.logNDSError(t_message, utility.userId)
success = False
return success, parameters
def callProcedure(conn, procname, params, commit=True):
"""
Generic SQL execution code for execting a stored procedure. Requires an
open
DCOracle2 connection object, the procedure name, and a sequence of
parameters
for the SQL statement if any are needed. Commit is a boolean indicating
if
the function should do a connection.commit(). Returns a list of
parameters.
"""
c = conn.cursor()
params = c.callproc(procname, *params)
if commit is True:
conn.commit()
return params
----------------------------------------------------------------------------
----------------------------------------------------
Version & config info:
----------------------------------------------------------------------------
----------------------------------------------------
Python 2.2.2 (#2, May 27 2003, 14:57:51)
[GCC 3.2.3] on hp-ux11
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 (cvs release) -- DCOracle2 1.101 (dco2:
1.134 -DORACLE8i -DUSEOBJECT
-DNATIVENUMERIC=1 -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
Christian Long
Technical Analyst
Information Advantage Inc.
5793 Grande Market Dr.
Appleton, WI 54913
(920) 996 1750
www.info-advan.com
More information about the Zope-DB
mailing list