如何调用过程使用cx_Oracle输入日期输入返回游标

问题描述：

我需要使用返回结果作为refcursor的日期查询对象，如果我只是将日期添加到函数中我得到返回（日期不是可定制的）。我试着发送时间只是作为一个字符串，并且不工作要么，我得到我使用如何调用过程使用cx_Oracle输入日期输入返回游标

PROCEDURE prc_get_some_data(
    p_date   IN DATE, 
    p_cursor  OUT SYS_REFCURSOR) 
IS 
BEGIN 
    DBMS_APPLICATION_INFO.SET_CLIENT_INFO ('Python Script'); 
    OPEN p_cursor FOR 
     SELECT * 
     FROM table 
     WHERE creates = p_date; 
EXCEPTION 
    WHEN OTHERS 
     THEN 
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_STACK); 
      DBMS_OUTPUT.PUT_LINE(CHR(10)); 
      DBMS_OUTPUT.PUT_LINE(DBMS_UTILITY.FORMAT_ERROR_BACKTRACE); 
END prc_get_somedatas;

python脚本

import sys, time 
from datetime import date, timedelta 
import random 
import cx_Oracle 
import string 

# Python Script to call a PL/SQL stored procedure to bulk load data 


class Test: 
    def __enter__(self): 
     self.__db = cx_Oracle.connect('/@wallet_string') 
     self.__cursor = self.__db.cursor() 
     return self 

    def __exit__(self, type, value, traceback): 
     self.__cursor.close() 
     self.__db.close() 


    def get_some_data(self): 
     d=date.today() # Initialize a date variable with date of today 

     db_start = time.time() 
     start_all = time.time() 

     self.__cursor.bindarraysize = 2500 
     self.__cursor.arraysize = 10000 

     # create a cursor variable to return the results into 
     l_cur = self.__cursor.var(cx_Oracle.CURSOR) 

     #execute the procedure 
     l_test = self.__cursor.callproc("prc_get_some_data",[datetime.strptime("2007-12-31 23:59:59", "%Y-%m-%d %H:%M:%S"), l_cur])[0] 


     db_elapsed = (time.time() - db_start) 
     total_elapsed = (time.time() - start_all) 

     print "DB:\t ", db_elapsed, " seconds" 
     print "Total:\t ", total_elapsed, " seconds" 

     return list(l_test) 



if __name__ == "__main__": 

    with Test() as mydbconn: 
     for row in mydbconn.get_corporate_actions_equities(): 
      print row

数据库Oracle 12c中一个ORA-01830: date format picture ends before converting entire input string

程序客户：12.1.0.2 任何帮助，将不胜感激。

答

想通了，这要归功于同事 ret_cursor应该是callproc返回的元组中的最后一项。因此，而不是[0]，我们应该有[-1]。

def get_some_data(self): 
    d=date.today() # Initialize a date variable with date of today 

    db_start = time.time() 
    start_all = time.time() 

    self.__cursor.bindarraysize = 2500 
    self.__cursor.arraysize = 10000 

    # create a cursor variable to return the results into 
    l_cur = self.__cursor.var(cx_Oracle.CURSOR) 

    #execute the procedure 
    rep_date = date(2017,07,20) 
    l_test = self.__cursor.callproc("prc_get_some_data",[rep_date, l_cur])[-1] 


    db_elapsed = (time.time() - db_start) 
    total_elapsed = (time.time() - start_all) 

    print "DB:\t ", db_elapsed, " seconds" 
    print "Total:\t ", total_elapsed, " seconds" 

    return list(l_test)

如何调用过程使用cx_Oracle输入日期输入返回游标

相关推荐