[Zope-DB] Oracle Date in DCOracle2's Stored Procedure

[Matthew T. Kromer]

DCOracle2 doesn't try very hard to make sure whatever string 
representation of a date that you pass in is actually a date.  It relies 
on Oracle's native  conversion mechanisms to do this.

For using from stored procedures, you should  pass dates in that you 
create with the Date, Time or DateTime constructors on the DCOracle2 
module, e.g.

date = DCOracle2.Date(2003,01,11)

c.procedure.pm.pm_08.insert_day_report(date, ...)

If you dont do that, the type gets bound as whatever the input type is, 
in your example a string.  Without going and looking at the code <grin> 
I think that singleton IN parameters just get bound as normal parameters 
-- ie based on the Python type, rather than the signature of the procedure.

As such, you told Oracle to go figure out what the date "20030111" was, 
and it probably wanted "2003-01-11" or "11-01-2003" or "01-11-2003" 
depending on your date formatting settings.

You could make the assertion that the stored procedure code for Zope 
Oracle Stored Procedure objects are broken because they don't convert 
from Zope's DateTime to the OracleDate object and vice versa.


Bo M. Maryniuck wrote:

>Hi.
>
>There is a problem (bug?) to pass a DATE type argument to stored procedure.
>Worse, that a half-working stuff. From a pure Python all works (!) fine:
>
>*bo@zope:(~/test) python
>Python 2.2 (#1, Mar 26 2002, 15:46:04) 
>[GCC 2.95.3 20010315 (SuSE)] on linux2
>Type "help", "copyright", "credits" or "license" for more information.
>  
>
>>>>import DCOracle2 as o
>>>>cc = o.connect('luser/buzzword@somebase')
>>>>c = cc.cursor()
>>>>c.procedure.pm.pm_08.insert_day_report('20030111', 1330L, '11', 11, 11, '11', '')
>>>>        
>>>>
>[13472, '\x00']
>  
>
>>>>print c.procedure.pm.pm_08.insert_day_report.__doc__                                   
>>>>        
>>>>
>function PM.PM_08.INSERT_DAY_REPORT returns OUT NUMBER, has arguments:
>        P_DT IN DATE
>        P_ACL_ID IN NUMBER
>        P_DESCRIPTION IN VARCHAR2
>        P_SPENT_DAYS IN NUMBER
>        P_DONE_PRC IN NUMBER
>        P_COMMENT IN VARCHAR2
>        P_ERR_MSG IN OUT VARCHAR2
>
>Now, from Zope (Z Oracle Stored Procedure) I got this:
>
>File /opt/zope/lib/python/Products/ZOracleDA/DCOracle2/DCOracle2.py, line 1492, in __call__
>    (Object: PM.PM_08.INSERT_DAY_REPORT)
>  File /opt/zope/lib/python/Products/ZOracleDA/DCOracle2/DCOracle2.py, line 973, in execute
>DatabaseError: (1861, 'ORA-01861: literal does not match format string\nORA-06512: at line 1')
>
>This error is quite common and means that I've passed wrong date. If I call it from a Python Script with the same 
>code as above I use in pure Python, it returns me the same error. How to kick it run under Zope? Note, that I can't
>change P_DT variable to VARCHAR2 type inside the func.
>
>  
>