Calling oracle procedure from cx_Oracle

Question

Query, that works in ide and i want to 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;

Procedure signature:

 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);

I have try:

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;
''')

And I got this:

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:

Then i was try:

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)",
    "",
))

And I got that:

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

What am I doing wrong?

Python 3.6.2 (v3.6.2:5fd33b5, Jul 8 2017, 04:14:34) [MSC v.1900 32 bit (Intel)] on win32

Oracle Database 11g Enterprise Edition Release 11.2.0.4.0

cx-Oracle==6.0.3

PKey · Accepted Answer · 2018-03-05 09:57:24Z

2

Try this: put your call string inside double quotes and use 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;
")

edited Mar 5, 2018 at 9:57

answered Mar 5, 2018 at 7:38

PKey

3,8712 gold badges21 silver badges45 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Vladimir Over a year ago

I've tried execute procedure in one line (your code is invalid), same result

PKey Over a year ago

@Vladimir try to use execute instead of callproc. I edited my answer.

Vladimir Over a year ago

Thanks @Plirkee, I tried to do that, my error was in the wrong comment: "-" instead of "--". Epic fail.

Anthony Tuininga · Accepted Answer · 2018-03-07 16:24:27Z

2

You can make a call like the one noted above using callproc() and keyword parameters as follows:

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)

answered Mar 7, 2018 at 16:24

Anthony Tuininga

7,2062 gold badges17 silver badges25 bronze badges

Collectives™ on Stack Overflow

Calling oracle procedure from cx_Oracle

2 Answers 2

3 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

3 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related