To create a temporary table in an Oracle stored procedure

DDL statements cannot be used directly in Oracle's stored procedures, such as CREATE, alter, DROP, truncate, and so on.

That assumes that we want to create a temporary table in a stored procedure that can only use dynamic SQL statements:

Create or replace procedure Pro as  str_sql varchar2 (+); Begin  -Creates a temporary table  str_sql: = ' Create global temporary TA Ble temp_table (       col1 varchar2 (Ten),       col2 number    ) on commit preserve rows ';  Execute immediate str_sql;  --Use temporary table  str_sql: = ' insert into temp_table (col1, col2) VALUES (' A ', ' 1 ') ';  Execute immediate str_sql;  --Delete temporary table  str_sql: = ' drop table temp_table ';  Execute immediate str_sql;end;

In Oracle. The temporary table is divided into two types: Session level and transaction level (transaction).

The session-level temporal table exists throughout the session until the session ends, and the transaction-level transient table data disappears after the transaction is finished. That is, Commit/rollback or ending the session,

The temporary table data is cleared.

On commit Preserve rows-session level temporary table (exit login ends session)

On commit Delete rows-transaction level temporary table (commit or rollback will end transaction)

Temporary table pros and Cons:

1. It is recommended to use cursors when querying data only.

2. The temporary table does not establish an index. Therefore, it is not recommended to assume a larger data volume or multiple queries.

To create a temporary table in an Oracle stored procedure