What is an example of security exploitation following the activation of the Ole Automation Procedures server configuration option?

The CIS controls for SQL Server 2008 and 2016 use this formulation

The Ole Automation Procedures option controls whether OLE Automation
objects can be instantiated in Transact-SQL batches. These are
Extended stored procedures that allow SQL Server users to run
functions external to SQL Server.

Rationale: Enabling this option will
increase the attack surface of SQL Server and allow users to run it
works in the security context of SQL Server.

The risk is that the object you are calling from your stored procedure does not do what you think it does. As far as I know, you can not sign Ole Automation objects or provide any guarantee that the actions they undertake are the ones that were planned.

To rephrase it, the ole object is an unreliable source that your stored procedure executes with the permissions that are granted to it. Mitigation would consist of only granting the minimum permissions to the user calling the procedure and setting the context of use when you create it to 4, described as follows:

a local OLE server does not have access to any SQL Server resources,
and it can not corrupt the memory or resources of SQL Server.

See here for more details and here for an example. What the example will not show you, is how it can go wrong. It might be better to rephrase your question to "What can happen if the data returned is not correct?"

In addition, SQL Server 2008 R2 is no longer supported in July 2019. An upgrade may need to be considered.