Oracle - python cx_oracle - include "exec schema.get" before data extract

Question

I am newbie to oracle and I am trying to make a connection from python to an oracle table.

I can use Toad for oracle to access to the data of one table. I am running the same SQL command through python and I get the "DatabaseError: ORA-00942: table or view does not exist". I have search a lot and still do not know what the issue is:

I setup my connection:

import cx_Oracle
con = cx_Oracle.connect(<user>,<password>,<database name>)
print("Database version:", con.version)

Successful connection

I set my current schema

con.current_schema = 'schema_name'

and

cursor = con.cursor()
cursor.execute('select du.cfile_id from table_name du')

This throws an error of:

---------------------------------------------------------------------------
DatabaseError                             Traceback (most recent call last)
<ipython-input-195-f3f5aacf8431> in <module>()
----> 1 cursor.execute('select du.cfile_id from brwr_tau_to_du_log du')

DatabaseError: ORA-00942: table or view does not exist

Same query with same credential is working fine if I use "Toad for oracle". Note that I require to put "exec schema.PKG_DVS_ACCESS.get" before running my data extract on Toad. How should I include the command to my python code?

Thanks

Christopher Jones · Accepted Answer · 2018-07-31 23:15:27Z

1

To call PL/SQL procedures and packages in cx_Oracle use callproc:

cursor.callproc('myproc')

If the PL/SQL procedure has parameters, you will have to bind data values.

myvar = cur.var(int)
cur.callproc('myproc', (123, myvar))
print(myvar.getvalue())

Take a look at the cx_Oracle tutorial.

Update: A new blog post from Oracle talks more about using PL/SQL with cx_Oracle.

edited Jul 31, 2018 at 23:15

answered Jul 27, 2018 at 3:00

Christopher Jones

11.1k7 gold badges32 silver badges64 bronze badges

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

1 Comment

Hamid K Over a year ago

Thanks a lot for the help.

Collectives™ on Stack Overflow

Oracle - python cx_oracle - include "exec schema.get" before data extract

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related