How to i read the contents of Oracle stored procedures using Python

Question

I am trying to read the contents/code of a stored procedure using python.

i used cx_Oracle function to establish the connection with oracle database.

here is the code

import cx_Oracle as co
import pandas as pd

dsn_tsn = co.makedsn(ip,port,SID)
db=co.connect(username,password,dsn_tsn)
cursor = db.cursor()

cursor.callproc(procedure_name,['argument']) # will be me result of the procedure.

However, i am trying to read the code of procedure itself. Is there any function to do that ?

What do you mean by you want to read code? Why are you creating procedure in the first place? If you want to get a specific part of the procedure, I think you'll have to save it as a file somewhere and then read it. I do not think there is a way to read directly from the database. Check this: stackoverflow.com/questions/19472922/… — django-unchained
– django-unchained, Commented Mar 7, 2020 at 2:04

Barbaros Özhan · Accepted Answer · 2020-03-07 04:44:20Z

1

You can call DBMS_METADATA.GET_DDL function from your code in such a way

import cx_Oracle

db = cx_Oracle.connect("<uname>/<pwd>@<host>:<port>/<service_name>")
cursor = db.cursor()


    def OutputTypeHandler(cursor, name, defaultType, size, precision, scale):
        if defaultType == cx_Oracle.CLOB:
            return cursor.var(cx_Oracle.LONG_STRING, arraysize = cursor.arraysize)

    cursor.outputtypehandler = OutputTypeHandler
    cursor.execute("SELECT DBMS_METADATA.GET_DDL('PROCEDURE', :PrcName) FROM DUAL",
            PrcName="MY_LITTLE_PROC")

    print("returned DDL is :",cursor.fetchall())

answered Mar 7, 2020 at 4:44

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

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

Comments

Littlefoot · Accepted Answer · 2020-03-06 21:31:11Z

Code of stored procedures can be accessed via user_source view. So, if you query it, you'll see what you want. Here's how:

SQL> create or replace procedure p_test is
  2  begin
  3    null;
  4  end;
  5  /

Procedure created.

SQL> desc user_source
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NAME                                               VARCHAR2(30)
 TYPE                                               VARCHAR2(12)
 LINE                                               NUMBER
 TEXT                                               VARCHAR2(4000)

SQL> select text from user_source where name = 'P_TEST' and type = 'PROCEDURE';

TEXT
--------------------------------------------------------------------------------
procedure p_test is
begin
  null;
end;

SQL>

Though, as I don't speak Python, I can't assist in actual code you need to use there. The last select I wrote is what you need; I hope you'll manage to use it. Good luck!

Vasim · Accepted Answer · 2023-02-11 14:26:23Z

0

import oracledb

db = oracledb.connect("<uname>/<pwd>@<host>:<port>/<service_name>")
cursor = db.cursor()

cursor.execute("SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'PROC_NAME', 'OWNER') FROM DUAL")
c = cursor.fetchall()
print( c[0][0] )

# To save on file    
sample = open('samplefile.txt', 'w')
print(c[0][0], file = sample)
sample.close()

Change connection details, PROC_NAME and OWNER and run.

edited Feb 11, 2023 at 14:26

answered Feb 11, 2023 at 14:21

Vasim

3,1636 gold badges39 silver badges58 bronze badges

1 Comment

Vasim Over a year ago

I used print( c[0][0].read() ) and it worked.

Collectives™ on Stack Overflow

How to i read the contents of Oracle stored procedures using Python

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related