I am using Toad for Oracle Base tool , registered copy version 10.1.1.8.
I have a situation where several records should be generated and inserted in another table, but the number of generated records is depending on a property witch has different values for different records.
For that I have developed a statement like :
INSERT INTO TableFinal
(f1, f2, r) AS – here points the error
(SELECT f1, f2, 1 as r
SELECT f1, f2, r + 1
WHERE r < f2) – the dataTab generates r records , depending on the property f2 of record in Table1
FROM dataTab ta
join Table1 tb
ON ta.f1 = tb.f1
ORDER BY ta.r;
witch is part of a package body.
The package body is ok when build outside Toad example Oracle SQL Developer tool or PL/SQL Developer tool. Even in toad the package can be run but does not compile.
Error shown is ‘ERROR line 12, col 7, ending_line 12, ending_col 7, Found ‘(’, Expecting: AS’ witch points to the with… line of the statement