We help IT Professionals succeed at work.

PL/SQL and Dynamic SQL syntax

mjfigur
mjfigur asked
on
All,

I have used dynamic sql in the past however I have never used it while using a 'WITH' clause. I believe I had everything correct here, however I am recieving the message:

ORA-00904: "NOV": Invalid Identifier

All I can think of is that I have the syntax for my variables incorrect or it has something to do with the 'WITH' clause.

Thank you in advance for your assistance.


CREATE OR REPLACE PROCEDURE BOMMGR.BO_000566_OP_A01_PRC AUTHID CURRENT_USER AS

    /*
        
    */
    TABLE_OR_VIEW_DOES_NOT_EXIST EXCEPTION;
    PRAGMA EXCEPTION_INIT (TABLE_OR_VIEW_DOES_NOT_EXIST,-942);
    
     
    CURR_DATE DATE;
    HI_TYPE NUMBER;
    DPD NUMBER;
    
BEGIN
    COMMIT;
    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DDL';
    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL DML';
    EXECUTE IMMEDIATE 'ALTER SESSION ENABLE PARALLEL QUERY';
    COMMIT;
  
/*
 --  INSERT ENTRY INTO GLOBAL_LOAD_STATUS_TBL
    BO_DBA004_PRC('BO_000566','BO_000566_TBL','BO_000566_A01_STGTBL','DAILY');


    --  UPDATE FIELD "SCHEDULE_IND" IN DAILY_DEPENDENCY_TBL SO JOB WILL NOT GET SCHEDULE
    BO_DBA005_PRC('DAILY_DEPENDENCY_TBL','BO_000566','BO_000566_A01_STGTBL','SCHEDULE_IND','STAGING_TBL_NM');

*/
    SELECT TRUNC(SYSDATE) INTO CURR_DATE FROM DUAL;
    SELECT DPD INTO DPD FROM BO_000566_INC_EXC_TBL
                                             WHERE     INCLUDE_EXCLUDE = 'INCLUDE'
                                                     AND CLIENT = 'ENT'
                                                     AND DPD IS NOT NULL;
     SELECT HI_TYPE INTO HI_TYPE
                                    FROM BO_000566_INC_EXC_TBL
                                   WHERE     INCLUDE_EXCLUDE = 'INCLUDE'
                                         AND CLIENT = 'ENT'
                                         AND HI_TYPE IS NOT NULL;
   
    DECLARE
        TABLE_OR_VIEW_DOES_NOT_EXIST EXCEPTION;
        PRAGMA EXCEPTION_INIT (TABLE_OR_VIEW_DOES_NOT_EXIST,-942);
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE BO_000566_OP_A01_STGTBL PURGE';
    EXCEPTION
    WHEN TABLE_OR_VIEW_DOES_NOT_EXIST THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
    END;
      
    EXECUTE IMMEDIATE  ' create table BO_000566_OP_A01_STGTBL 
           as 
             WITH EX_MANCODE AS (SELECT /*+ materialize */  MAN_CODE
                                FROM BO_000566_INC_EXC_TBL
                               WHERE     INCLUDE_EXCLUDE = ''EXCLUDE''
                                     AND WATERFALL = ''ACTIVE''
                                     AND MAN_CODE IS NOT NULL),
          
         EX_PROCESS_STOP AS (SELECT /*+ materialize */   PROCESS_STOP_CODE
                                FROM BO_000566_INC_EXC_TBL
                               WHERE     INCLUDE_EXCLUDE = ''EXCLUDE''
                                     AND WATERFALL = ''ACTIVE''
                                     AND PROCESS_STOP_CODE IS NOT NULL),
                                     
          EX_PIF_CODE AS          (SELECT /*+ materialize */   PIF_CODE
                                                FROM BO_000566_INC_EXC_TBL
                                               WHERE     INCLUDE_EXCLUDE = ''EXCLUDE''
                                                     AND WATERFALL = ''ACTIVE''
                                                     AND PIF_CODE IS NOT NULL),
                                                     
           EX_NO_NOTICE AS (SELECT  /*+ materialize */  NO_NOTICE
                                FROM BO_000566_INC_EXC_TBL
                               WHERE     INCLUDE_EXCLUDE = ''EXCLUDE''
                                     AND WATERFALL = ''ACTIVE''
                                     AND NO_NOTICE IS NOT NULL),
                                     
            INC_MANCODE AS (SELECT  /*+ materialize */  MAN_CODE
                                        FROM BO_000566_INC_EXC_TBL
                                       WHERE     INCLUDE_EXCLUDE = ''INCLUDE''
                                             AND CLIENT = ''ENT''
                                             AND MAN_CODE IS NOT NULL),
                                             
            INC_PROCESS_STOP AS (SELECT  /*+ materialize */  PROCESS_STOP_CODE
                                        FROM BO_000566_INC_EXC_TBL 
                                       WHERE     INCLUDE_EXCLUDE = ''INCLUDE''
                                             AND CLIENT = ''ENT''
                                             AND PROCESS_STOP_CODE
                                                    IS NOT NULL),
              
             INC_CAT_CODE AS  (SELECT /*+ materialize */  CATEGORY_CODE
                                         FROM BO_000566_INC_EXC_TBL BO_000566_INC_EXC_TBL11
                                        WHERE     INCLUDE_EXCLUDE = ''INCLUDE''
                                              AND CLIENT = ''156''
                                              AND CATEGORY_CODE IS NOT NULL),
                                              
               EA_INC_FC AS (SELECT /*+ materialize */   COMMENT_LOG
                                                   FROM BO_000566_INC_EXC_TBL 
                                                  WHERE     CLIENT = ''ENT''
                                                        AND COMMENT_LOG
                                                               IS NOT NULL),
                
               EA_EX_FC AS (SELECT /*+ materialize */   COMMENT_LOG
                                               FROM BO_000566_INC_EXC_TBL BO_000566_INC_EXC_TBL6
                                              WHERE     CLIENT = ''ENT''
                                                    AND INCLUDE_EXCLUDE =
                                                           ''INCLUDE''
                                                    AND COMMENT_LOG
                                                           IS NOT NULL)
                       


SELECT '||CURR_DATE||'  as LOAD_DT,
          ''ALT_FC'' AS ALT_FC_POP,
          LOAN_NUMBER,
          CLIENT,
          DPD,
          HI_TYPE,
          MAN_CODE,
          PROCESS_STOP_CODE,
          PAYMENT_IN_FULL_STOP_CODE,
          NO_NOTICES_FLAG,
          servicing_sold_id,
          MAN_CODE_EXCLUSION,
          PROCESS_STOP_EXCLUSION,
          PIF_STOP_EXCLUSION,
          NO_NOTICES_EXCLUSION,
          BK_STATUS_CODE,
          FC_STATUS_CODE,
          ''NEW'' AS POPULATION,
          INVESTOR_ID
     FROM (SELECT LOAN_NUMBER,
                  CLIENT,
                  DPD,
                  HI_TYPE,
                  MAN_CODE,
                  PROCESS_STOP_CODE,
                  PAYMENT_IN_FULL_STOP_CODE,
                  NO_NOTICES_FLAG,
                  servicing_sold_id,
                  INVESTOR_ID,
                  BANKRUPTCY_STATUS_CODE_M as BK_STATUS_CODE,
                  FORECLOSURE_STATUS_CODE as FC_STATUS_CODE,
                  CASE
                     WHEN MAN_CODE IN
                            (SELECT MAN_CODE FROM EX_MANCODE)
                     THEN
                        ''MAN_CODE_EXCLUSION''
                     ELSE
                        ''N''
                  END
                     AS MAN_CODE_EXCLUSION,
                  CASE
                     WHEN PROCESS_STOP_CODE IN
                             (SELECT PROCESS_STOP_CODE FROM EX_PROCESS_STOP)
                     THEN
                        ''PROCESS_STOP_EXCLUSION''
                     ELSE
                        ''N''
                  END
                     AS PROCESS_STOP_EXCLUSION,
                  CASE
                     WHEN PAYMENT_IN_FULL_STOP_CODE IN
                             (SELECT PIF_CODE FROM EX_PIF_CODE)
                     THEN
                        ''PIF_STOP_EXCLUSION''
                     ELSE
                        ''N''
                  END
                     AS PIF_STOP_EXCLUSION,
                  CASE
                     WHEN NO_NOTICES_FLAG IN
                             (SELECT NO_NOTICE FROM EX_NO_NOTICE)
                     THEN
                        ''NO_NOTICES_EXCLUSION''
                     ELSE
                        ''N''
                  END
                     AS NO_NOTICES_EXCLUSION
             FROM (SELECT LOAN_NUMBER,
                          ''465'' AS CLIENT,
                          PROCESS_STOP_CODE,
                          MAN_CODE,
                          HI_TYPE,
                          ('||CURR_DATE||'  - NEXT_PAYMENT_DUE_DATE) AS DPD,
                          PAYMENT_IN_FULL_STOP_CODE,
                          NO_NOTICES_FLAG,
                          SERVICING_SOLD_ID,
                          BANKRUPTCY_STATUS_CODE_M,
                          FORECLOSURE_STATUS_CODE,
                          INVESTOR_ID
                     FROM MORTGMGR.MSP_LOAN_D@INFODB LOAN1
                    WHERE     (   LOAN1.MAN_CODE IN
                                     (SELECT MAN_CODE FROM INC_MANCODE)
                               OR PROCESS_STOP_CODE IN
                                     (SELECT PROCESS_STOP_CODE FROM INC_PROCESS_STOP)
                                     )
                          AND HI_TYPE =
                                 '||HI_TYPE||'
                          AND ( '||CURR_DATE||'  - NEXT_PAYMENT_DUE_DATE) >=
                                 '||DPD||'
                   UNION
                   SELECT EA1.LOAN_NUMBER,
                          ''465'' AS CLIENT,
                          LOAN2.PROCESS_STOP_CODE,
                          LOAN2.MAN_CODE,
                          LOAN2.HI_TYPE,
                         ('||CURR_DATE||' -NEXT_PAYMENT_DUE_DATE) AS DPD,
                          LOAN2.PAYMENT_IN_FULL_STOP_CODE,
                          LOAN2.NO_NOTICES_FLAG,
                          LOAN2.SERVICING_SOLD_ID,
                          BANKRUPTCY_STATUS_CODE_M,
                          FORECLOSURE_STATUS_CODE,
                          INVESTOR_ID
                     FROM    (SELECT *
                                FROM (SELECT loan_number,
                                             comment_code,
                                             comment_date,
                                             ROW_NUMBER ()
                                             OVER (
                                                PARTITION BY loan_number
                                                ORDER BY comment_date DESC)
                                                AS row_order
                                        FROM mortgmgr.MSP_COMMENT_LOG_R@infodb MSP_COMMENT_LOG_R1
                                       WHERE comment_code IN
                                                (SELECT COMMENT_LOG FROM EA_INC_FC))
                               WHERE     comment_code IN
                                            (SELECT COMMENT_LOG FROM EA_EX_FC)
                                     AND row_order = 1) EA1
                          INNER JOIN
                             mortgmgr.MSP_LOAN_D@INFODB LOAN2
                          ON EA1.LOAN_NUMBER = LOAN2.LOAN_NUMBER
                    WHERE     HI_TYPE =
                                  '||HI_TYPE||'
                          AND ('||CURR_DATE||'  - NEXT_PAYMENT_DUE_DATE) >=
                                  '||DPD||'
                   UNION ALL
                   (SELECT LOAN_NUMBER,
                           ''156'' AS CLIENT,
                           PROCESS_STOP_CODE,
                           MAN_CODE,
                           HI_TYPE,
                            ('||CURR_DATE||' - NEXT_PAYMENT_DUE_DATE)
                              AS DPD,
                           PAYMENT_IN_FULL_STOP_CODE,
                           NO_NOTICES_FLAG,
                           servicing_sold_id,
                           BANKRUPTCY_STATUS_CODE_M,
                           FORECLOSURE_STATUS_CODE,
                           INVESTOR_ID
                      FROM MC156MGR.MSP_LOAN_D@INFODB LOAN3
                     WHERE     (   LOAN3.MAN_CODE =
                                        (SELECT MAN_CODE FROM INC_MANCODE)
                                OR PROCESS_STOP_CODE =
                                      (SELECT PROCESS_STOP_CODE FROM INC_PROCESS_STOP)
                                OR CATEGORY_CODE IN
                                      (SELECT CATEGORY_CODE FROM INC_CAT_CODE)
                                      )
                           AND HI_TYPE =
                                  '||HI_TYPE||'
                           AND ('||CURR_DATE||'  - NEXT_PAYMENT_DUE_DATE) >=
                                  '||DPD||'
                    UNION
                    SELECT /*+ NO_INDEX(LOAN4) */
                          EA2.LOAN_NUMBER,
                           ''156'' AS CLIENT,
                           LOAN4.PROCESS_STOP_CODE,
                           LOAN4.MAN_CODE,
                           LOAN4.HI_TYPE,
                            ('||CURR_DATE||'  - LOAN4.NEXT_PAYMENT_DUE_DATE)
                              AS DPD,
                           LOAN4.PAYMENT_IN_FULL_STOP_CODE,
                           LOAN4.NO_NOTICES_FLAG,
                           servicing_sold_id,
                           BANKRUPTCY_STATUS_CODE_M,
                           FORECLOSURE_STATUS_CODE,
                           INVESTOR_ID
                      FROM    (SELECT *
                                 FROM (SELECT loan_number,
                                              comment_code,
                                              comment_date,
                                              ROW_NUMBER ()
                                              OVER (
                                                 PARTITION BY loan_number
                                                 ORDER BY comment_date DESC)
                                                 AS row_order
                                         FROM mc156mgr.MSP_COMMENT_LOG_R@infodb 
                                        WHERE comment_code IN
                                                 (SELECT COMMENT_LOG FROM EA_INC_FC))
                                WHERE     comment_code IN
                                             (SELECT COMMENT_LOG FROM EA_EX_FC)
                                      AND row_order = 1) EA2
                           INNER JOIN
                              MC156MGR.MSP_LOAN_D@INFODB LOAN4
                           ON EA2.LOAN_NUMBER = LOAN4.LOAN_NUMBER
                     WHERE     HI_TYPE =
                                  '||HI_TYPE||'
                           AND ('||CURR_DATE||'  - NEXT_PAYMENT_DUE_DATE) >=
                                         '||DPD||')))
           
       ';
            COMMIT;
            

  
    DECLARE
        TABLE_OR_VIEW_DOES_NOT_EXIST EXCEPTION;
        PRAGMA EXCEPTION_INIT (TABLE_OR_VIEW_DOES_NOT_EXIST,-942);
    BEGIN
        EXECUTE IMMEDIATE 'DROP TABLE BO_000566_OP_FINAL_A01_TBL PURGE';
    EXCEPTION
    WHEN TABLE_OR_VIEW_DOES_NOT_EXIST THEN
        NULL;
    WHEN OTHERS THEN
        RAISE;
    END;
    COMMIT;


    EXECUTE IMMEDIATE 'RENAME BO_000566_OP_A01_STGTBL TO BO_000566_OP_FINAL_A01_TBL';
    
     EXECUTE IMMEDIATE ' create UNIQUE INDEX BO_000566_OP_FINAL_A01_TBL_IDX ON BO_000566_OP_FINAL_A01_TBL(LOAN_NUMBER) COMPUTE STATISTICS
      
           
       ';
            COMMIT;
    
    -- GRANT PERMISSION
    EXECUTE IMMEDIATE 'GRANT SELECT ON BO_000566_OP_FINAL_A01_TBL TO BOMMGR_USER';
    COMMIT;
  
 /*  
    --  UPDATE GLOBAL_LOAD_STATUS_TBL
    BO_DBA005_PRC('GLOBAL_LOAD_STATUS_TBL','BO_000566','BO_000566_A01_STGTBL','COMP_IND','STAGING_TBL_NM');
    
    --  UPDATE FIELD "COMP_IND" IN DAILY_DEPENDENCY_TBL SO NEXT JOB WILL GET SCHEDULE
    BO_DBA005_PRC('DAILY_DEPENDENCY_TBL','BO_000566','BO_000566_A01_STGTBL','COMP_IND','STAGING_TBL_NM');
    
    UPDATE 
        DAILY_DEPENDENCY_TBL
    SET 
        DEPENDENCY_MEET_IND = 'Y'
    WHERE
        PROCESS_ID = 'BO_000566'
        AND TARGET_TBL_NM = 'BO_000566_TBL'
        AND VALID_VALUES = 'BO_000566_A01_STGTBL';
    */    
    
    COMMIT;

END;
/

Open in new window

Comment
Watch Question

Author

Commented:
UPDATE:

I realize now it has to do with the CURR_DATE variable. I just have no idea how to bind that variable since I am doing a DDL.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
Try:
select ''' || to_char(curr_date,'mm/dd/yyyy') ||'''  as load_dt

Author

Commented:
Is there anyway to rewrite the query to use bind variables? I have read that the bind variable perform quite faster.
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019

Commented:
When selecting it I believe you can.  The key is 'using' with execute immediate.

On mobile now but try:

Execute immediate ' ...  Select '':mydate'', ...'
Using to_char(curr_date, 'mm/dd/yyyy);
CERTIFIED EXPERT
Most Valuable Expert 2012
Distinguished Expert 2019
Commented:
I should add that the 'performance' gain is that Oracle doesn't have to parse the sql on each execution.

Depending on your database and number of executions, you probably won't see any difference.

Author

Commented:
Thank you so much worked perfect.

Explore More ContentExplore courses, solutions, and other research materials related to this topic.