(skipping oddities like JBI's Harvard Architecture)
They're only oddities in the context of general desktop/server computing. PIC microcontrollers are ubiquitous in embedded control applications; there's a good chance you own dozens of them without realizing it, in your home appliances and car. Wouldn't surprise me if they outnumber "normal" CPUs 10 to 1.
People talk about self-modifying code where you treat the instructions as data, overwrite them and then execute the new data/instructions but it is very rare. More common are compressed executables, a small block of code at the start and a large block of compressed data. The code at the start uncompresses the data in to the original program and then jumps to it.
One particularly clever use of self-modifying code was back in the MS-DOS days, when some x86 processors had a FPU (x87) and some did not. The Microsoft C compiler would generate FPU instructions for floating point math, and pad them with enough trailing NOPs such that there was room to patch a subroutine call in their place if needed. At runtime, executing an x87 instruction on a CPU without a FPU would trigger an exception (software interrupt); the exception handler (also compiled into the binary) would patch the code on the fly to replace the offending x87 instruction with a call to a software emulation routine, then resume execution at the point where the exception occurred.
This way you could have a single binary that could run on x86 processors lacking a FPU, yet still take advantage of the FPU if it was present. The in-place patching meant you only took the overhead hit of running the exception handler once for each FPU instruction on each run of the program; after the first time through, the x87 instruction was emulated with a simple subroutine call. On CPUs with a FPU, the only additional overhead was a couple of extraneous NOPs after each FPU instruction, and the extra memory occupied by the (unused) emulation code. (If you knew you were only going to run on CPUs with a FPU, you could set a compiler option to turn all this stuff off...)
I remember thinking that this was really cool (in a sick sort of way) when I figured out what was going on.