title: ORA-00933: SQL command not properly ended in stored procedure
body: <pre><code>create or replace PROCEDURE SEC_YIELD_DOD_COMPARE_RPT(
      -- IN parameters
      reportingDate             IN DATE,
      P_PORTFOLIO_ID            IN NUMERIC,
      HOLDING_SECURITY_ID_PARAM IN VARCHAR2,
      derYieldCalcEngineCd      IN VARCHAR2,
      -- OUT for INSTRUMENT
      INSTRUMENT_CUR OUT SYS_REFCURSOR,
      -- OUT for PORTFOLIO
      PORTFOLIO_CUR OUT SYS_REFCURSOR )
  AS
    instrument_sids instrument_sids_table := instrument_sids_table();
    portfolio_sids portfolio_sids_table   := portfolio_sids_table();
    queryPortfolio VARCHAR2(1500);
    queryInstrument VARCHAR2(1500);
    queryComplement VARCHAR2(1500);
  BEGIN
  
    queryPortfolio:=' SELECT PORTFOLIO_SIDS_RECORD( c1, c2, c3 ) 
            FROM
              (SELECT PHS.PORTFOLIO_SID AS c1,
                NULL                    AS c2,
                PHS.TRADABLE_ENTITY_SID AS c3
              FROM PORTFOLIO_HOLDING_SNAPSHOT PHS
              INNER JOIN PORTFOLIO P     
              ON P.PORTFOLIO_SID = PHS.PORTFOLIO_SID
              INNER JOIN TRADABLE_ENTITY TE
              ON PHS.TRADABLE_ENTITY_SID = TE.TRADABLE_ENTITY_SID
              INNER JOIN INSTRUMENT I
              ON TE.INSTRUMENT_SID = I.INSTRUMENT_SID
              INNER JOIN TRADABLE_ENTITY_SNAPSHOT TES
              ON TE.TRADABLE_ENTITY_SID     = TES.TRADABLE_ENTITY_SID
              WHERE PHS.REPORTING_DT=:1
              AND TES.REPORTING_DT=:2
              AND P.CALCULATE_SEC_YIELD_IND = ''Y'')';
  
      queryInstrument:='SELECT INSTRUMENT_SIDS_RECORD(INSTRUMENT_SID, TRADABLE_ENTITY_SID) 
          FROM
            ( SELECT DISTINCT TE.INSTRUMENT_SID,
              TE.TRADABLE_ENTITY_SID
            FROM TRADABLE_ENTITY TE
            INNER JOIN PORTFOLIO_HOLDING_SNAPSHOT PHS
            ON PHS.TRADABLE_ENTITY_SID = TE.TRADABLE_ENTITY_SID
            INNER JOIN PORTFOLIO P
            ON PHS.PORTFOLIO_SID = P.PORTFOLIO_SID
            INNER JOIN INSTRUMENT I
            ON TE.INSTRUMENT_SID = I.INSTRUMENT_SID
            INNER JOIN TRADABLE_ENTITY_SNAPSHOT TES
            ON TE.TRADABLE_ENTITY_SID       = TES.TRADABLE_ENTITY_SID
            WHERE P.CALCULATE_SEC_YIELD_IND = ''Y''
            AND PHS.REPORTING_DT = :1
            AND TES.REPORTING_DT = :2)';
      -- Populate instrument IDs --
  
         IF P_PORTFOLIO_ID IS NOT NULL THEN
           IF HOLDING_SECURITY_ID_PARAM IS NOT NULL THEN
              IF derYieldCalcEngineCd IS NOT NULL THEN
                  -- P_PORTFOLIO_ID AND HOLDING_SECURITY_ID_PARAM AND derYieldCalcEngineCd
                  queryComplement:=' AND P.PORTFOLIO_ID=:3 AND PHS.HOLDING_SECURITY_ID =:4 AND TES.DER_YIELD_CALC_ENGINE_CD = :5';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, HOLDING_SECURITY_ID_PARAM, derYieldCalcEngineCd;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, HOLDING_SECURITY_ID_PARAM, derYieldCalcEngineCd;
              ELSE
                  -- P_PORTFOLIO_ID AND HOLDING_SECURITY_ID_PARAM
                  queryComplement:=' AND P.PORTFOLIO_ID=:3 AND PHS.HOLDING_SECURITY_ID =:4';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, HOLDING_SECURITY_ID_PARAM;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, HOLDING_SECURITY_ID_PARAM;
        END IF;
          ELSE
              IF derYieldCalcEngineCd IS NOT NULL THEN
                  -- P_PORTFOLIO_ID AND derYieldCalcEngineCd
                  queryComplement:=' AND P.PORTFOLIO_ID=:3 AND TES.DER_YIELD_CALC_ENGINE_CD = :4';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, derYieldCalcEngineCd;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID, derYieldCalcEngineCd;
              ELSE
                  -- reportingDate, P_PORTFOLIO_ID
  
                   queryComplement:=' AND P.PORTFOLIO_ID=:3';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate,P_PORTFOLIO_ID;
              END IF;
          END IF;
  
      ELSE
          --FUND NUMBER IS NULL
          IF HOLDING_SECURITY_ID_PARAM IS NOT NULL THEN
              IF derYieldCalcEngineCd IS NOT NULL THEN
                --HOLDING_SECURITY_ID_PARAM and derYieldCalcEngineCd , NO FUND NUMBER
                  queryComplement:='  AND PHS.HOLDING_SECURITY_ID =:3 AND TES.DER_YIELD_CALC_ENGINE_CD = :4';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate, HOLDING_SECURITY_ID_PARAM, derYieldCalcEngineCd;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate, HOLDING_SECURITY_ID_PARAM, derYieldCalcEngineCd;
              ELSE
                 --ONLY HOLDING_SECURITY_ID_PARAM
                   queryComplement:=' AND PHS.HOLDING_SECURITY_ID =:3 ';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate, HOLDING_SECURITY_ID_PARAM;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate, HOLDING_SECURITY_ID_PARAM;
                END IF;
          ELSE
          --ONLY derYieldCalcEngineCd
           IF derYieldCalcEngineCd IS NOT NULL THEN
               queryComplement:='  AND TES.DER_YIELD_CALC_ENGINE_CD = :3';
                  queryPortfolio := queryPortfolio || queryComplement;
                  queryInstrument := queryInstrument || queryComplement;
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate, derYieldCalcEngineCd;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate, derYieldCalcEngineCd;
  
          ELSE         
  
  
          --  ONLY reportingDate
  
  
                 EXECUTE IMMEDIATE queryPortfolio BULK COLLECT INTO PORTFOLIO_SIDS USING reportingDate,reportingDate;
                 EXECUTE IMMEDIATE queryInstrument BULK COLLECT INTO INSTRUMENT_SIDS USING reportingDate,reportingDate;
      END IF;
      END IF;
      END IF;
  </code></pre>
  
  <p>When I execute this Stored Proc, it's showing me 
  ORA-00933: SQL command not properly ended
  ORA-06512: at "FAYAODSDEV01.SEC_YIELD_DOD_COMPARE_RPT", line 112
  ORA-06512: at line 14.</p>
  
  <p>None of the lines is missing semicolon or any other syntactic stuff. 
  I suspect the problem is in the string queryPortfolio or
  queryInstrument.</p>
  
  <p>However, I'm unable to figure out where the exact problem lies.</p>
  
  <p>And for some reason, it is mentioning the line number 14.</p>
  
  <p>Any thoughts?
  Thanks in advance. </p>