Originally posted by oceanju
the global temporary table is visible to all the sessions. I want to create temporary table in the package function and auto dropped when the session end.I want the temporary table created in a session and not visible to a another session.
In sql server, I can create local temporay table with # name and create global temporary table with ## name.
How to do in the oracle?
Your #temp tables in sqlserver are equivalent to global temporary tables in oracle..The only difference being that you create the global temporary table once and for all.The data remains for either the duration of the session or for the duration of the time the user stays logged in.In the former case you create the table with on commit delete rows option and in the later case you use on commit preserve rows options..

so to sum up

sqlserver temp#=global temporary tables in oracle

create them once and not every time in a proceedure..

If you are migrating from sqlserver to oracle instead of #temp tables
being replaced by Global temporary tables consider using inline views..

regards
Hrishy