How do I run non query sql commands using cx_Oracle?

Question

I am trying to run these commands using cx_oracle:

begin
add_command_pkg.add_command
(  command_id    => 7,
   expiry_time   => sysdate + 7
 );

add_command_pkg.add_command
(  command_id    => 12,
   expiry_time   => sysdate + 7
 );
commit;
end;

So this is my Python code:

dsn = cx_Oracle.makedsn(hostname, port, sid)
orcl = cx_Oracle.connect(username + '/' + password + '@' + dsn)

curs = orcl.cursor()
cmd = "begin \n\
       add_command_pkg.add_command \n\
       (  command_id    => 7, \n\
          expiry_time   => sysdate + 7 \n\
       ); \n\
       \n\
       add_command_pkg.add_command \n\
       (  command_id    => 12, \n\
          expiry_time   => sysdate + 7 \n\
       ); \n\
       commit; \n\
       end;"

curs.execute(cmd)
orcl.close()

When I run this code, I get this error:

cx_Oracle.InterfaceError: not a query

So how do I run these sql commands that aren't queries using cx_oracle?

Edit:

After making changes this is what I have now:

            curs.callproc('add_command_pkg.add_command', [],
                          { 'command_id' : 7,
                            'session_id' : 'null',
                            'p_expiry_time' : 'sysdate + 7',
                            'config_id' : 6 })

When I run this, I get this error:

File "N:\App\MainWidget.py", line 456, in myFunc
'config_id' : 6 })
cx_Oracle.DatabaseError: ORA-01858: a non-numeric character was found where a numeric was expected
ORA-06512: at line 1

Also, how do I commit this?

aneroid · Accepted Answer · 2016-11-17 07:17:22Z

7

The best way is to call the procedure directly using callproc.

curs.callproc['add_command_pkg.add_command',['7', 'sysdate + 7']]
orcl.commit()

or if you need to use keyword arguments directly use a dictionary not a list.

curs.callproc['add_command_pkg.add_command'
             , {'command_id' : '7', 'expiry_time' : 'sysdate + 7'}]
orcl.commit()

The actual syntax is

curs.callproc['package_name.procedure_name'
             , ['list_argument1', 'list_argument2']
             , {'keyword_argument1' : 'keyword1'}
             ]

Which is the same as the following in Oracle

begin
    package_name.procedure_name( 'list_argument1', 'list_argument2'
                               , keywork_argument1 => 'keyword1');
end;

Whilst I'm about the connect method can be called in the following way without the need for concatenation:

 cx_Oracle.connect(username, password, dsn)

edited Nov 17, 2016 at 7:17

aneroid

16.7k3 gold badges42 silver badges77 bronze badges

answered Jan 23, 2012 at 22:25

Ben

53.1k36 gold badges133 silver badges156 bronze badges

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

3 Comments

Di Zou Over a year ago

Thasnk!. I made some changes to my code, but I'm getting a new error now. Can you take a look?

Ben Over a year ago

It looks like it might be a problem with the Oracle procedure itself as I would expect a TypeError to be raised by python, but you are passing some variables incorrectly, for instance 'null' should just be None.

Di Zou Over a year ago

how should i pass the date in? When I try to pass it in as 'sysdate + 7', I get an error. When I try to pass it in as datetime.now() + timedelta(seconds=10) my application locks up.

Collectives™ on Stack Overflow

How do I run non query sql commands using cx_Oracle?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related