Most of my recent experience is with MS SQL Server. I haven't worked much with Oracle since 7.3. With that said...
In our MS SQL procs, we commonly create temporary tables (identified by preceding the names with a # sign), insert data into those tables via traditional SQL, perform DML on that data, and then pull the data back out before dropping the temporary table at the bottom of the proc. I cannot seem to find anyway to do this within PL/SQL without creating some sort of persistent object outside the procedure.
More specifically, here is what I am trying to do...
I have a massive SELECT statement that I am using to populate a REF CURSOR to return to an ADO result set for a web-based report. This SELECT statement is currently 2700 lines. It was originally designed with two input parms to get the data for a specific object. I want to give the user the option of SELECTing for a specific object or a set of objects. In MS SQL I would traditionally create a temporary table of IDs. I would insert the desired IDs into this table. if the user wanted data for a single object, I would insert one ID. If they wanted it for a set of objects, I would enter several IDs corresponding to the set. Then, in the bohemoth SELECT statement, I would add a JOIN to the temporary table linking on these IDs in place of binding static variables directly.
This provides me with tremendous flexibility in my selection criteria. Is there anyway to do this with Oracle PL/SQL considering I am using ver 8.1.6?
Oracle has something called a "global temporary" table. It is different to the SQL Server temporary table, but fulfils the same purpose.
You create a global temporary table ONCE, just like a normal table. The difference is that the data is local to each session, and is automatically deleted either when you commit or when your session ends - depending on an option when you created the table.
The syntax is:
CREATE GLOBAL TEMPORARY TABLE table( ....... ) [ON COMMIT PRESERVE ROWS];
If you omit the ON COMMIT PRESERVE ROWS part, then data is deleted when the user COMMITs; otherwise, it persists until their session ends.
You do not CREATE or DROP the table from within the application - it is a permanent table liek any other; only the DATA is temporary (and private to the session).