Anyone see a reason why using the "with" construct in a create private temporary table results in ORA-00942 using SQLPLUS.
script:
create private temporary table ora$ptt_test as
with MyWith(num) as (
select 1 as num from sys.dual
)
select * from MyWith
;
This sql runs fine in Toad using integrated execution but not with SQL PLUS either with Toad or at Console. Issue appears to be SQLPLUS only.
Environments tested:
Solaris 11.4 with Oracle 19c database
Windows 10 with Oracle 19c client
Windows 10 Console:
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Apr 20 11:57:36 2020
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Enter password:
Last Successful login time: Mon Apr 20 2020 11:55:00 -04:00
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.6.0.0.0
SQL> create private temporary table ora$ptt_test as
2 with MyWith(num) as (
3 select 1 as num from sys.dual
4 )
5
SQL> select * from MyWith
2 ;
select * from MyWith
*
ERROR at line 1:
ORA-00942: table or view does not exist