Both EXEC[ute] SP()
and CALL SP()
could be used in SQL*Plus to execute an SP. BTW, you can also use BEGIN SP(); END;
But there are some differences.
-
CALL
is Oracle SQL and should work everywhere. Other DB clients that can talk to Oracle may or may not support SQL*Plus EXEC. Many do (for example, Oracle SQL Developer, SQLWorkbench/J), but some don't (Liquibase). -
The data types of the parameters passed by the
CALL
statement must be SQL data types. They cannot be PL/SQL-only data types such as BOOLEAN. -
EXEC
could be used to execute not only an SP, but an arbitrary statement. -
If an SP does not have parameters, you can use
EXEC SP;
syntax, butCALL
requires empty parentheses:CALL SP();
python:
def get_checked_data(param1, param2, param3):
from sqlalchemy import create_engine
import pandas as pd
import logging
engine = create_engine("oracle://user:pass@ip:port/?service_name=?", echo=True)
logging.info('Run procedure and get data... ...')
connection = engine.raw_connection()
try:
cursor = connection.cursor()
cursor.callproc("procedure_name", [param1, param2])
sql = "select * from table_name where username=:param3"
df = pd.read_sql_query(sql, engine, params=[param3])
cursor.close()
connection.commit()
return df.to_dict(orient='records')
except Exception as e:
logging.error('Get checked data for bov error:' + str(e.message))
logging.error(traceback.format_exc())
finally:
connection.close()