What is a blockchain oracle for smart contracts? And why is it needed? I've seen a lot of posts about "how do oracles work", and "what is the oracle problem" but I don't understand what they are?
A blockchain oracle is any device or entity that connects a deterministic blockchain with off-chain data.
Smart contracts cannot make API calls themselves because they are deterministic, but without being able to interact with data the lives off-chain, they won't be able to utilize the decentrality, security, and reliability a smart contract has. To get data (that powers all our current modern world, ie any API that runs your applications) onto the chain, we have to have an off-chain entity create a transaction on-chain with the data posted.
This process of posting data from the real world onto a blockchain is what oracles do.
There are a number of ways to do this. An example of an oracle would be a Chainlink node, where you can request any API call through the blockchain, and have it post the data on-chain.