PL/SQL Statment Ignored

Question

I am begginer in PL/SQL and I want to create function to retrive AllProject. SO far what I create is

          CREATE OR REPLACE FUNCTION GETALLPROJECTS(currentUserID in INT)
   RETURN SYS_REFCURSOR
IS 
   rc  SYS_REFCURSOR;
BEGIN
OPEN rc FOR
        SELECT p.*
        FROM projects p
        LEFT JOIN project_users_schedule_dates pusd
ON     
      pusd.ProjectID = p.ProjectID
AND 
      pusd.UserID = currentUserID
LEFT JOIN responsible_persons rp
ON
   rp.ProjectID = p.ProjectID
AND 
   rp.UserID = currentUserID
LEFT JOIN users u ON u.UserID = currentUserID
WHERE    
     u.User_roleID = 1
(
    (p.Responsible_person_id = currentUserId OR 
    p.Delivery_contact = currentUserId OR 
    rp.UserID = currentUserId OR 
    (pusd.ProjectID = p.ProjectID AND                         
    pusd.UserID = currentUserId AND NOW() BETWEEN pusd.StartDate AND pusd.EndDate + INTERVAL 1 DAY 
    AND
    NOW() BETWEEN p.StartDate AND p.EndDate + INTERVAL 1 DAY)
    AND p.status = 2)
)
 ORDER BY p.ProjectID;
RETURN rc;
END GETALLPROJECTS;

When I compile I gett two error which I dont understand what errors are:

First error is:

Error(6,1): PL/SQL: SQL Statement ignored

And Second error is:

Error(27,24): PL/SQL: ORA-00933: SQL command not properly ended

Answer 1

A superfluous comma, should be = instead of :=, missing semi-colon, missing single quotes for INTERVAL.

Also, you can't select that many columns into a single VARCHAR2 variable, nor can you GROUP BY a single column. Consider returning a ref cursor instead:

CREATE OR REPLACE FUNCTION GETALLPROJECTS (currentUserID IN INT)
   RETURN SYS_REFCURSOR
IS
   rc  SYS_REFCURSOR;
BEGIN
   OPEN rc FOR
        SELECT p.*
          FROM projects p
               LEFT JOIN project_users_schedule_dates pusd
                  ON     pusd.ProjectID = p.ProjectID
                     AND pusd.UserID = currentUserID
               LEFT JOIN responsible_persons rp
                  ON     rp.ProjectID = p.ProjectID
                     AND rp.UserID = currentUserID
               LEFT JOIN users u ON u.UserID = currentUserID
         WHERE    u.User_roleID = 1
               OR ( (   p.Responsible_person_id = currentUserID
                     OR p.Delivery_contact = currentUserID
                     OR rp.UserID = currentUserID
                     OR     (    pusd.ProjectID = p.ProjectID
                             AND pusd.UserID = currentUserID
                             AND NOW () BETWEEN pusd.StartDate
                                            AND pusd.EndDate + INTERVAL '1' DAY
                             AND NOW () BETWEEN p.StartDate
                                            AND p.EndDate + INTERVAL '1' DAY)
                        AND p.status = 2))
      ORDER BY p.ProjectID;

   RETURN rc;
END GETALLPROJECTS;

---

As I don't have your tables, here's a example created on Scott's sample schema, just to illustrate what you might have done:

SQL> create or replace function getallemp (par_deptno in number)
  2    return sys_refcursor
  3  is
  4    rc sys_refcursor;
  5  begin
  6    open rc for
  7      select e.*
  8      from emp e
  9      where e.deptno = par_deptno
 10      order by e.ename;
 11    return rc;
 12  end;
 13  /

Function created.

SQL>
SQL> select getallemp(10) from dual;

GETALLEMP(10)
--------------------
CURSOR STATEMENT : 1

CURSOR STATEMENT : 1

     EMPNO ENAME      JOB              MGR HIREDATE        SAL       COMM     DEPTNO
---------- ---------- --------- ---------- -------- ---------- ---------- ----------
      7782 CLARK      MANAGER         7839 09.06.81       2450                    10
      7839 KING       PRESIDENT            17.11.81      10000                    10
      7934 MILLER     CLERK           7782 23.01.82       1300                    10


SQL>

Answer 2

Use this query and first check if query is working independently by passing the hardcoded value in the query:

CREATE OR REPLACE FUNCTION GETALLPROJECTS(currentUserID in INT)
   RETURN SYS_REFCURSOR
IS 
   rc  SYS_REFCURSOR;
BEGIN
OPEN rc FOR
        SELECT p.ProjectID,
            p.CustomName,
            p.Name,
            p.Responsible_person_id,
            p.Delivery_contact,
            p.StartDate,
            p.EndDate,
            p.TehnicReview,
            p.status
        FROM projects p
        LEFT JOIN project_users_schedule_dates pusd
ON     
      pusd.ProjectID = p.ProjectID
AND 
      pusd.UserID = currentUserID
LEFT JOIN responsible_persons rp
ON
   rp.ProjectID = p.ProjectID
AND 
   rp.UserID = currentUserID
LEFT JOIN users u ON u.UserID = currentUserID
WHERE    
     u.User_roleID = 1
(
    (p.Responsible_person_id = currentUserId OR 
    p.Delivery_contact = currentUserId OR 
    rp.UserID = currentUserId OR 
    (pusd.ProjectID = p.ProjectID AND                         
    pusd.UserID = currentUserId AND NOW() BETWEEN pusd.StartDate AND pusd.EndDate + INTERVAL 1 DAY 
    AND
    NOW() BETWEEN p.StartDate AND p.EndDate + INTERVAL 1 DAY)
    AND p.status = 2)
)
 ORDER BY p.ProjectID;
RETURN rc;
END GETALLPROJECTS;