SQL & PL/SQL

Hi,

I'm joining a project, but I am too novice on PL/SQL. One particular Procedure does a search query. The approach can be summarized as:

* Build a SQL string base on values passed in the list of parameters
* Truncate a permanent table named Temp_SearchID
* Execute the dynamic SQL string which will populate the Temp_SearchID table (INSERT INTO Temp_SearchID SELECT ...)
* Populate the returned cursor by a SELECT joining other tables with the IDs in Temp_SearchID

{color:#ff0000}*Q1.*{color} If the application load increases, there will be a higher concurrency and let assume that there are several simultaneous calls to the procedure above. What would happen as all these calls has different parameters and they all want to compete to use the Temp_SearchID table their own way?

*{color:#ff0000}Q2.{color}* If the approach using a permanent Temp_SearchID table is not recommended, it is possible to dump the IDs in a cursor which is local only to the procedure. Then make a SELECT joining with this cursor? In the SQL Server world, this is possible using Table variable or session local temp tables. If it is possible to achieve this with PL/SQL, can you please show me the syntax or point me to some code snippets?

Thank you very much for any help.