Yes, Potatotron is correct, the processor can execute code from RAM.
In fact, from normal Arduino usage, you can add "FASTRUN" in front of a function and it will be loaded in RAM, which (sometimes) runs faster than having the same code in flash.
To load code from media like a SD card, you'd need to go to quite a lot of trouble to create that code, since the normal build process creates code for the flash. You'd need to edit the linker file, so the compiler creates the code for the RAM address, rather than flash. You code will presumably need both static and stack based variables. You could create your linker script to just put your static variables right after your code, in the same block of RAM. You could even simplify the startup routines at the beginning of your code, since it wouldn't need to copy static initialization from flash to ram. Presumably, you'd use the already-established stack, set up by the code running from flash.
You'll probably need to edit the normal linker script too, to make a big chunk of the RAM with a fixed starting address available. Then you could have the flash-based code simply copy the binary data from a file on the SD card to the big RAM buffer.
Actually calling the RAM-based code from the flash-based code requires some tricky C syntax. Here's an example from eeprom.c, which calls a function at the address "do_flash_cmd" and passes a pointer to it.
Code:
// do_flash_cmd() must execute from RAM. Luckily the C syntax is simple...
(*((void (*)(volatile uint8_t *))((uint32_t)do_flash_cmd | 1)))(&FTFL_FSTAT);
Those are the easy parts.
Achieving any sort of integration between the RAM-based code and flash based code will make the arcane syntax of linker scripts and C function pointer type casting look like a walk in the park!
Using interrupts from the RAM-based code will be the first huge challenge. Probably the simplest way would be to just create a new vector table in the RAM image and use it.
But if you want to actually use any of the code in flash, you're going to have to figure out some way for your RAM-based program to know where the flash-based code allocated stuff in the _other_ RAM that's not dedicated to your program. I can think of some slow schemes, like creating tables of pointers, where the linker script places the table at a fixed location. But that's slow and wasteful. How to achieve any sort of good integration, where the RAM-based code can simply use the resource from the flash-based code is beyond my technical skills.
