Calling Oracle stored procedures

[Telman Yusupov]

Hi everyone,

I'm developing a web2py application on top of a legacy Oracle database
and need to call some existing stored procedures that mplement some
complex transactional computations that would be impractical to
perform in application layer.

I have cx-oracle driver installed and properly working - DAL works
fine for straightforward web2py ORM activities.

cx-oracle implements callproc function for these purposes.
Unfortunately, I'm new to Python and wasn't able to modify sql.py in
gluon package to add it to DAL.

So, instead, I'm trying to use executesql method in my controller
trying to pass an anonymous PL/SQL block, as in this example:

db.executesql('begin my_proc_name(arg1, arg2); end;')

Unfortunately, I keep getting errors like the one below. I think I've
tried every possible combination but none of them work.

DatabaseError: ORA-06550: line 1, column 26:
PLS-00103: Encountered the symbol "end-of-file" when expecting one of
the following:

; <an identifier> <a double-quoted delimited-identifier>
The symbol ";" was substituted for "end-of-file" to continue

Can you please advise what would be the best option here? Any
suggestions will be very much appreciated!

Thanks a lot in advance,

Telman

[mdipierro]

You can use

db._connection # the raw cx_oracle connection

or

db._cursor # the raw cx_oracle cursor

as in

db._cursor.execute("your raw sql")

Mind that I believe cx_oracle does not like the terminating ";". You
need to try with and without that.

Massimo

[Telman Yusupov]

Thank you very much, Massimo! It worked with this syntax:

db._cursor.execute("begin my_proc_name('arg1', 'arg2'); end;")

- it did require a trailing semicolon.

Thanks again,

Telman