Robot Framework Database Library calling Oracle store procedure fails with character to number conversion error

Libraries DatabaseLibrary
1

I have an Oracle PL/SQL procedure that I can directly call as follows, without problem:

BEGIN example_package_name.example_procedure(p_item_no => 123456, p_send_now => true); END;

(Note: p_item_no expects a NUMBER and p_send_now expects a BOOLEAN)

I am attempting to run this from within my Robot Framework test automation framework as shown below.

First I have a small helper wrapper method for robotframework-databaselibrary:

Execute SQL Store Procedure
  [Arguments]
  ...  ${target_database}
  ...  ${target_store_procedure}
  ...  ${store_procedure_arguments}
  ...  ${timeout}=5 minutes
  [Documentation]  Small wrapper around DatabaseLibrary for the: Call Stored Procedure keyword.
  [Timeout]  ${timeout}

  __Open connection to target database  ${target_database}
  DatabaseLibrary.Set Auto Commit  autoCommit=${True}
  DatabaseLibrary.Call Stored Procedure  ${target_store_procedure}  ${store_procedure_arguments}

  Close connection from the current database

Next from my test I am attempting something as follows:

${item_no_int}=  Convert To Integer  ${test_item_dictionary.item_no}
${example_procedure_argument_list}=  Create List  p_item_no => ${item_no_int}  p_send_now => ${True}
Execute SQL Store Procedure  target_database=test_db_name  target_store_procedure=example_package_name.example_procedure  store_procedure_arguments=${example_procedure_argument_list}

My error reads:

[info (+0.10s)] Executing : Call Stored Procedure | example_package_name.example_procedure | [‘p_item_no => 123456’, ‘p_send_now => True’]

[FAIL] DatabaseError:ORA-06502: PL/SQL: numeric or value error: character to number conversion error ORA-06512: at line 1 Help: ORA-06502 - Database Error Messages

Naturally I have been trying to ensure that my data is of the correct type when leaving Robot Framework, when reading the documentation on the Robot Framework: DatabaseLibrary.Call Stored Procedure keyword, I see:

def call_stored_procedure(
    self, spName: str, spParams: Optional[List[str]] = None, sansTran: bool = False, alias: Optional[str] = None
):

With description:

Calls a stored procedure spName with the spParams - a list of
parameters the procedure requires.

Is it be possible that the Keyword DatabaseLibrary.Call Stored Procedure / spParams: Optional[List[str]] does not end up preserving one’s intended data types? Or is something else perhaps missing on my part?

I am running:

  • robotframework>=7.0.0
  • robotframework-databaselibrary>=1.4.3
  • oracledb>=2.1.0
2

So I have had no luck with Robot Framework, instead I had to write a Python solution as follows:

from robot.api.deco import keyword
import oracledb


@keyword("Execute Stored Procedure")
def execute_stored_procedure(connection_details, procedure_name, arguments_dictionary):
    """
    Executes a stored procedure on an Oracle database using the provided connection details,
    procedure name, and arguments dictionary.

    Args:
        connection_details (dict): A dictionary containing connection details for the Oracle database.
            Requires the following keys: 'dbUser', 'dbPassword', 'dbHost', 'dbPort', 'dbServiceName'.
        procedure_name (str): The name of the stored procedure to execute.
        arguments_dictionary (dict): A dictionary containing the arguments to pass to the stored procedure.

    Returns:
        None: This function does not return any value.

    Raises:
        oracledb.Error: If any error occurs during the execution of the stored procedure,
            an oracledb.Error is raised.

    Example:
        | ${connection_details}= | Create Dictionary | dbUser | my_user | dbPassword | my_password | dbHost | localhost | dbPort | 1521 | dbServiceName | my_service |
        | ${arguments}= | Create Dictionary | arg1 | value1 | arg2 | value2 |
        | Execute Stored Procedure | ${connection_details} | my_stored_procedure | ${arguments} |
    """
    # Construct the connection string
    connection_string = f"{connection_details['dbUser']}/{connection_details['dbPassword']}@{connection_details['dbHost']}:{connection_details['dbPort']}/{connection_details['dbServiceName']}"

    # Connect to the Oracle database
    connection = oracledb.connect(connection_string)

    try:
        # Create a cursor
        cursor = connection.cursor()

        # Call the stored procedure with the provided name and arguments
        cursor.callproc(procedure_name, keywordParameters=arguments_dictionary)

        # Commit the transaction
        connection.commit()

        # Close the cursor
        cursor.close()

    except oracledb.Error as error:
        print("Error occurred:", error)
        # Rollback the transaction in case of error
        connection.rollback()

    finally:
        # Close the connection
        connection.close()