Loading from cassette was very, very common and the default option for most 8-bit micro owners. The early micros like the C64 were reasonably affordable, and the fact you could hook them up to a regular television set as a monitor and an ordinary cassette deck to load data added to that affordability. Floppy drives were quite expensive and could double the cost of owning a system.
Games took various different lengths of time to load from cassette depending on a number of factors, but each individual game always took the same length of time to load because the cassette ran at a specific speed. Games loaded from the cassette into the computer's RAM so, broadly speaking, the more memory the game took up when loaded, the longer it took to load.
In the very early days of all 8-bit micros, games tended to be quite simple in concept and graphically. Some were designed to fit into far less RAM than your computer had because many micros came with memory options (for example, the Commodore had 16K and 64K models, and the ZX Spectrum 16K and 48K models). Games designed for 16K naturally loaded faster than later, more complex titles that took full advantage of the enhanced machines.
Part of the tape-loading experience was a 'loading screen' - usually a graphical display that you could look at while the game loaded. This had to load and show on the screen before the main game started loading, so this added to the loading time. A few games even had loading music that played while the main game loaded. These things loaded in a sequence, so the loading screen/music would load first and be cleared from RAM by the time the main program had loaded.
As development on 8-bit micros progressed, programmers found ways to make games load faster by coding their own tape loading systems which could receive data faster than the system's in-built method. To make use of this though, the fast-loading system had to load itself first using the system method, and then the rest of the data on the tape would be in the faster format. These systems often had a timer on the screen which counted down so you could see how long the game had left to load. As this later era of software coincided with bigger titles, games could still take some considerable time to load.
Finally, it is worth mentioning 'multi-load' games. These were games that were too big to fit into the memory in one go. Often these were arcade-conversions which had not been designed with small micros in mind. These would usually load the game engine first, then you were asked to stop the tape. Each level of the game would load separately and you were prompted to stop and start the tape in-between levels.
I'd say that 5-10 minutes is probably a more realistic average loading time for a decent game at the height of the 8-bit micro era, but to a child that wait could seem a lot longer which may account for the exaggeration in the song you quoted.
Having games on cassette did make piracy quite easy, but it also kept production costs quite low so arguably there was less temptation to pirate them when you could buy them at an affordable price. Because there was no internet, piracy was limited to copying games from other people you knew which was far more limiting than file-sharing today. The time it would take for a game to copy tape-to-tape didn't put us off. I'd buy 90-minute blank cassettes which I would append newly copied games to. If a friend brought round a new game we'd load it, and then then I'd copy it while we played it.
The line from your song which says "if everything went alright" seems to refer to the fact that tape-loading could sometimes fail. Tapes could get damaged or just degrade over time. Poor quality cassette players could "eat" cassettes - causing the tape to come off the spool and get tangled up inside the deck. Cassette player heads could also be slightly misaligned, and users with persistent loading difficulties were often advised to adjust the azimuth alignment screw which could improve the tone of playback in a way that would be condusive to successful loading. Copied (pirated) cassettes could also be of poorer quality. Some twin-cassette decks available at the time had a feature called 'high-speed dubbing' that copied cassettes at double-speed. I found their games copied this way didn't always load so reliably, possibly because the process left a slight high-pitched whine on the dubbed copy which could interfere with loading.