How can I create stored procedure from within Python script?

Question

I am looking to way to create an oracle stored procedure in Python.

I know, cursor.callproc in cx_Oracle exists to call the existing stored procedure, but I am looking for a way/method to create an Oracle stored procedure in Python.

What did you try or research? Building stored procedures is just running SQL or plsql code which you can run in Python or any database connected language (Java, PHP, C#, etc.). — Parfait
– Parfait, Commented Sep 22, 2020 at 0:02

Christopher Jones · Accepted Answer · 2020-09-22 00:09:22Z

3

Just use execute() with the SQL CREATE OR REPLACE command.

To check if the procedure was valid, you need to query a base table like user_errors.

with connection.cursor() as cursor:
    cursor.execute("""create or replace procedure x(a in number) as begin BOGUS end;""")
    cursor.execute("""
            select name, type, line, position, text
            from user_errors
            where name = 'X'
            order by name, type, line, position""")
    data = cursor.fetchall()
    print(data)

The output is:

[('X', 'PROCEDURE', 1, 41, 'PLS-00103: Encountered the symbol "END" when expecting one of the following:\n\n   := . ( @ % ;\nThe symbol ";" was substituted for "END" to continue.\n')]

We are looking at how a future version of cx_Oracle can directly expose Oracle's 'success with info' flag and the compilation error message.

edited Sep 22, 2020 at 0:09

answered Sep 21, 2020 at 23:39

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

2 Comments

Zenith_1024 Over a year ago

This does not seem to work when its a multiline procedure ?

Christopher Jones Over a year ago

Works for me :) Did you use triple quotes? Open a new question with behavior details if you still have a problem.

Collectives™ on Stack Overflow

How can I create stored procedure from within Python script?

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related