从cx_Oracle调用预言机过程

Question

>Query，在IDE中工作，我想执行：

begin
  sys.dbms_scheduler.create_schedule(schedule_name   => 'U6_GB.SCHED_DAILY_7_30',
                                     start_date      => to_date(null),
                                     repeat_interval => 'Freq=Daily;ByHour=7;ByMinute=30',
                                     end_date        => to_date(null),
                                     comments        => '');
end;

程序签名：

 DBMS_SCHEDULER.CREATE_SCHEDULE (
   schedule_name          IN VARCHAR2,
   start_date             IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   repeat_interval        IN VARCHAR2,
   end_date               IN TIMESTAMP WITH TIMEZONE DEFAULT NULL,
   comments               IN VARCHAR2 DEFAULT NULL);

我试过：

from django.db import connection
cursor = connection.cursor()
cursor.callproc('''
    begin
      sys.dbms_scheduler.create_schedule(schedule_name   => 'U6_GB.SCHED_DAILY_7_30',
                                         start_date      => to_date(null),
                                         repeat_interval => 'Freq=Daily;ByHour=7;ByMinute=30',
                                         end_date        => to_date(null),
                                         comments        => '');
    end;
''')

我得到了这个：

return self.cursor.callproc(procname)
cx_Oracle.DatabaseError: ORA-06550: line 9, column 2:
PLS-00103: Encountered the symbol ")" when expecting one of the following:

然后我尝试：

cursor.callproc('SYS.DBMS_SCHEDULER.CREATE_SCHEDULE', (
    "U6_GB.SCHED_DAILY_7_30",
    "to_date(null)",
    "Freq=Daily;ByHour=7;ByMinute=30",
    "to_date(null)",
    "",
))

我明白了：

return self.cursor.callproc(procname, params)
cx_Oracle.DatabaseError: ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1

我做错了什么？

Python 3.6.2 (

v3.6.2：5fd33b5， Jul 8 2017， 04：14：34( [MSC v.1900 32 位 (Intel(] on win32

Oracle 数据库 11g 企业版版本 11.2.0.4.0

cx-Oracle==6.0.3

Answer 1

试试这个：把你的调用字符串放在双引号内，然后用execute

。

from django.db import connection
cursor = connection.cursor()
cursor.execute("
    begin
      sys.dbms_scheduler.create_schedule(schedule_name   => 'U6_GB.SCHED_DAILY_7_30',
                                         start_date      => to_date(null),
                                         repeat_interval => 'Freq=Daily;ByHour=7;ByMinute=30',
                                         end_date        => to_date(null),
                                         comments        => '');
    end;
")

Answer 2

您可以使用 callproc(( 和关键字参数进行上述调用，如下所示：

kwArgs = dict(schedule_name = 'U6_GB.SCHED_DAILY_7_30', repeat_interval = 'Freq=Daily;ByHour=7;ByMinute=30') cursor.callproc("dbms_scheduler.create_schedule", [], kwArgs)