I'm new to oracle. I am trying to use CTE's within a stored procedure.
Here's the sample code:
create or replace PROCEDURE "TEST"
AS
BEGIN
WITH CTE (StudentID, Studentname, Coursename) AS (
SELECT Distinct A.StudentID,B.Studentname ,C.Coursename FROM Student A
JOIN Studentname B ON A.studentid=b.studentid
JOIN Course C on C.CourseID=B.courseid
Where B.studenttype=4 and B.studentid is not null )
INSERT INTO studenthistory
select A.studentregistrationumber,A.studentregdate,B.studentname from studentregistration A
Inner Join cte b on A.studentid=b.studentid
END TEST;
This is the error I get,
Error(6,1): PL/SQL: SQL Statement ignored
Error(11,2): PL/SQL: ORA-00928: missing SELECT keyword
Thanks in advance