[Zope-DB] Problem with DCOracle2 stored procedure calls

Christian Long clong at info-advan.com
Fri Aug 8 14:48:24 EDT 2003


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  pytyhon 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, *
---
>  33, OCIStmtPrepare, Ox########, Ox######## ?= BEGIN
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:
<snip>
---
>  64, bindObject, BindingArrayObject, *
>  18, bindObject, 0, 1, Ox########, 256, *
>  33, OCIBindByPos, Ox########, 5, Ox########, 256, 1, 0, *
>  97, OCIBindByPos, supplemental, 0, Ox########, 2, 3, *
7245a7660,7661
>  33, OCIBindDynamic, Ox########, *
>  34, OCIBindDynamic, 0, OUT, Ox########, Ox########, *
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>
---
>  33, OCIBindByPos, Ox########, 16, Ox########, 256, 1, -1, *
7411a7827,7871
>  17, dynamicBindIn, Ox########, 0, 0, *
>  18, dynamicBindIn, Ox######## ?= xg, 7, Ox########, 0, 0, *
>  17, dynamicBindIn, Ox########, 0, 0, *
>  20, dynamicBindIn, datatype conflict, 1, 5, *
>  18, dynamicBindIn, Ox######## ?= EA, 3, Ox########, 0, 0, *  <-Note "EA"
>  17, dynamicBindIn, Ox########, 0, 0, *
>  20, dynamicBindIn, datatype conflict, 1, 5, *
>  18, dynamicBindIn, Ox######## ?= A, 2, Ox########, 0, 0, * <- "A"
>  17, dynamicBindIn, Ox########, 0, 0, *
>  20, dynamicBindIn, datatype conflict, 1, 5, *
>  18, dynamicBindIn, Ox######## ?= Q26BB, 6, Ox########, 0, 0, * <- "Q26BB"
>  17, dynamicBindIn, Ox########, 0, 0, *
>  20, dynamicBindIn, datatype conflict, 1, 5, *
>  18, dynamicBindIn, Ox######## ?= A, 2, Ox########, 0, 0, * <- "A"
>  17, dynamicBindIn, Ox########, 0, 0, *
>  20, dynamicBindIn, datatype conflict, 1, 5, *
>  18, dynamicBindIn, Ox######## ?= Q26FB, 6, Ox########, 0, 0, * <- "Q26FB"
>  17, dynamicBindOut, Ox########, 0, 0, 0, *
> 161, OCIAttrGet, Ox########, OCI_HTYPE_BIND,
Ox########,OCI_ATTR_ROWS_RETURNED, *
> 162, OCIAttrGet, 0, 0, *
>  18, dynamicBindOut, Ox########, Ox########, 256, Ox########, Ox########,*
>  17, dynamicBindOut, Ox########, 0, 0, 0, *
</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



Christian Long
Technical Analyst
Information Advantage Inc.
5793 Grande Market Dr.
Appleton, WI 54913

(920) 996 1750
www.info-advan.com




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