Reputation: 197
I am trying to connect to oracle database using cx_Oracle in python. I am able to connect to the database and pull the data.
Now I am trying to connect to one more database where I have to call a security procedure first and then only I can see data in the underlying tables. Can you please help me in calling the procedure by adjusting the below block of code. My procedure will be like: exec ef.applogin('1234')
def connect_oracle():
import cx_Oracle
import pandas as pd
ip = 'some_ip'
port = 1521
SID = 'some_SID'
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect('user_name', 'password', dsn_tns)
#Procedure to be executed here..
query = """ SELECT * from table_name """
df_ora = pd.read_sql(query, con=connection)
return df_ora
connect_oracle()
Many thanks in advance!!
Upvotes: 1
Views: 1995
Reputation: 665
Create def for connect by Oracle. for execute procedure use cursor. Don't forget close connect and cursor. Also in case exception.
import cx_Oracle
import pandas as pd
ip = 'some_ip'
port = 1521
SID = 'some_SID'
user_name = 'user_name'
password = 'password'
query = """ SELECT * from table_name """
def connect_oracle(ip, port, SID, user_name, password):
dsn_tns = cx_Oracle.makedsn(ip, port, SID)
connection = cx_Oracle.connect(user_name, password, dsn_tns)
return connection
def get_df(conn, query):
df_ora = pd.read_sql(query, con=conn)
return df_ora
def execute_proc(conn, procname, param):
my_cursor=conn.cursor()
my_cursor.callproc(procname, param)
conn.commit()
def execute_func(conn, procname, param):
my_cursor=conn.cursor()
out_parameter = my_cursor.var(cx_Oracle.NUMBER)
qw = my_cursor.callfunc(procname, out_parameter)
return qw
conn1 = connect_oracle(ip, port, SID, user_name, password)
df = get_df(conn1, query)
df.head()
execute_proc(conn1, "insert_temp", [])
execute_func(conn1, "get_version", [])
Function for test:
create or replace function get_version
return number
is
v_version NUMBER(8) := 11;
begin
return v_version;
end;
Procedure for test:
create table temp (clm number);
create or replace procedure insert_temp
is
v_version NUMBER(8) := 11;
begin
insert into temp(clm) values(v_version);
end;
select * from temp
Upvotes: 1