All,
I’m still on Toad 12.11.0.22
while working on a script (anonymous block) I block the query used
to define a cursor, then hit F9, expecting to have the result grid populated.
Usually, this works, but sometimes I get an ORA-00942: table or view does not exist
Spooling the SQL shows that the failing statement is:
select *
from current_rows
Oracle is correct in flagging this as an error: current_rows is not the name
of any table; it is the name of a CTE defined in the cursor.
This is the cursor definition:
cursor load_messages
is
with current_rows
as (select pl.pono
, pl.porowpos
, pl.porowseq
, max(pl.rowstatus_new) keep (dense_rank last order by pl.timestamp) as rowstatus
, max(pl.timestamp) as timestamp
from iib_krg.porow_log pl
where pl.status not in (‘0’, ‘1’) – exclude unprocessed events
and pono = 2116943
group by pl.pono, pl.porowpos, pl.porowseq
having max(pl.change_type) keep (dense_rank last order by pl.timestamp) in (‘I’, ‘U’) – skip deleted rows
and max(pl.otype_new) keep (dense_rank last order by pl.timestamp) = ‘31’
and max(pl.rowstatus_new) keep (dense_rank last order by pl.timestamp) in (300, 310, 320, 322)
and max(pl.podeltype_new) keep (dense_rank last order by pl.timestamp) in (‘L’, ‘AT’))
, po_full
as (select cr.pono
, -1 as porowpos
, -1 as porowseq
, max(cr.timestamp) as timestamp
from current_rows cr
group by cr.pono)
, asn_c
as (select cr.pono
, cr.porowpos
, cr.porowseq
, cr.timestamp
from current_rows cr
where cr.rowstatus = 322)
select pono
, porowpos
, porowseq
, timestamp
from po_full
union all
select pono
, porowpos
, porowseq
, timestamp
from asn_c
order by pono
, porowpos
, porowseq
, timestamp;
Can you please fix this issue?