I tried to create a temporary table but it's showing me this error:

ORA-00903: invalid table name.

What did I did wrongly? This is what I did:

create private temporary table PID 
ON COMMIT PRESERVE ROWS
as
select distinct
    lot_id,
    ssr.run_oid,
    process_id
from sigma.sigma_run ssr
inner join sigma.sigma_lot ssl on ssl.run_oid = ssr.run_oid

CodePudding user response：

From the CREATE TABLE documentation:

Restrictions on Private Temporary Tables

In addition to the general limitations of temporary tables, private temporary tables are subject to the following restrictions:

• The name of private temporary tables must always be prefixed with whatever is defined with the init.ora parameter PRIVATE_TEMP_TABLE_PREFIX. The default is ORA$PTT_.

So you need to use:

create private temporary table ORA$PTT_PID 
ON COMMIT PRESERVE DEFINITION
AS
select distinct
    lot_id,
    ssr.run_oid,
    process_id
from sigma.sigma_run ssr
inner join sigma.sigma_lot ssl on ssl.run_oid = ssr.run_oid

Note: You also want PRESERVE DEFINITION and not PRESERVE ROWS.

fiddle