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(), )