SQL & PL/SQL

I have written a stored procedure wich scans through a table, row by row, then inspecting each row column by column. (A lengthy business.)

The outer loop picks up the rowid so I can use it in the inner loop. The inner loop looks something like this:

FOR nIndx IN 1 .. LastCol LOOP
EXECUTE IMMEDIATE 'select vsize( :ColName ) FROM MyTable WHERE rowid = :TheRowid'
INTO nSizeHolder
USING IN szColName, IN MyRowId;
.
. (Now I process szColName
.
END LOOP;

(szColName is, in fact, the contents of a PL/SQL table, referenced by nIndx.) As stated, if the column name is 'SPTYPE' (for example), the dynamic query will always return 6 (i.e. LENGTH( 'SPTYPE' ). If I change the EXECUTE IMMEDIATE to:

'select vsize ( ' || szColName || ' ) FROM ...'

it correctly gives a varying value, depending on the contents of the column SPTYPE. (I'm using SPTYPE as an example: it happens on all columns.)

So, while I can get it to work, failing to use the bind variables properly will hammer the Shared Pool.

I re-cast the code in DBMS_SQL equivalent and had exactly the same problem. (This is more drastic: DBMS_SQL could be more efficient because I could do most of the dynamic work once per row, outside the inner query; however, I can't because of this bind problem.)

I must be missing a trick somewhere. (The asnwer will probably be RTFM!) Anyone any ideas?