Using temporary tables in stored procedure. If you could send me a sample stored procedure so that I. A similar Update statement in Oracle. Numeric/Math Functions. NEXT: DELETE. Oracle / PLSQL: UPDATE Statement. This Oracle tutorial explains how to use the Oracle. Example - Update table with data.

Hi Tom, I am used to use MS SQL Server or Sybase to create stored procedures for reporting. There, it is very convenient to use temporary tables in the stored procedure to manipulate complex logic. However, in ORACLE, it seems difficult to use. Let say I want to 1) create temp_1 to store the results from 'Select col1, col2 from table1, table2 where.' 2) create temp_2 to store the results from another query 3) Join temp_1 and temp_2 with other tables to get the final results. The results are multiple rows.

The application software will call the stored procedure to get the results set. 4) temp_1 and temp_2 should be dropped automatically after running the stored procedure. Wincc V7.2 Rapidshare. How can I accomplish these requirement in a stored procedure? You will be highly appreciated if you could send me a sample stored procedure so that I can learn from it.

Karl Huang and we said. You should simply: open ref_cursor for select * from ( query you used to put into temp_1 ), ( query you used to put into temp_2 ) where join_conditions in your procedure. You'll find that Oracle is much better at complex queries involving dozens (yes more then 16) tables -- without any issues whatsoever. The temporary table crutch which ismandatory in SQLServer is something you need not use in Oracle -- we are very different -- Oracle and SQLServer are as different as you can get.

See for getting result sets from stored procedures. In this case, I would recommend you totally skip the temporary tables and their unneccesary IO and overhead and go right for the answer.

We support temporary tables, but in a different fashion, see: for a comparision. DO NOT dynamically create them, DO NOT dynamically create them, please -- do NOT dynamically create them. Followup to comment one below see for examples of ref cursors and their usage.

Anytime you can rewrite in a single query what you are currently doing procedurally or in many steps -- you will be the better for it. In the example above, they were catering to a database engine that did not perform optimization very well with lots of tables ( very common in that database, that is why temp tables are so overused ). In Oracle -- even with 'real temp tables' in 8i -- this would be the wrong way to approach this problem. In their case, using a ref cursor is easy, straight forward and cuts out the temp table all together.