StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>What is the business problem you are trying to solve? It is exceptionally rare that you need to use temporary tables in Oracle. Why wouldn't you simply </p> <pre><code>SELECT * FROM employees WHERE id = p_id_passed_in; </code></pre> <p>In other databases, you often create temporary tables because readers block writers so you want to create a separate copy of the data in order to avoid blocking any other sessions. In Oracle, however, readers never block writers, so there is generally no need to save off a separate copy of the data.</p> <p>In other databases, you create temporary tables because you don't want to do dirty reads. Oracle, however, does not allow dirty reads. Multi-version read consistency means that Oracle will always show you the data as it existed when the query was started (or when the transaction started if you've set a transaction isolation level of serializable). So there is no need to create a temporary table to avoid dirty reads.</p> <p>If you <em>really</em> wanted to use temporary tables in Oracle, you would not create the table dynamically. You would create a global temporary table before you created the stored procedure. The table structure would be visible to all sessions but the data would be visible only to the session that inserted it. You would populate the temporary table in the procedure and then query the table. Something like</p> <pre><code>CREATE GLOBAL TEMPORARY TABLE temp_emp ( empno number, ename varchar2(10), job varchar2(9), mgr number, sal number(7,2) ) ON COMMIT PRESERVE ROWS; CREATE OR REPLACE PROCEDURE populate_temp_emp AS BEGIN INSERT INTO temp_emp( empno, ename, job, mgr, sal ) SELECT empno, ename, job, mgr, sal FROM emp; END; / SQL> begin 2 populate_temp_emp; 3 end; 4 / PL/SQL procedure successfully completed. SQL> select * 2 from temp_emp; EMPNO ENAME JOB MGR SAL ---------- ---------- --------- ---------- ---------- 7623 PAV Dev 7369 smith CLERK 7902 800 7499 ALLEN SALESMAN 7698 1600 7521 WARD SALESMAN 7698 1250 7566 JONES MANAGER 7839 2975 7654 MARTIN SALESMAN 7698 1250 7698 BLAKE MANAGER 7839 2850 7782 CLARK MANAGER 7839 2450 7788 SCOTT ANALYST 7566 3000 7839 KING PRESIDENT 5000 7844 TURNER SALESMAN 7698 1500 7876 ADAMS CLERK 7788 1110 7900 SM0 CLERK 7698 950 7902 FORD ANALYST 7566 3000 7934 MILLER CLERK 7782 1300 1234 BAR 16 rows selected. </code></pre> <p>As I said, though, it would be very unusual in Oracle to actually want to use a temporary table.</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload