In general, stored procedures are usually reserved for DML rather than DDL. The assumption is that all DDL is done much less frequently than DML, so there's no need for DDL in a stored procedure. Why not just create your temporary table outside of your procedure, than then use your sp to populate the table? Or better yet, maybe just use a subselect inside your sp for the data that you're interested in, rather than putting it into a temporary table?
The data being selected is sometimes located in sub-tables depending
on a field/column in the main table, but is usually null.
If I do something like SELECT a.field1, a.field2, b.field1 FROM
SELECT(tbl1.dta1, tbl1.dta2 FROM tbl1 WHERE dta3 = something) a,
SELECT(tbl2.dta1 FROM tbl2 WHERE dta2 = something) b;
The problem is the b.field1 value can come from multiple tables in the DB, not
just one.
I was planning on doing separate selects for each of the sub-tables
and throwing it all into a temp table then having the cursor select its
data from that. In MySQL and SQL Server 2000 that isn't a problem,
but I don't know what would happen if I created a temporary table
outside of a stored procedure in oracle, because I would have to
delete the contents every time I invoked the stored procedure and
I don't know what kind of ramifications that would have for concurrency.