mjfigur
asked on
PL/SQL and Dynamic SQL syntax
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.
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;
/
Try:
select ''' || to_char(curr_date,'mm/dd/y
select ''' || to_char(curr_date,'mm/dd/y
ASKER
Is there anyway to rewrite the query to use bind variables? I have read that the bind variable perform quite faster.
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);
On mobile now but try:
Execute immediate ' ... Select '':mydate'', ...'
Using to_char(curr_date, 'mm/dd/yyyy);
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you so much worked perfect.
ASKER
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.