I am using SQL DML scripts to do some data inserting into a table; the select has a with clause and with clause has a function and additional with .
If I don't add the insert into table part, it works fine. but if I add insert, it ends with error:
like below:
Insert into tb1 (col1, col2....)
WITH --- to find the graduation date in studentcorefields table
FUNCTION test_date (d VARCHAR2)
RETURN VARCHAR2
AS
v_date DATE;
BEGIN
SELECT TO_DATE (d, 'MM/YYYY') INTO v_date FROM DUAL;
RETURN 'Valid';
EXCEPTION
WHEN OTHERS
THEN
RETURN 'Invalid';
END;
graduationDay
AS (select id, graduation_year, test_date(graduation_year)
from tb2
where....)
select ...
From tb3
join graduationday
on.... ;
It gives syntax error, ORA-00933: SQL command not properly ended.
But if I run only the part from with to the end without the insert, it runs fine.return results. It seems the program does not like with function when used with insert statement.
I read from article Ask Tom, it says to add /*+ WITH_PLSQL */ in the insert statement, but it still does not work for me.
Any fix so that I am able still use SQL.
Thanks,