sql9

The error ORA-14451: unsupported feature with temporary table occurs because you're trying to create a Private Temporary Table (PTT) with a schema qualifier (DEMO_USER.), which is not allowed.

The Problem:
Private Temporary Tables (PTTs) are session-private and belong only to the session that creates them.

PTTs cannot be qualified with a schema name because they exist only in your current session's namespace.

When you write CREATE PRIVATE TEMPORARY TABLE DEMO_USER.ORASPTT_..., Oracle expects this to be a regular table in the DEMO_USER schema, but the PRIVATE TEMPORARY keywords conflict with this.

Correct Syntax:
Remove the schema qualifier:

sql
CREATE PRIVATE TEMPORARY TABLE ORASPTT_ET_CORP_ALERT_FORMATTED_MSG_TEMP3 
ON COMMIT PRESERVE DEFINITION 
AS 
SELECT COD_ALERT_NO, COD_ALERT_UID, COD_ALERT_MIS_UID 
FROM DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG 
WHERE 1=0;

Key Rules for Private Temporary Tables:
No schema qualification - PTTs exist only in your session namespace

Naming convention - Must start with ORA$PTT_ (which yours does: ORASPTT_...)

Transaction scope - Controlled by ON COMMIT clause:

ON COMMIT DROP DEFINITION - Table dropped after transaction

ON COMMIT PRESERVE DEFINITION - Table persists for session

Visibility - Only visible to the creating session

Alternative Solutions:
1. Create the PTT without schema (as shown above)
2. If you need a table accessible to other sessions/users:
Use a regular global temporary table instead:

CREATE GLOBAL TEMPORARY TABLE DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG_TEMP3
ON COMMIT PRESERVE ROWS  -- or DELETE ROWS
AS 
SELECT COD_ALERT_NO, COD_ALERT_UID, COD_ALERT_MIS_UID 
FROM DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG 
WHERE 1=0;

Key differences:

Global Temporary Tables: Schema-qualified, visible to all sessions, data is session-private

Private Temporary Tables: No schema, visible only to creating session, both definition and data are private

3. If you want the table definition to persist beyond your session:
Use a regular table:

sql
CREATE TABLE DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG_TEMP3 
AS 
SELECT COD_ALERT_NO, COD_ALERT_UID, COD_ALERT_MIS_UID 
FROM DEMO_USER.ET_CORP_ALERT_FORMATTED_MSG 
WHERE 1=0;

Summary:
For session-private, temporary use: Remove DEMO_USER. prefix from your PTT

For temporary data accessible to all sessions: Use GLOBAL TEMPORARY TABLE with schema

For permanent table: Use regular CREATE TABLE with schema

Since you're using Oracle 19c, PTTs are supported, but you must follow the naming rules and avoid schema qualification for PTTs.