WITH statement error on execute package body

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
(
fa,
fb,
fc)
WITH dataTab
(f1, f2, r) AS – here points the error
(SELECT f1, f2, 1 as r
FROM Table1
WHERE condition1
UNION ALL
SELECT f1, f2, r + 1
FROM dataTab
WHERE r < f2) – the dataTab generates r records , depending on the property f2 of record in Table1

SELECT
tb.f1,
ta.r,
tb.fx
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

That’s a really old version of Toad you have. This is not a problem in the current version.

By the way, I got it to work in Toad 10 by taking out the column names before AS like this:

INSERT INTO TableFinal

(
fa,
fb,
fc)
WITH dataTab AS
(SELECT f1, f2, 1 as r

Hi John,

Thanks for the reply. I have to say that the issue is not solved, and the issue rise when the statement is part of a package, this is the tricky part. The statement works fine, executes too from toad ( if compiled by another tool) but does not compile.

If I remove what you suggested the package body detects the procedure names in the navigator pane but than a new error: *PL/SQL: ORA-32039: recursive WITH clause must have column alias list *

witch of course does not compile the package. So have you tried this as part of a package body ?

Yes, actually I did. I created this package on Oracle 10.2 using Toad 10.1

CREATE OR REPLACE PACKAGE PKG_WITH_TEST IS

PROCEDURE INS_DEPT;

END PKG_WITH_TEST;
/

CREATE OR REPLACE PACKAGE BODY PKG_WITH_TEST IS
PROCEDURE INS_DEPT IS
BEGIN
insert into dept (deptno, dname, loc)
with data
as (select deptno, dname, 1 as loc from dept)
select * from data;
commit;
end;
end;
/

I tried again, making my insert statement to look more like yours. I had to create table1 like this:

create table table1 (f1 number, fx varchar2(30));

I still can make the error go away if I comment out the alias list

CREATE OR REPLACE PACKAGE PKG_WITH_TEST IS

PROCEDURE INS_DEPT;

END PKG_WITH_TEST;
/

CREATE OR REPLACE PACKAGE BODY PKG_WITH_TEST IS
PROCEDURE INS_DEPT IS
BEGIN
INSERT INTO dept
(deptno, dname, loc)
WITH dataTab
– (f1, fx, r) – error here if this is uncommented
AS
(SELECT 1 as f1, ‘x’ as fx, 2 as r
FROM dual
WHERE 1=1
UNION ALL
SELECT 2, ‘y’, 1+2
FROM dual
WHERE 4 < 5)
SELECT
tb.f1,
ta.r,
tb.fx
FROM dataTab ta
join Table1 tb
ON ta.f1 = tb.f1
ORDER BY ta.r;
commit;
end;
end;
/

as always for any kind of “Toad bugs”.

first try the same statement in same ORACLE_HOME against same database with same user in Oracle SQL*Plus.

If there is no errors then this is then in Toad area.