Create Temp table in Oracle

In Oracle, you can create temporary tables using the CREATE GLOBAL TEMPORARY TABLE statement. Temporary tables are session-specific, meaning they are only visible to the session that creates them, and their data is automatically deleted at the end of the session or when the transaction is committed or rolled back.

Here's the syntax for creating a temporary table in Oracle:

CREATE GLOBAL TEMPORARY TABLE table_name (

    column1 datatype1,

    column2 datatype2,

    ...

)

{ON COMMIT {PRESERVE ROWS | DELETE ROWS}};

- table_name: This is the name of the temporary table you want to create.

- column1, column2, etc.: These are the columns of the temporary table.

- datatype1, datatype2, etc.: These are the data types of the columns.

- ON COMMIT: This clause specifies what happens to the data in the temporary table at the end of the transaction. PRESERVE ROWS retains the data until the end of the session, while DELETE ROWS deletes the data at the end of the transaction.

Here's an example of creating a temporary table in Oracle:

CREATE GLOBAL TEMPORARY TABLE temp_employee (

    emp_id NUMBER,

    emp_name VARCHAR2(100),

    emp_salary NUMBER

)

ON COMMIT DELETE ROWS;

In this example:

- We create a temporary table named temp_employee with three columns: emp_id, emp_name, and emp_salary.

- The data type of emp_id and emp_salary is NUMBER, and the data type of emp_name is VARCHAR2.

- We specify ON COMMIT DELETE ROWS, meaning that the data in the temporary table will be deleted at the end of the transaction.

After creating the temporary table, you can use it like a regular table within your session. However, keep in mind that the data in the temporary table is only visible within the session that created it, and it will be automatically deleted when the session ends or the transaction is committed or rolled back.