I got two levels in Pickpocket, and I have no idea how it's supposed to work on successful use.

I went up to Joram, used the ability on him and there's a sound effect of what should be me going through his pockets, but nothing happens.

Nothing is added to my inventory (my Gold and Weight values don't change at all) and I don't get any angry reactions from Joram, nor does his disposition towards me go any lower, so the attempt didn't fail. I'm just not getting any items.

What exactly is supposed to happen?

You need to select pickpocket skill in the window where you select magic spells and then right click on a character.
If you pass the level requirements needed to pickpocket the character his/her inventory window should open and you can drag and drop stuff into your own inventory.

If you don't pass the level requirements their disposition toward you will lower.
Maybe Joram, being the 1st character you encounter, is imune to pickpocketing so try someone else.

Like I said, there's no negative reaction from him and his disposition does not lower.

I get a sound effect of what seems like a rucksack being handled, so for all intents and purposes the pickpocketing attempt succeeded. But there's no inventory window opening. I tried on other NPCs and nothing happened as well.

Looks like it's bugged. Unfortunately I'm rather far into this playthrough and I'm not gonna restart for one or two skill points.