I agree with the others and add that you shouldn't need/want to do this is in a well-designed database, but that's not going to make your requirement go away (nor the fact that you've been tasked with doing it), is it?

I'm not going to write it for you and check it for syntax, as that would be doing your job for you! Besides, I don't have a working copy of Oracle installed at the moment. But I can hopefully give you some pointers:

1. Temp tables must be created outside of procedures, with the CREATE GLOBAL TEMPORARY TABLE syntax. This means that the object will always be available to everyone, regardless of whether it is populated by your procedure. The semantics are the same, however, in that data inserted during one user session will not be available in another user session and it will clean itself up when the owning session disconnects (the exact point where the data is deleted is determinable by a clause in the CREATE statement).

2. QUOTENAME and PARSENAME don't exist as is, but equivalent functions for their typical uses can be found in the DBMS_ASSERT package.

3. Most dynamic SQL can be run with EXECUTE IMMEDIATE, i.e. EXECUTE IMMEDIATE 'SELECT * FROM blah', or EXECUTE IMMEDIATE v_sql. If you are going to loop round, executing a similar statement each time, consider looping to build up a CLOB full of PL/SQL (in a BEGIN/END; block) and EXECUTE IMMEDIATE-ing that whole block once.

4. INFORMATION_SCHEMA is supposedly ANSI-compliant, so should be available as is, in later versions of Oracle. If not, consider the USER_TABLES/USER_COLUMNS tables for objects in the currently logged on schema, or ALL_TABLES/ALL_COLUMNS for all schemas (subject to granted permissions).

5. If you need to get this data back out (e.g. to a .NET IDataReader), alter your procedure definition to include an OUT SYS_REFCURSOR parameter, then open that cursor with your resultset, e.g. OPEN results FOR SELECT * FROM the_temp_table. Note that, depending on when you've configured the temp table to clean up, the data may no longer be available to a forward-only reader like IDataReader.

Hope this helps.

J.