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.