Wanted to know the syntax for creating global temporary table with index to be created dynamically using a select statement. I want to use it in place of a CTE, which uses a WITH clause and creates a temporary table, but with out an index.
This forum is ONLY for Sql Developer questions.
Please mark the thread ANSWERED and repost it in the Sql and Pl/Sql forum
Oracle tables are NEVER created with indexes using a single statement.
Also Oracle is NOT sql server and you do NOT use, or need, temporary tables like you do with that other product.
If you need a temp table then add DDL to your version control system to create the table. Then check out the script and execute it to statically create the temp table that you need. The script should include any indexes you want.
All users/sessions use the SAME temp table but the data is kept separately. No session can see or use either committed or uncommitted data of another session that is in a temp table.