Oracle 10g: no temporary tables in stored procedures

SQL Server and MySQL both allow the creation of temporary tables
in stored procedures. How is Oracle better by disallowing this? Does
this somehow make an Oracle environment more secure or better
performing than a SQL server or MySQL environment?

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?

pcgamer4life said:

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?

Click to expand...

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.

Use WITH. Creating a table that's only going to last a single query is silly.

petteyg359 said:

Use WITH. Creating a table that's only going to last a single query is silly.

Click to expand...

Oracle definitely doesn't mind re-writing the SQL standards. However,
there are apparently performance drawbacks to using WITH clauses --
aside from the fact it's cryptic:

http://www.dba-oracle.com/t_sql_rewrite_complex_queries_with_clause_temporary_tables.htm

The defined global temporary table isn't used to query
just one or even just two tables, it's used to query
sixteen different tables that have a one-to-many
relationship w/the parent table.

And just how many consistent gets were involved in creating those three tables? Notice how he leaves that bit out? Burleson says lots of things. Other repected sites (including Ask Tom) frequently contradict him. Take his advice with a "trust but verify" mindset.

If you're actually going to be using this aggregate data set a lot, rather than a few one-off queries, then you probably want to look at materialized views (and materialized view logs).