OK, you can have tab_name as user_tables.table_name%TYPE if you prefer. It doesn't make a lot of difference to the functioning of the procedure.

I don't understand what you mean by "returns the result from a query". The procedure I wrote is returning a result set in the form of an open cursor, which can then be manipulated by the calling program. What exactly do you want to be able to do ?