def call_function(connection: cx_Oracle.Connection,
name: str,
return_type: Type[T],
*,
args: List[Any] = None,
kwargs: Dict[str, Any] = None) -> T:
if kwargs is None:
kwargs = {}
if args is None:
args = []
with connection.cursor() as cursor:
if issubclass(return_type, objects.Base):
r_type = connection.gettype(return_type.__type_name__)
else:
r_type = return_type
args_ = [
v.to_oracle_object(connection)
if isinstance(v, objects.Base) else v for v in args
]
kwargs_ = {
k: (v.to_oracle_object(connection)
if isinstance(v, objects.Base) else v)
for k, v in kwargs.items()
}
obj = cursor.callfunc(name, r_type, args_, kwargs_)
if issubclass(return_type, objects.Base):
return return_type.from_oracle_object(obj)
else:
return obj
async def cash_transactions_sp(
request: models.TransactionRequest,
my_conn: cx_Oracle.Connection = Depends(get_raw_connection)):
print(request)
with my_conn.cursor() as cursor:
collection_type = my_conn.gettype("ott_cashTransactionsList")
request4 = {
'P_FORMAT': request.format,
'P_COMPANY': request.company,
'P_SOURCE': request.source,
'P_COUNTRY': request.country,
'P_ACCOUNT_ID': request.accountId,
'P_DATE_FROM': request.dateFrom,
'P_DATE_TO': request.dateTo,
'P_TRACE_ID': "eyanez",
'P_ITEMS_PER_PAGE': request.itemsPerPage,
'P_PAGE_NUMBER': request.pageNumber,
'P_TOTAL_PAGES': cursor.var(int),
'P_TOTAL_RECORDS': cursor.var(int),
'P_RESPONSE_CODE': cursor.var(int),
'P_MESSAGE': cursor.var(str),
'PTT_CASHTRANSACTIONS': cursor.var(collection_type)
}
pl_sql4 = f"begin TRAINING_PACKAGE.PRO_GET_CASH_TRANSACTIONS({declare_parameters(request4)}); end;"
cursor.execute(pl_sql4, request4)
my_collection = request4['PTT_CASHTRANSACTIONS'].getvalue().aslist()
print(f"PTT_CASHTRANSACTIONS length: {len(my_collection)}")
# Fetch data using reflection (using the metadata from the DB):
# No impact for changes in the contract
response_collection = []
for elem in my_collection:
my_dict = dict()
for the_attrib in elem.type.attributes:
my_dict[the_attrib.name] = getattr(elem, the_attrib.name)
response_collection.append(my_dict)
response = {
'P_TOTAL_PAGES': request4['P_TOTAL_PAGES'].getvalue(),
'P_TOTAL_RECORDS': request4['P_TOTAL_RECORDS'].getvalue(),
'P_RESPONSE_CODE': request4['P_RESPONSE_CODE'].getvalue(),
'P_MESSAGE': request4['P_MESSAGE'].getvalue(),
'PTT_CASHTRANSACTIONS': response_collection
}
return response
def _dbms_describe_describe_procedure(connection: oracle.Connection,
name: str) -> Iterator[Tuple[Any, ...]]:
"""Wrapper for Oracle DBMS_DESCRIBE.DESCRIBE_PROCEDURE procedure"""
number_table, varchar2_table = (
connection.gettype("SYS.DBMS_DESCRIBE.NUMBER_TABLE"),
connection.gettype("SYS.DBMS_DESCRIBE.VARCHAR2_TABLE"),
)
with connection.cursor() as cursor:
overload = number_table.newobject()
position = number_table.newobject()
level = number_table.newobject()
argument_name = varchar2_table.newobject()
datatype = number_table.newobject()
default_value = number_table.newobject()
in_out = number_table.newobject()
length = number_table.newobject()
precision = number_table.newobject()
scale = number_table.newobject()
radix = number_table.newobject()
spare = number_table.newobject()
try:
cursor.callproc(
"dbms_describe.describe_procedure",
[
name,
None,
None,
overload,
position,
level,
argument_name,
datatype,
default_value,
in_out,
length,
precision,
scale,
radix,
spare,
],
)
except oracle.DatabaseError as err:
if err.args[0].code == 20001:
raise NotFound
raise
return zip(
overload.aslist(),
position.aslist(),
level.aslist(),
argument_name.aslist(),
datatype.aslist(),
map(bool, default_value.aslist()),
map(Direction, in_out.aslist()),
length.aslist(),
precision.aslist(),
scale.aslist(),
radix.aslist(),
)
