HPL/SQL provides you with two options to work with temporary tables: native and managed.

Use the hplsql.temp.tables option to define how to handle temporary tables, the default value is native.

When native temporary tables are used HPL/SQL relies on the underlying database to manage temporary tables.

HPL/SQL converts DECLARE TEMPORARY TABLE statement to CREATE TEMPORARY TABLE in Hive. Note that Hive supports temporary tables since version 0.14 only.

When hplsql.temp.tables is set to managed, HPL/SQL creates a regular table in the database and automatically drops it at the end of the session.

Note that the schema name and location are defined by hplsql.temp.tables.schema and hplsql.temp.tables.location options, respectively.

Also UUID is added to the table name to prevent name conflicts between multiple sessions.

For example, if you declare temporary table temp1, HPL/SQL will actually create something like temp1_3fc162e0590f4e17ae141385cc0e8447.

Example:

Create a managed temporary table and use it in other SQL statements:

SET hplsql.temp.tables = managed;
 
DECLARE TEMPORARY TABLE temp1
(
   c1 INT,
   c2 STRING
);
 
INSERT INTO temp1 SELECT 1, 'A' FROM dual;
 
SELECT * FROM temp1;

See also:

• CREATE LOCAL TEMPORARY TABLE
• CREATE VOLATILE TABLE
• DECLARE TEMPORARY TABLE