oracle-databasecommitprivatetemporary

correct syntax for private temporary table with on commit clause

I am creating a private temporary table in oracle with the following

    create private temporary table ora$ptt_users as
select * from lul_users;

This works fine however when I add on commit preserve definition as follows

    create private temporary table ora$ptt_users as
select * from lul_users on commit preserve definition;

I get an "SQL command not properly ended. Any suggestions

Solution

  • You have the clause in the wrong place:

    create private temporary table ora$ptt_users
on commit preserve definition as
select * from lul_users;

    fiddle (against dual).

    See the documentation, including the example for a global temporary table