Oracle Database - I am getting an error saying missing right paranthesis

CREATE OR REPLACE PROCEDURE FCSS.FCSS_REQUEST_SUMMARY(                                                      
                                                ivOfficeID IN  VARCHAR2  DEFAULT NULL,    
                                                ivAssignmentID IN  VARCHAR2  DEFAULT NULL,
                                                ivStartDt IN  VARCHAR2  DEFAULT NULL,
                                                ocRequestType OUT FCSS_PACKAGE.FCSSType,                                        
                                                ovErrorMsg      OUT VARCHAR2 
                                              ) AS
withClause   VARCHAR2(2000) := NULL;
selectClause  VARCHAR2(4000) := NULL;
cmdSQL        VARCHAR2(4000) := NULL;


BEGIN


withClause:= 'WITH SelectedRequests AS ( 
  SELECT R.REQ_ID, R.REQ_SIBLING_ID, R.REQ_STATUS, R.REQ_START_DT, R.OFFICE_ID, R.ASSIGNMENT_ID FROM FCSS_REQUEST R          
            (WHERE 1=1) 
            AND  TRUNC(R.REQ_START_DT) BETWEEN TO_DATE('
            ||ivStartDt|| ',"MM/DD/RRRR")  AND TO_DATE('
            ||ivStartDt|| ' ,"MM/DD/RRRR")
            AND R.OFFICE_ID ='|| ivOfficeID ||
            'AND R.ASSIGNMENT_ID ='|| ivAssignmentID||')';


--OPEN ocRequestType FOR    


selectClause:= '
Select 
 (select  T.DESCRIPTION from FCSS_REQUEST_TYPE T where T.CODE = 1) AS DESCRIPTION,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="PND") AS PENDING,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="APP") AS APPROVED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="ASN") AS ASSIGNED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="CAN") AS CANCELLED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="COM") AS COMPLETED
from dual
 union 
Select 
(select  T.DESCRIPTION from FCSS_REQUEST_TYPE T where T.CODE = 2) AS DESCRIPTION, 
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="PND") AS PENDING,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="APP") AS APPROVED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="ASN") AS ASSIGNED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="CAN") AS CANCELLED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="COM") AS COMPLETED
from dual
 union
 Select  
 (select  T.DESCRIPTION from FCSS_REQUEST_TYPE T where T.CODE = 3) AS DESCRIPTION, 
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="PND") AS PENDING,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="APP") AS APPROVED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="ASN") AS ASSIGNED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="CAN") AS CANCELLED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="COM") AS COMPLETED
from dual
union
 Select  
 (select  T.DESCRIPTION from FCSS_REQUEST_TYPE T where T.CODE = 4) AS DESCRIPTION, 
  (select Count(*) from FCSS_REQUEST where REQ_STATUS="PND") AS PENDING,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="APP") AS APPROVED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="ASN") AS ASSIGNED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="CAN") AS CANCELLED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="COM") AS COMPLETED
from dual
union
 Select  
 (select  T.DESCRIPTION from FCSS_REQUEST_TYPE T where T.CODE = 5) AS DESCRIPTION, 
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="PND") AS PENDING,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="APP") AS APPROVED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="ASN") AS ASSIGNED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="CAN") AS CANCELLED,
 (select Count(*) from FCSS_REQUEST where REQ_STATUS="COM") AS COMPLETED
from dual;';              
        


cmdSQL:= withClause || selectClause;


INSERT INTO TEST Values (cmdSQL, SYSDATE);
    COMMIT;
    
    OPEN ocRequestType FOR cmdSQL;
    
   exception  When others then
             ovErrorMsg := 'FCSS_REQUEST_SUMMARY - ' || SUBSTR(SQLERRM,1,200);
       
  
END;


 

Why are you running the same count query with the same where clause over and over again?  Wouldn't it be smarter to run it one and store the value in a local variable.  Then, use that variable as part of your select statement.

Also why are you storing the contents of a sql string in table?

I think you are also missing an extra set of parenthesis to isolate your BETWEEN statement on dates.

I also can't figure out why you are using dynamic sql.  It doesn't seem necessary based on your query.

Is there a more senior person on your team.  Maybe you should take this to them and have it code reviewed.

Hi Robbe,

Actually this was coded by my Team lead it self he wanted in a dynamic way. I informed him saying that its not necessary. Even without dynamic Sql I am able to produce results, but he wants me to try with dynamic SQL.

Yeah will look at it again. Thank you for the review and comments

Dynamic SQL, even in stored procedures, is open to SQL injection attacks.  I would think your team lead would be aware of that.  There are rare places where dynamic sql is a must but this isn't one of them.  I can't think of any competent developer I've ever worked with who would have suggested that you do this.  Its too bad you don't have a team lead with better judgement.