Implementation of a custom temporary table in Oracle and its application in Oracle spatial

oracle| temporary table
Oracle temporary table feature Description:
Temporary tables in Oracle are global and need to be created at database design time, not when the program is used. Each login user uses the same temporary table, but does not see each other's data, which means that the temporary table is session independent.
Oracle's temporary tables are divided into transactional and conversational types.
A transactional temporary table means that records inserted during a transaction are valid only before the transaction commits or rolls back, and records in the table are automatically cleared once the transaction completes.
A session-type temporary table means that the data in a temporary table is valid for the duration of this session until the session ends. Data for this session is automatically emptied in the table after the session ends.

Deficiencies of the Oracle temp table:
1. LOB objects, such as Spatial Sdo_geometry, are not supported. This may be a design based on operational efficiency, but it's not possible to use temporary tables when you actually need this functionality in real-world applications.
2, the main foreign key relationship is not supported. This means that the temporary table

For these reasons, batting practice designed a custom temporary table approach to support Oracle spatial data types and primary foreign key relationships without concurrent conflicts.

1. Create a table structure for a temporary datasheet in the form of a regular table, but add a SessionID <NUMBER> column to the primary key of each table to differentiate between sessions. (You can have LOB columns and primary foreign keys)
2. Write a user logoff trigger to delete all the records inserted by this session (SessionID equals the record for this session ID) when the user ends the session.
3. When the program writes the data, the current session ID (SessionID) is written to the table.
4, when the program reads the data, only read the same record as the current session ID.

Feature-Enhanced extended design:
1, you can set up a view on the datasheet, the view of the record filter condition is the current session of the SessionID.
2, the SessionID column in the datasheet can be implemented through trigger to achieve the transparency of the application layer.
3. Advanced users can access global data to achieve more complex functionality.

Benefits of extending temporary tables:
1, the same functionality as Oracle's session-based temporary table is implemented.
2, support Sdo_geometry and other LOB data types.
3, support the primary foreign key connection between tables, and the primary foreign key connection is also based on the session.
4. Advanced users can access global data to achieve more complex functionality.


Eight wind does not move November 22, 2004 graffiti in Dalian Development Zone CMC Office