Skies of Arcadia: Legends for the GameCube. Final Fantasy style RPG with AIR PIRATES. If that's not enough for you, it's one of the most fun RPGs I've ever played. It's generally pretty easy with a few notable exceptions, but a very fun game all around. I highly recommend it. Oh, and it's cartoony.
If you've never played Neverwinter Nights, that's high on my list, too. They recently released the Platinum version which includes both the Shadows of Undrentide and Hordes of the Underdark expansions. It's a D&D style RPG (it even shows your hit rolls) in a medieval setting. Very compelling game with lots of replayability - you can develop your character nearly any way you want. I think this was only for PC, though I could be wrong.
Another of my favorites... Star Wars: Knights of the Old Republic. Developed by the same guys who did NWN, it's also a D&D style RPG, except this time it's set in (obviously) the Star Wars universe. Even if you don't particularly like Star Wars, you should enjoy this game if you like RPGs at all. If you
do like Star Wars and you haven't played this game, feel free to hang yourself right now (or at the very least play it as fast as you possibly can). The sequel was, in my opinion, superior as far as gameplay goes (lots of little tweaks made it much more enjoyable to play), but inferior in terms of story and character development. They're both excellent games, though. I believe they were both released for X-Box and PC.
Those are all single player RPGs, but they all have compelling stories that should keep you hooked for a while. The only one of those that I haven't played through multiple times is Skies of Arcadia, and I actually might do that again soon. That's how good they are.
Any of the Final Fantasy games you can get your hands on should fit the bill nicely. They're fun to play, pretty hard in parts, and insanely long. Any one should hold you attention pretty well, though my favorite is FF6. You might be able to find an emulator and ROM, though don't quote me on that
Morrowind. A lot of people have issues with the graphics and controls of this game (and with good reason... the character skins are fugly, and the controls kinda suck), but if you can get past that it's a pretty amazing game. A first person RPG that's so large and diverse it nearly feels like an MMO. Many, many sidequests to do, lots of character customization. You can't do everything in the game in one play, so it has some good replayability. And it might get you hooked on the series and psyched for the upcoming ELDER SCROLLS IV: OBLIVION, BABY! (Sorry about that. I'm just so fucking excited for this game. It looks absolutely amazing.) You can get Morrowind for X-Box or PC.
If you haven't yet played it, get Zelda: Wind Waker. It's not quite an RPG, but sort of an action puzzle RPG hybrid. Lots of people complained that it looked "kiddy," but I think the graphics are simply genius. They are colorful, clean, crisp (whoa... check that alliteration

), and executed very well. The gameplay follows the typical post-Ocarina Zelda style, which is just fine by me. The puzzles are mostly pretty easy, but all around it's an excellent game. It's in a close second for my favorite Zelda game (right behind Ocarina). This is, obviously, only for Gamecube.
Phew. I think I've exhausted my recommendation muscle for a little while. I hope you find something you haven't played yet!