Toad World® Forums

Execute snippet from package results in ora-942

The snippet has a bunch of WITH clauses.

SELECT *
FROM ALL_LAST_SALES
Error: ORA-00942: table or view does not exist

ALL_LAST_SALES is a defined within my snippet via a WITH clause, so the error is technically correct, though the SQL is not correct.

I tried an example based on how I understand the problem and I’m missing something. Execute snippet in this example is working as expected for me. Please provide a sample.

    DECLARE
n NUMBER;
BEGIN
WITH my_cte_1 AS (SELECT * FROM user_objects),
my_cte_2 AS (SELECT * FROM user_objects)
SELECT COUNT (*)
INTO n
FROM my_cte_1 t1, my_cte_2 t2
WHERE t1.object_id = t2.object_id;
DBMS_OUTPUT.put_line (n);
END;