SQL & PL/SQL

I'm new to PL/SQL.

I've got a complex SQL statement that I'd like to access as a result set and store to a temporary table, but not in the same invocation.

I can't seem to make it into a view because it's constructed using a WITH clause and my CREATE VIEW statement seems to balk. Have I got the syntax wrong or do views not permit With clauses?

Even if I resolve this, I need a "parameterized view" because it's a hierarchical (CONNECT BY) query that needs to different START WITHs. My understanding is that I can parameterize the view by getting the value using SYS_CONTEXT, but I'll have lock the variable, this providing sequential access to this view, which seems like a bad idea.

So, I've ended up with the stored proc route--to pass the parameters cleanly and handle the With clause well. Since it's not a view, if I want to join results, I have to save it to a table. Other times, I'd just like to get the result set back directly.

I've considered two SPs, one that returns the result set and one that writes it to a table, but I can't figure out a way to centralized the SQL statement (the guts of the proc). So I decided to stuff it into the same proc and pass a flag to tell the desired output. It looks like:

if Result Set:
Open O_RESULT_SET for
With ...
Select ...

if Temp Table:
INSERT INTO TempTable
With...
Select...

Even in the same proc, I can't figure out how to maintain only 1 copy of the "With...Select..." I understand PL/SQL doesn't support macro substitution and doesn't appear to have include files. Is this right?

I can either write it to the temp table and read it back (wasteful if the temp table actually gets written to disk). Or I can do the reverse, open the cursor, and write it to disk if needed. Is there a FAST way to write the contents of a cursor to a table? Seems like FORALL is the fastest way. Is that right? Or does this apply to a TABLE variable which is different from a CURSOR?

Any comments would be helpful, but I guess I'm first looking for a way to maintain the SQL in one place and second the fastest way to read/write the results if I do combine both functions in one.

Thanks much.